数据库模式设计 database-schema-design

---

名称: 数据库模式设计 描述: 用于PostgreSQL/MySQL的数据库模式设计，包括规范化、关系、约束。适用于新数据库、模式审查、迁移，或遇到缺少主键/外键、错误数据类型、过早反规范化、EAV反模式等情况。 关键词: 数据库模式, 模式设计, 数据库规范化, 1nf 2nf 3nf, 主键, 外键, 数据库关系, 一对一, 一对多, 多对多, 数据类型 postgresql, 约束检查, 审计列, 软删除, 数据库最佳实践, 模式模式, 数据库反模式, 缺少主键, 无外键, varchar 最大, 反规范化, 实体关系, 复合键, uuid 对比 bigserial, timestamptz 许可证: MIT

数据库模式设计

PostgreSQL和MySQL的全面数据库模式设计模式，包括规范化、关系、约束和错误预防。

---

快速开始（10分钟）

步骤1: 从模板中选择您的模式模式：

# 基本模式，包含用户、产品、订单
cat templates/basic-schema.sql

# 关系模式（1:1, 1:M, M:M）
cat templates/relationships.sql

# 约束示例
cat templates/constraints.sql

# 审计模式
cat templates/audit-columns.sql

步骤2: 应用规范化规则（至少3NF）：

• 1NF: 无重复组，原子值
• 2NF: 无对复合键的部分依赖
• 3NF: 无传递依赖
• 加载 references/normalization-guide.md 获取详细示例

步骤3: 为每个表添加基本元素：

CREATE TABLE your_table (
  -- 主键（必需）
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- 业务列，使用适当类型
  name VARCHAR(200) NOT NULL,  -- 使用适当长度

  -- 审计列（始终包含）
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

---

关键规则

✓ 始终做

规则	原因
每个表都有主键	确保行唯一性，启用关系
显式定义外键	强制执行引用完整性，防止孤儿记录
索引所有外键	防止慢JOIN，关键性能
在必填字段上使用NOT NULL	数据完整性，防止NULL污染
审计列（created_at, updated_at）	跟踪变更、调试、合规
适当数据类型	存储效率、验证、索引
对枚举使用检查约束	在数据库级别强制执行有效值
指定ON DELETE/UPDATE规则	防止意外数据丢失或孤儿记录

✗ 永远不做

反模式	为什么不好
到处使用VARCHAR(MAX)	浪费空间，减慢索引，无验证
日期作为VARCHAR	无日期数学，无验证，排序损坏
缺少外键	无引用完整性，孤儿记录
过早反规范化	难以维护，数据异常
EAV（实体-属性-值）	查询复杂，无类型安全，慢
多态关联	无外键完整性，复杂查询
循环依赖	无法填充，破坏CASCADE
外键上无索引	极慢JOIN，性能杀手

---

前7个关键错误

错误1：缺少主键

症状: 无法唯一标识行，重复数据 修复:

-- ❌ 错误
CREATE TABLE users (
  email VARCHAR(255),
  name VARCHAR(100)
);

-- ✅ 正确
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);

错误2：无外键约束

症状: 孤儿记录，数据不一致 修复:

-- ❌ 错误
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID  -- 无约束！
);

-- ✅ 正确
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

-- 索引外键
CREATE INDEX idx_orders_user_id ON orders(user_id);

错误3：到处使用VARCHAR(MAX)

症状: 浪费空间，慢索引，无验证 修复:

-- ❌ 错误
CREATE TABLE products (
  name VARCHAR(MAX),
  sku VARCHAR(MAX),
  status VARCHAR(MAX)
);

-- ✅ 正确
CREATE TABLE products (
  name VARCHAR(200) NOT NULL,
  sku VARCHAR(50) UNIQUE NOT NULL,
  status VARCHAR(20) NOT NULL
    CHECK (status IN ('draft', 'active', 'archived'))
);

错误4：错误数据类型（日期作为字符串）

症状: 无日期验证，排序损坏，无日期数学 修复:

-- ❌ 错误
CREATE TABLE events (
  event_date VARCHAR(50)  -- '2025-12-15' 或 'Dec 15, 2025'？
);

