Back to Details

schema-designer

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

Schema Designer

Schema 设计器

Design relational database schemas with proper structure, relationships, and constraints.
设计具备合理结构、关系和约束的关系型数据库Schema。

Quick Start

快速开始

Identify entities, define relationships, normalize to 3NF, add constraints and indexes.
识别实体、定义关系、规范化至3NF、添加约束与索引。

Instructions

操作指南

Schema Design Process

Schema 设计流程

  1. Identify entities (tables)
  2. Define attributes (columns)
  3. Establish relationships (foreign keys)
  4. Apply normalization
  5. Add constraints
  6. Create indexes
  1. 识别实体（表）
  2. 定义属性（列）
  3. 建立关系（外键）
  4. 应用规范化
  5. 添加约束
  6. 创建索引

Entity Identification

实体识别

Main entities:
  • Core business objects
  • Things that need to be stored
  • Independent concepts
Example - E-commerce:
  • Users
  • Products
  • Orders
  • Categories
  • Reviews
主要实体：
  • 核心业务对象
  • 需要存储的内容
  • 独立概念
示例 - 电商场景：
  • 用户（Users）
  • 产品（Products）
  • 订单（Orders）
  • 分类（Categories）
  • 评论（Reviews）

Table Definition

表定义

Basic table structure:
sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Data types (PostgreSQL):
  • SERIAL
    : Auto-incrementing integer
  • INTEGER
    : Whole numbers
  • BIGINT
    : Large integers
  • VARCHAR(n)
    : Variable-length string
  • TEXT
    : Unlimited text
  • BOOLEAN
    : True/false
  • TIMESTAMP
    : Date and time
  • DATE
    : Date only
  • JSON/JSONB
    : JSON data
  • DECIMAL(p,s)
    : Precise decimals
基础表结构：
sql
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
数据类型（PostgreSQL）：
  • SERIAL
    : 自增整数
  • INTEGER
    : 整数
  • BIGINT
    : 大整数
  • VARCHAR(n)
    : 可变长度字符串
  • TEXT
    : 无限制文本
  • BOOLEAN
    : 布尔值（真/假）
  • TIMESTAMP
    : 日期时间
  • DATE
    : 仅日期
  • JSON/JSONB
    : JSON数据
  • DECIMAL(p,s)
    : 精确小数

Relationships

关系设计

One-to-Many:
sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Many-to-Many (junction table):
sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE product_tags (
    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);
One-to-One:
sql
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio TEXT,
    avatar_url VARCHAR(500),
    phone VARCHAR(20)
);
一对多：
sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    title VARCHAR(200) NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
多对多（关联表）：
sql
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);

CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE product_tags (
    product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, tag_id)
);
一对一：
sql
CREATE TABLE user_profiles (
    user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    bio TEXT,
    avatar_url VARCHAR(500),
    phone VARCHAR(20)
);

Normalization

规范化设计

First Normal Form (1NF):
  • Atomic values (no arrays in cells)
  • Each column has unique name
  • Order doesn't matter
sql
-- Bad: Multiple values in one column
CREATE TABLE users (
    id INTEGER,
    phones VARCHAR(200)  -- "555-1234, 555-5678"
);

-- Good: Separate table
CREATE TABLE user_phones (
    user_id INTEGER REFERENCES users(id),
    phone VARCHAR(20)
);
Second Normal Form (2NF):
  • Must be in 1NF
  • No partial dependencies
sql
-- Bad: Order details depend on part of composite key
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    product_name VARCHAR(200),  -- Depends only on product_id
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- Good: Product name in products table
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);
Third Normal Form (3NF):
  • Must be in 2NF
  • No transitive dependencies
sql
-- Bad: City depends on zip_code
CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    street VARCHAR(200),
    zip_code VARCHAR(10),
    city VARCHAR(100)  -- Depends on zip_code
);

-- Good: Separate zip_codes table
CREATE TABLE zip_codes (
    code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(100),
    state VARCHAR(2)
);

CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    street VARCHAR(200),
    zip_code VARCHAR(10) REFERENCES zip_codes(code)
);
第一范式（1NF）：
  • 原子值（单元格中无数组）
  • 每列具有唯一名称
  • 顺序无关
sql
-- 不良示例：单个列包含多个值
CREATE TABLE users (
    id INTEGER,
    phones VARCHAR(200)  -- "555-1234, 555-5678"
);

