schema-designer（模式设计师）

Schema Designer

Design relational database schemas with proper structure, relationships, and constraints.

Quick Start

Identify entities, define relationships, normalize to 3NF, add constraints and indexes.

Instructions

Schema Design Process

1. Identify entities (tables)
2. Define attributes (columns)
3. Establish relationships (foreign keys)
4. Apply normalization
5. Add constraints
6. Create indexes

Entity Identification

Main entities:

• Core business objects
• Things that need to be stored
• Independent concepts

Example - E-commerce:

• Users
• Products
• Orders
• Categories
• Reviews

Table Definition

Basic table structure:

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

Relationships

One-to-Many:

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

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:

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

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

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

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

Constraints

Primary Key:

id SERIAL PRIMARY KEY
-- Or composite
PRIMARY KEY (user_id, post_id)

Foreign Key:

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:

email VARCHAR(255) UNIQUE NOT NULL
-- Or composite unique
UNIQUE (user_id, product_id)

Not Null:

name VARCHAR(100) NOT NULL

Check:

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:

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
status VARCHAR(20) DEFAULT 'pending'
is_active BOOLEAN DEFAULT true

Indexes

Single column:

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created_at ON posts(created_at);

Composite index:

CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

Unique index:

CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

Partial index:

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

Complete Example - Blog Platform

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

Common Patterns

Soft Deletes

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;

Audit Trail

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

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)

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

Polymorphic Associations

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

Denormalization Patterns

Caching counts:

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:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    subtotal DECIMAL(10,2),
    tax DECIMAL(10,2),
    total DECIMAL(10,2)  -- Denormalized: 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

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