database-schema-design

数据库架构设计

适用于 PostgreSQL 和 MySQL 的全面数据库架构设计模式，涵盖规范化、关系、约束和错误预防。

---

快速开始（10 分钟）

步骤 1：从模板中选择架构模式：

# Basic schema with users, products, orders
cat templates/basic-schema.sql

# Relationship patterns (1:1, 1:M, M:M)
cat templates/relationships.sql

# Constraint examples
cat templates/constraints.sql

# Audit patterns
cat templates/audit-columns.sql

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

• 1NF：不存在重复组，值具有原子性
• 2NF：不存在对复合键的部分依赖
• 3NF：不存在传递依赖
• 加载 references/normalization-guide.md 查看详细示例

步骤 3：为每个表添加必要元素：

CREATE TABLE your_table (
  -- Primary key (required)
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Business columns with proper types
  name VARCHAR(200) NOT NULL,  -- Use appropriate lengths

  -- Audit columns (always include)
  created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

---

关键规则

✓ 始终遵循

规则	原因
每个表都有 PRIMARY KEY	确保行的唯一性，支持建立关系
显式定义外键	确保引用完整性，避免产生孤立记录
为所有外键建立索引	避免 JOIN 速度缓慢，对性能至关重要
必填字段设置 NOT NULL	确保数据完整性，避免 NULL 污染
审计列（created_at、updated_at）	跟踪更改，便于调试并满足合规要求
使用恰当的数据类型	提高存储效率，支持验证和索引
为枚举值设置检查约束	在数据库层面确保值有效
指定 ON DELETE/UPDATE 规则	避免意外丢失数据或产生孤立记录

✗ 绝不这样做

反模式	危害
到处使用 VARCHAR(MAX)	浪费空间、拖慢索引，且无法验证
将日期存为 VARCHAR	无法进行日期运算、无法验证，排序也会出错
缺少外键	无法确保引用完整性，会产生孤立记录
过早反规范化	难以维护，并会导致数据异常
EAV（实体-属性-值）	查询复杂、缺少类型安全，且速度缓慢
多态关联	无法确保外键完整性，查询复杂
循环依赖	无法填充数据，会破坏 CASCADE
外键未建立索引	JOIN 极慢，严重影响性能

---

最常见的 7 个严重错误

错误 1：缺少主键

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

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

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

错误 2：没有外键约束

症状：存在孤立记录，数据不一致 修复：

-- ❌ Bad
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_id UUID  -- No constraint!
);

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

-- Index the foreign key
CREATE INDEX idx_orders_user_id ON orders(user_id);

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

症状：浪费空间、索引速度慢，且无法验证 修复：

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

-- ✅ Good
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：数据类型错误（将日期存为字符串）

症状：无法验证日期，排序出错，无法进行日期运算 修复：

-- ❌ Bad
CREATE TABLE events (
  event_date VARCHAR(50)  -- '2025-12-15' or 'Dec 15, 2025'?
);

-- ✅ Good
CREATE TABLE events (
  event_date DATE NOT NULL,  -- Validated, sortable
  event_time TIMESTAMPTZ     -- With timezone
);

错误 5：外键未建立索引

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

-- Always index foreign keys
CREATE TABLE order_items (
  order_id UUID NOT NULL REFERENCES orders(id),
  product_id UUID NOT NULL REFERENCES products(id)
);

-- ✅ Required indexes
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：缺少审计列

症状：无法跟踪记录的创建或修改时间 修复：

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

-- ✅ Good
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
);

-- Auto-update trigger (PostgreSQL)
CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

错误 7：EAV 反模式

症状：查询复杂、缺少类型安全，性能缓慢 修复：

-- ❌ Bad (EAV)
CREATE TABLE product_attributes (
  product_id UUID,
  attribute_name VARCHAR(100),  -- 'color', 'size', 'price'
  attribute_value TEXT           -- Everything as text!
);

-- ✅ Good (Structured + JSONB)
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  price DECIMAL(10,2) NOT NULL,  -- Required fields as columns
  color VARCHAR(50),              -- Common attributes as columns
  size VARCHAR(20),
  attributes JSONB                -- Optional/dynamic attributes
);

-- Index 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 推荐类型

-- Primary Keys
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
-- OR for performance-critical:
id BIGSERIAL PRIMARY KEY

-- Text
name VARCHAR(200) NOT NULL
description TEXT
code CHAR(10)  -- Fixed-length codes only

-- Numbers
price DECIMAL(10,2) NOT NULL  -- Money: NEVER use FLOAT
quantity INT NOT NULL
rating DECIMAL(3,2)  -- 0.00 to 9.99

-- Dates/Times
created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL  -- With timezone
event_date DATE
duration INTERVAL

-- Boolean
is_active BOOLEAN DEFAULT true NOT NULL

-- JSON
attributes JSONB  -- Binary, faster, indexable

-- Enum Alternative (preferred over ENUM type)
status VARCHAR(20) NOT NULL
  CHECK (status IN ('draft', 'active', 'archived'))

MySQL 差异

-- MySQL doesn't have:
TIMESTAMPTZ  -- Use TIMESTAMP (stored as UTC)
gen_random_uuid()  -- Use UUID() function
JSONB  -- Use JSON (same performance in 8.0+)

-- MySQL equivalent:
id CHAR(36) PRIMARY KEY DEFAULT (UUID())
-- OR:
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),           -- Issue: No primary key, VARCHAR(MAX)
  password VARCHAR(MAX),
  created VARCHAR(50)           -- Issue: Date as string
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  user_email VARCHAR(MAX),      -- Issue: No foreign key
  total VARCHAR(20),            -- Issue: Money as string
  status VARCHAR(MAX)           -- Issue: No validation
);

之后（可用于生产）：

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. ✅ 外键没有索引 → 为所有 FK 建立索引
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 许可证