-- 优化示例：拆分至独立表
CREATE TABLE user_phones (
    user_id INTEGER REFERENCES users(id),
    phone VARCHAR(20)
);
第二范式（2NF）：
  • 需满足1NF
  • 无部分依赖
sql
-- 不良示例：订单详情依赖复合主键的部分字段
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    product_name VARCHAR(200),  -- 仅依赖product_id
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- 优化示例：产品名称存储在产品表中
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);
第三范式（3NF）：
  • 需满足2NF
  • 无传递依赖
sql
-- 不良示例：城市依赖邮政编码
CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    street VARCHAR(200),
    zip_code VARCHAR(10),
    city VARCHAR(100)  -- 依赖zip_code
);

-- 优化示例：拆分邮政编码表
CREATE TABLE zip_codes (
    code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(100),
    state VARCHAR(2)
);

CREATE TABLE addresses (
    id INTEGER PRIMARY KEY,
    street VARCHAR(200),
    zip_code VARCHAR(10) REFERENCES zip_codes(code)
);

Constraints

约束设置

Primary Key:
sql
id SERIAL PRIMARY KEY
-- Or composite
PRIMARY KEY (user_id, post_id)
Foreign Key:
sql
user_id INTEGER REFERENCES users(id)
-- With cascade
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
-- With restrict
user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT
Unique:
sql
email VARCHAR(255) UNIQUE NOT NULL
-- Or composite unique
UNIQUE (user_id, product_id)
Not Null:
sql
name VARCHAR(100) NOT NULL
Check:
sql
age INTEGER CHECK (age >= 0 AND age <= 150)
price DECIMAL(10,2) CHECK (price > 0)
status VARCHAR(20) CHECK (status IN ('pending', 'active', 'cancelled'))
Default:
sql
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
status VARCHAR(20) DEFAULT 'pending'
is_active BOOLEAN DEFAULT true
主键：
sql
id SERIAL PRIMARY KEY
-- 或复合主键
PRIMARY KEY (user_id, post_id)
外键：
sql
user_id INTEGER REFERENCES users(id)
-- 级联删除
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
-- 限制删除
user_id INTEGER REFERENCES users(id) ON DELETE RESTRICT
唯一约束：
sql
email VARCHAR(255) UNIQUE NOT NULL
-- 或复合唯一约束
UNIQUE (user_id, product_id)
非空约束：
sql
name VARCHAR(100) NOT NULL
检查约束：
sql
age INTEGER CHECK (age >= 0 AND age <= 150)
price DECIMAL(10,2) CHECK (price > 0)
status VARCHAR(20) CHECK (status IN ('pending', 'active', 'cancelled'))
默认值：
sql
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
status VARCHAR(20) DEFAULT 'pending'
is_active BOOLEAN DEFAULT true

Indexes

索引创建

Single column:
sql
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created_at ON posts(created_at);
Composite index:
sql
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);
Unique index:
sql
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
Partial index:
sql
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
When to index:
  • Foreign keys
  • Columns in WHERE clauses
  • Columns in JOIN conditions
  • Columns in ORDER BY
  • Columns in GROUP BY
When not to index:
  • Small tables
  • Columns with low cardinality
  • Frequently updated columns
  • Rarely queried columns
单列索引：
sql
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created_at ON posts(created_at);
复合索引：
sql
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);
唯一索引：
sql
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
部分索引：
sql
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;
何时创建索引：
  • 外键字段
  • WHERE子句中的列
  • JOIN条件中的列
  • ORDER BY中的列
  • GROUP BY中的列
何时不创建索引：
  • 小型表
  • 低基数列
  • 频繁更新的列
  • 极少查询的列

Complete Example - Blog Platform

完整示例 - 博客平台

sql
-- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- Posts table
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_status_published ON posts(status, published_at);

-- Comments table
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);
CREATE INDEX idx_comments_parent_id ON comments(parent_id);

-- Tags table
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

-- Post-Tag junction table
CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);
sql
-- 用户表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);

-- 文章表
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_posts_status_published ON posts(status, published_at);

-- 评论表
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);
CREATE INDEX idx_comments_parent_id ON comments(parent_id);

-- 标签表
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);

-- 文章-标签关联表
CREATE TABLE post_tags (
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);

Common Patterns

常见设计模式

Soft Deletes

软删除

sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    deleted_at TIMESTAMP NULL
);

