database-schema-design

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

database-schema-design

数据库Schema设计

Comprehensive database schema design patterns for PostgreSQL and MySQL with normalization, relationships, constraints, and error prevention.

这是一份针对PostgreSQL和MySQL的全面数据库Schema设计指南，涵盖规范化、关系、约束以及错误预防相关内容。

Quick Start (10 Minutes)

快速入门（10分钟）

Step 1: Choose your schema pattern from templates:

bash

undefined

步骤1：从模板中选择合适的Schema模式：

bash

undefined

Basic schema with users, products, orders

包含用户、产品、订单的基础Schema

cat templates/basic-schema.sql

Relationship patterns (1:1, 1:M, M:M)

关系模式（1:1、1:多、多:多）

cat templates/relationships.sql

Constraint examples

约束示例

cat templates/constraints.sql

Audit patterns

审计模式

cat templates/audit-columns.sql


**Step 2**: Apply normalization rules (at minimum 3NF):
- **1NF**: No repeating groups, atomic values
- **2NF**: No partial dependencies on composite keys
- **3NF**: No transitive dependencies
- **Load** `references/normalization-guide.md` for detailed examples

**Step 3**: Add essential elements to every table:
```sql
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
);

cat templates/audit-columns.sql


**步骤2**：应用规范化规则（至少达到3NF）：
- **1NF**：无重复组，原子值
- **2NF**：复合主键无部分依赖
- **3NF**：无传递依赖
- 查看`references/normalization-guide.md`获取详细示例

**步骤3**：为每个表添加必要元素：
```sql
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
);

Critical Rules

核心规则

✓ Always Do

✓ 必须遵循

Rule	Reason
Every table has PRIMARY KEY	Ensures row uniqueness, enables relationships
Foreign keys defined explicitly	Enforces referential integrity, prevents orphans
Index all foreign keys	Prevents slow JOINs, critical for performance
NOT NULL on required fields	Data integrity, prevents NULL pollution
Audit columns (created_at, updated_at)	Track changes, debugging, compliance
Appropriate data types	Storage efficiency, validation, indexing
Check constraints for enums	Enforces valid values at database level
ON DELETE/UPDATE rules specified	Prevents accidental data loss or orphans

规则	原因
每个表都要有主键	确保行唯一性，支持关系定义
显式定义外键	强制引用完整性，防止孤立记录
为所有外键创建索引	避免慢JOIN，对性能至关重要
必填字段设置NOT NULL	保证数据完整性，防止NULL污染
包含审计列（created_at、updated_at）	追踪变更、调试、合规需求
使用合适的数据类型	存储高效、支持验证、便于索引
为枚举类型添加Check约束	在数据库层面强制有效值
指定ON DELETE/UPDATE规则	防止意外数据丢失或孤立记录

✗ Never Do

✗ 切勿触碰

Anti-Pattern	Why It's Bad
VARCHAR(MAX) everywhere	Wastes space, slows indexes, no validation
Dates as VARCHAR	No date math, no validation, sorting broken
Missing foreign keys	No referential integrity, orphaned records
Premature denormalization	Hard to maintain, data anomalies
EAV (Entity-Attribute-Value)	Query complexity, no type safety, slow
Polymorphic associations	No foreign key integrity, complex queries
Circular dependencies	Impossible to populate, breaks CASCADE
No indexes on foreign keys	Extremely slow JOINs, performance killer

反模式	危害
到处使用VARCHAR(MAX)	浪费空间、减慢索引、无验证机制
日期存储为VARCHAR	无法进行日期运算、无验证、排序失效
缺失外键	无引用完整性，产生孤立记录
过早反规范化	难以维护，出现数据异常
EAV（实体-属性-值）	查询复杂、无类型安全、性能低下
多态关联	无外键完整性，查询逻辑复杂
循环依赖	无法填充数据，破坏CASCADE规则
外键未加索引	JOIN操作极慢，严重影响性能

Top 7 Critical Errors

7大关键错误

Error 1: Missing Primary Key

错误1：缺失主键

Symptom: Cannot uniquely identify rows, duplicate data Fix:

sql

-- ❌ 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
);

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

sql

-- ❌ 错误示例
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
);

Error 2: No Foreign Key Constraints

错误2：无外键约束

Symptom: Orphaned records, data inconsistency Fix:

sql

-- ❌ 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);