-- ✅ 正确
CREATE TABLE events (
  event_date DATE NOT NULL,  -- 已验证，可排序
  event_time TIMESTAMPTZ     -- 带时区
);

错误5：外键上无索引

症状: 极慢JOIN，差查询性能 修复:

-- 始终索引外键
CREATE TABLE order_items (
  order_id UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id)
);

-- ✅ 必需索引
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

错误6：缺少审计列

症状: 无法跟踪记录创建/修改时间 修复:

-- ❌ 错误
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200)
);

-- ✅ 正确
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- 自动更新触发器（PostgreSQL）
CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

错误7：EAV反模式

症状: 复杂查询，无类型安全，慢性能 修复:

-- ❌ 错误（EAV）
CREATE TABLE product_attributes (
  product_id UUID,
  attribute_name VARCHAR(100),  -- 'color', 'size', 'price'
  attribute_value TEXT           -- 一切作为文本！
);

-- ✅ 正确（结构化 + JSONB）
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,  -- 必填字段作为列
  color VARCHAR(50),              -- 常见属性作为列
  size VARCHAR(20),
  attributes JSONB                -- 可选/动态属性
);

-- 索引JSONB
CREATE INDEX idx_products_attributes ON products USING GIN(attributes);

加载 references/error-catalog.md 获取所有12个错误的详细修复。

---

常见模式模式

模式	使用案例	模板
基本CRUD	标准用户/产品/订单	templates/basic-schema.sql
一对一	用户 → 个人资料	templates/relationships.sql（第7-17行）
一对多	用户 → 订单	templates/relationships.sql（第23-34行）
多对多	学生 ↔ 课程	templates/relationships.sql（第40-60行）
层次结构	类别树，组织图	templates/relationships.sql（第66-83行）
软删除	标记删除，保留历史	templates/audit-columns.sql（第55-80行）
版本控制	跟踪随时间变化	templates/audit-columns.sql（第86-108行）
多租户	每个组织的数据隔离	references/schema-design-patterns.md（第228-258行）

---

规范化快速参考

范式	规则	示例
1NF	原子值，无重复组	phone1, phone2 → phones 表
2NF	1NF + 无部分依赖	复合键依赖 → 单独表
3NF	2NF + 无传递依赖	user.city → city.id 引用
BCNF	3NF + 每个决定因素都是候选键	罕见边缘案例
4NF	BCNF + 无多值依赖	复杂多对多
5NF	4NF + 无连接依赖	非常罕见，学术性

推荐: 设计到3NF，仅在有测量性能数据时反规范化。

加载 references/normalization-guide.md 获取详细前后示例。

---

配置总结

PostgreSQL 推荐类型

-- 主键
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- 或针对性能关键：
id BIGSERIAL PRIMARY KEY

-- 文本
name VARCHAR(200) NOT NULL
description TEXT
code CHAR(10)  -- 仅固定长度代码

-- 数字
price DECIMAL(10,2) NOT NULL  -- 金钱：绝不要用FLOAT
quantity INT NOT NULL
rating DECIMAL(3,2)  -- 0.00 到 9.99

-- 日期/时间
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL  -- 带时区
event_date DATE
duration INTERVAL

-- 布尔
is_active BOOLEAN DEFAULT true NOT NULL

-- JSON
attributes JSONB  -- 二进制，更快，可索引

-- 枚举替代（优于ENUM类型）
status VARCHAR(20) NOT NULL
  CHECK (status IN ('draft', 'active', 'archived'))

MySQL 差异

-- MySQL 没有：
TIMESTAMPTZ  -- 使用 TIMESTAMP（存储为UTC）
gen_random_uuid()  -- 使用 UUID() 函数
JSONB  -- 使用 JSON（8.0+ 性能相同）

-- MySQL 等价：
id CHAR(36) PRIMARY KEY DEFAULT (UUID())
-- 或：
id BIGINT AUTO_INCREMENT PRIMARY KEY

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
attributes JSON

加载 references/data-types-guide.md 获取全面类型选择指南。

---

何时加载参考

模式设计过程

加载 references/schema-design-patterns.md 当：