-- Query only non-deleted
SELECT * FROM posts WHERE deleted_at IS NULL;
sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    deleted_at TIMESTAMP NULL
);

-- 查询未删除数据
SELECT * FROM posts WHERE deleted_at IS NULL;

Audit Trail

审计追踪

sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    created_by INTEGER REFERENCES users(id),
    updated_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    created_by INTEGER REFERENCES users(id),
    updated_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Versioning

版本控制

sql
CREATE TABLE document_versions (
    id SERIAL PRIMARY KEY,
    document_id INTEGER REFERENCES documents(id),
    version INTEGER NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (document_id, version)
);
sql
CREATE TABLE document_versions (
    id SERIAL PRIMARY KEY,
    document_id INTEGER REFERENCES documents(id),
    version INTEGER NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (document_id, version)
);

Hierarchical Data (Adjacency List)

分层数据（邻接表）

sql
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    parent_id INTEGER REFERENCES categories(id)
);
sql
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    parent_id INTEGER REFERENCES categories(id)
);

Polymorphic Associations

多态关联

sql
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    commentable_type VARCHAR(50),  -- 'Post' or 'Photo'
    commentable_id INTEGER,
    content TEXT
);

CREATE INDEX idx_comments_polymorphic ON comments(commentable_type, commentable_id);
sql
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    commentable_type VARCHAR(50),  -- 'Post' 或 'Photo'
    commentable_id INTEGER,
    content TEXT
);

CREATE INDEX idx_comments_polymorphic ON comments(commentable_type, commentable_id);

Denormalization Patterns

反规范化模式

Caching counts:
sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    comment_count INTEGER DEFAULT 0  -- Denormalized
);

-- Update with trigger or application code
Storing computed values:
sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    subtotal DECIMAL(10,2),
    tax DECIMAL(10,2),
    total DECIMAL(10,2)  -- Denormalized: subtotal + tax
);
缓存计数：
sql
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    comment_count INTEGER DEFAULT 0  -- 反规范化字段
);

-- 通过触发器或应用代码更新
存储计算值：
sql
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    subtotal DECIMAL(10,2),
    tax DECIMAL(10,2),
    total DECIMAL(10,2)  -- 反规范化字段：subtotal + tax
);

Best Practices

最佳实践

Naming conventions:
  • Tables: plural nouns (
    users
    , 
    posts
    )
  • Columns: snake_case (
    created_at
    , 
    user_id
    )
  • Indexes: 
    idx_table_column
  • Foreign keys: 
    fk_table_column
Always include:
  • Primary key on every table
  • Timestamps (created_at, updated_at)
  • Appropriate constraints
Use appropriate types:
  • VARCHAR for limited strings
  • TEXT for unlimited text
  • TIMESTAMP for dates with time
  • DECIMAL for money
Index strategically:
  • Foreign keys
  • Frequently queried columns
  • Don't over-index
命名规范：
  • 表名：复数名词（
    users
    , 
    posts
    ）
  • 列名：蛇形命名法（
    created_at
    , 
    user_id
    ）
  • 索引名：
    idx_table_column
  • 外键名：
    fk_table_column
建议始终包含：
  • 每张表都设置主键
  • 时间戳字段（created_at, updated_at）
  • 合适的约束
使用合适的数据类型：
  • 有限长度字符串使用VARCHAR
  • 无限制文本使用TEXT
  • 带时间的日期使用TIMESTAMP
  • 金额使用DECIMAL
策略性创建索引：
  • 为外键创建索引
  • 为频繁查询的列创建索引
  • 避免过度索引

Troubleshooting

故障排除

Slow queries:
  • Add indexes on WHERE/JOIN columns
  • Check for N+1 queries
  • Use EXPLAIN to analyze
Data integrity issues:
  • Add foreign key constraints
  • Use CHECK constraints
  • Add NOT NULL where appropriate
Storage bloat:
  • Review denormalization
  • Archive old data
  • Use appropriate data types
查询缓慢：
  • 为WHERE/JOIN列添加索引
  • 检查是否存在N+1查询问题
  • 使用EXPLAIN分析查询
数据完整性问题：
  • 添加外键约束
  • 使用CHECK约束
  • 在必要字段添加NOT NULL约束
存储膨胀：
  • 评估反规范化的必要性
  • 归档旧数据
  • 使用合适的数据类型