症状：出现孤立记录，数据不一致 修复方案：

sql

-- ❌ 错误示例
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);

Error 3: VARCHAR(MAX) Everywhere

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

Symptom: Wasted space, slow indexes, no validation Fix:

sql

-- ❌ 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'))
);

症状：浪费空间、索引缓慢、无验证 修复方案：

sql

-- ❌ 错误示例
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'))
);

Error 4: Wrong Data Types (Dates as Strings)

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

Symptom: No date validation, broken sorting, no date math Fix:

sql

-- ❌ 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
);

症状：无法验证日期、排序失效、无法进行日期运算 修复方案：

sql

-- ❌ 错误示例
CREATE TABLE events (
  event_date VARCHAR(50)  -- 格式混乱：'2025-12-15' 或 'Dec 15, 2025'？
);

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

Error 5: No Indexes on Foreign Keys

错误5：外键未加索引

Symptom: Extremely slow JOINs, poor query performance Fix:

sql

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

症状：JOIN操作极慢，查询性能差 修复方案：

sql

-- 始终为外键创建索引
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);

Error 6: Missing Audit Columns

错误6：缺失审计列

Symptom: Cannot track when records created/modified Fix:

sql

-- ❌ 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();

症状：无法追踪记录的创建/修改时间 修复方案：

sql

-- ❌ 错误示例
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();

Error 7: EAV Anti-Pattern

错误7：EAV反模式

Symptom: Complex queries, no type safety, slow performance Fix:

sql

-- ❌ 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);

Load

references/error-catalog.md

for all 12 errors with detailed fixes.

症状：查询复杂、无类型安全、性能低下 修复方案：

sql

-- ❌ 错误示例（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种错误的详细修复方案。

Common Schema Patterns

常见Schema模式

Pattern	Use Case	Template
Basic CRUD	Standard users/products/orders	`templates/basic-schema.sql`
One-to-One	User → Profile	`templates/relationships.sql` (lines 7-17)
One-to-Many	User → Orders	`templates/relationships.sql` (lines 23-34)
Many-to-Many	Students ↔ Courses	`templates/relationships.sql` (lines 40-60)
Hierarchical	Categories tree, org chart	`templates/relationships.sql` (lines 66-83)
Soft Delete	Mark deleted, keep history	`templates/audit-columns.sql` (lines 55-80)
Versioning	Track changes over time	`templates/audit-columns.sql` (lines 86-108)
Multi-Tenant	Isolated data per organization	`references/schema-design-patterns.md` (lines 228-258)

模式	适用场景	模板
基础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行）

Normalization Quick Reference

规范化快速参考

Form	Rule	Example
1NF	Atomic values, no repeating groups	`phone1, phone2` → `phones` table
2NF	1NF + no partial dependencies	Composite key dependency → separate table
3NF	2NF + no transitive dependencies	`user.city` → `city.id` reference
BCNF	3NF + every determinant is candidate key	Rare edge cases
4NF	BCNF + no multi-valued dependencies	Complex many-to-many
5NF	4NF + no join dependencies	Very rare, academic

Recommendation: Design to 3NF, denormalize only with measured performance data.

Load

references/normalization-guide.md

for detailed examples with before/after.

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

建议：设计时至少达到3NF，仅在有明确性能数据支撑时才进行反规范化。

查看

references/normalization-guide.md

获取包含前后对比的详细示例。

Configuration Summary

配置汇总

PostgreSQL Recommended Types

PostgreSQL推荐类型

sql

-- 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'))

sql

-- 主键
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 Differences

MySQL差异点

sql

-- 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

Load

references/data-types-guide.md

for comprehensive type selection guide.

sql

-- 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

获取全面的类型选择指南。

When to Load References

何时查看参考文档

Schema Design Process

Schema设计流程

Load

references/schema-design-patterns.md

when:

Starting a new database design
Need pattern examples (audit columns, soft deletes, versioning)
Implementing multi-tenancy
Choosing between UUID vs BIGSERIAL
Following naming conventions

当以下场景时查看

references/schema-design-patterns.md

：

启动新数据库设计
需要模式示例（审计列、软删除、版本控制）
实现多租户
选择UUID还是BIGSERIAL
遵循命名规范

Normalization

规范化

Load

references/normalization-guide.md

when:

Schema has data duplication
Unsure what normal form you're in
Need to normalize existing schema
Planning database structure