• 开始新数据库设计
• 需要模式示例（审计列、软删除、版本控制）
• 实现多租户
• 选择UUID对比BIGSERIAL
• 遵循命名约定

规范化

加载 references/normalization-guide.md 当：

• 模式有数据重复
• 不确定在哪个范式
• 需要规范化现有模式
• 规划数据库结构

关系

加载 references/relationship-patterns.md 当：

• 定义表关系
• 实现连接表
• 创建层次结构
• 设置级联规则

数据类型

加载 references/data-types-guide.md 当：

• 选择列类型
• 在PostgreSQL/MySQL之间迁移
• 优化存储
• 实现JSON字段

约束

加载 references/constraints-catalog.md 当：

• 添加验证规则
• 实现CHECK约束
• 设置外键级联
• 创建唯一约束

错误预防

加载 references/error-catalog.md 当：

• 需要模式审查
• 故障排除模式问题
• 所有12个文档错误及修复

---

完整设置检查清单

创建表之前:

• [ ] 规范化到至少3NF
• [ ] 识别所有关系
• [ ] 选择适当数据类型
• [ ] 定义级联规则

每个表必须有:

• [ ] 定义主键
• [ ] 审计列（created_at, updated_at）
• [ ] 在必填字段上使用NOT NULL
• [ ] 适当VARCHAR长度（非MAX）
• [ ] 对枚举/范围使用CHECK约束

外键:

• [ ] 所有外键用REFERENCES定义
• [ ] 指定ON DELETE/UPDATE操作
• [ ] 所有外键索引

索引:

• [ ] 外键索引
• [ ] 频繁查询列索引
• [ ] 多列查询的复合索引

验证:

• [ ] 无循环依赖
• [ ] 无EAV模式
• [ ] 无多态关联
• [ ] 适当数据类型（无日期作为字符串）

---

生产示例

之前（多个问题）：

CREATE TABLE users (
  email VARCHAR(MAX),           -- 问题：无主键，VARCHAR(MAX)
  password VARCHAR(MAX),
  created VARCHAR(50)           -- 问题：日期作为字符串
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_email VARCHAR(MAX),      -- 问题：无外键
  total VARCHAR(20),            -- 问题：金钱作为字符串
  status VARCHAR(MAX)           -- 问题：无验证
);

之后（生产就绪）：

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status VARCHAR(20) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'canceled')),
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

结果: ✅ 所有约束强制执行，适当类型，索引，可审计

---

已知问题预防

所有12个文档错误预防：

1. ✅ 缺少主键 → 必需UUID/BIGSERIAL
2. ✅ 无外键约束 → 必需REFERENCES
3. ✅ 到处VARCHAR(MAX) → 适当长度
4. ✅ 无理由反规范化 → 至少3NF
5. ✅ 缺少NOT NULL约束 → 必填字段标记
6. ✅ 外键上无索引 → 所有外键索引
7. ✅ 错误数据类型 → 适当类型选择
8. ✅ 缺少CHECK约束 → 验证规则
9. ✅ 无审计列 → 必需created_at/updated_at
10. ✅ 循环依赖 → 依赖分析
11. ✅ 缺少ON DELETE/UPDATE级联 → 级联规则
12. ✅ EAV反模式 → 结构化模式 + JSONB

参见: references/error-catalog.md 获取详细修复

---

资源

模板:

• templates/basic-schema.sql - 用户、产品、订单入门
• templates/relationships.sql - 所有关系类型
• templates/constraints.sql - 约束示例
• templates/audit-columns.sql - 审计模式 + 触发器

参考:

• references/normalization-guide.md - 1NF到5NF详细
• references/relationship-patterns.md - 关系类型
• references/data-types-guide.md - PostgreSQL对比MySQL类型
• references/constraints-catalog.md - 所有约束
• references/schema-design-patterns.md - 最佳实践
• references/error-catalog.md - 所有12个错误文档

官方文档:

• PostgreSQL 数据类型: https://www.postgresql.org/docs/current/datatype.html
• PostgreSQL 约束: https://www.postgresql.org/docs/current/ddl-constraints.html
• MySQL 数据类型: https://dev.mysql.com/doc/refman/8.0/en/data-types.html

---

生产测试 | 12个错误预防 | MIT许可证