当以下场景时查看

references/normalization-guide.md

：

Schema存在数据重复
不确定当前处于哪种范式
需要规范化现有Schema
规划数据库结构

Relationships

关系定义

Load

references/relationship-patterns.md

when:

Defining table relationships
Implementing junction tables
Creating hierarchical structures
Setting up cascade rules

当以下场景时查看

references/relationship-patterns.md

：

定义表间关系
实现关联表
创建层级结构
设置级联规则

Data Types

数据类型

Load

references/data-types-guide.md

when:

Choosing column types
Migrating between PostgreSQL/MySQL
Optimizing storage
Implementing JSON fields

当以下场景时查看

references/data-types-guide.md

：

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

Constraints

约束配置

Load

references/constraints-catalog.md

when:

Adding validation rules
Implementing CHECK constraints
Setting up foreign key cascades
Creating unique constraints

当以下场景时查看

references/constraints-catalog.md

：

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

Error Prevention

错误预防

Load

references/error-catalog.md

when:

Schema review needed
Troubleshooting schema issues
All 12 documented errors with fixes

当以下场景时查看

references/error-catalog.md

：

需要进行Schema评审
排查Schema问题
获取全部12种已记录错误的修复方案

Complete Setup Checklist

完整设置检查清单

Production Example

生产环境示例

Before (Multiple issues):

sql

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

After (Production-ready):

sql

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

Result: ✅ All constraints enforced, proper types, indexed, auditable

优化前（存在多个问题）：

sql

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)           -- 问题：无验证机制
);

优化后（生产可用）：

sql

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

结果：✅ 所有约束已生效、类型正确、已加索引、可审计

Known Issues Prevention

已知问题预防

All 12 documented errors prevented:

✅ Missing primary key → UUID/BIGSERIAL required
✅ No foreign key constraints → REFERENCES required
✅ VARCHAR(MAX) everywhere → Appropriate lengths
✅ Denormalization without justification → 3NF minimum
✅ Missing NOT NULL constraints → Required fields marked
✅ No indexes on foreign keys → All FKs indexed
✅ Wrong data types → Proper type selection
✅ Missing CHECK constraints → Validation rules
✅ No audit columns → created_at/updated_at required
✅ Circular dependencies → Dependency analysis
✅ Missing ON DELETE/UPDATE cascades → Cascade rules
✅ EAV anti-pattern → Structured schema + JSONB

See:

references/error-catalog.md

for detailed fixes

已覆盖12种已记录错误的预防方案：

✅ 缺失主键 → 强制使用UUID/BIGSERIAL
✅ 无外键约束 → 强制使用REFERENCES
✅ 到处使用VARCHAR(MAX) → 使用合适长度
✅ 无正当理由的反规范化 → 至少达到3NF
✅ 缺失NOT NULL约束 → 必填字段标记
✅ 外键未加索引 → 所有外键强制加索引
✅ 数据类型错误 → 正确选择类型
✅ 缺失Check约束 → 添加验证规则
✅ 缺失审计列 → 强制包含created_at/updated_at
✅ 循环依赖 → 进行依赖分析
✅ 缺失ON DELETE/UPDATE级联 → 定义级联规则
✅ EAV反模式 → 使用结构化Schema+JSONB

查看：

references/error-catalog.md

获取详细修复方案

Resources

资源

Templates:

```
templates/basic-schema.sql
```
- Users, products, orders starter
```
templates/relationships.sql
```
- All relationship types
```
templates/constraints.sql
```
- Constraint examples
```
templates/audit-columns.sql
```
- Audit patterns + triggers

References:

```
references/normalization-guide.md
```
- 1NF through 5NF detailed
```
references/relationship-patterns.md
```
- Relationship types
```
references/data-types-guide.md
```
- PostgreSQL vs MySQL types
```
references/constraints-catalog.md
```
- All constraints
```
references/schema-design-patterns.md
```
- Best practices
```
references/error-catalog.md
```
- All 12 errors documented

Official Documentation:

PostgreSQL Data Types: https://www.postgresql.org/docs/current/datatype.html
PostgreSQL Constraints: https://www.postgresql.org/docs/current/ddl-constraints.html
MySQL Data Types: https://dev.mysql.com/doc/refman/8.0/en/data-types.html

Production-tested | 12 errors prevented | MIT License

模板：

```
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许可证