schema-designer

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Schema Designer

Schema设计器

Help users design database schemas, create tables, and model data relationships.

帮助用户设计数据库Schema、创建数据表以及建模数据关系。

When to Use

使用场景

Activate when user asks:

"Create a table for storing orders"
"Design a schema for a blog"
"Add a column to track user preferences"
"How should I model this relationship?"

当用户提出以下请求时触发：

"创建用于存储订单的数据表"
"为博客设计Schema"
"添加列以跟踪用户偏好"
"我应该如何为这种关系建模？"

Workflow

工作流程

1. Understand Requirements

1. 理解需求

Ask clarifying questions:

What data needs to be stored?
What are the relationships between entities?
What queries will be common?
What's the expected data volume?

提出澄清问题：

需要存储哪些数据？
实体之间的关系是什么？
常见的查询有哪些？
预期的数据量是多少？

2. Check Existing Schema

2. 检查现有Schema

whodb_tables() → See what already exists
whodb_columns(table="related_table") → Understand existing structure

whodb_tables() → 查看已存在的表
whodb_columns(table="related_table") → 了解现有结构

3. Design the Schema

3. 设计Schema

Follow database design principles:

Normalize to reduce redundancy
Use appropriate data types
Define primary keys
Establish foreign key relationships
Add indexes for common queries

遵循数据库设计原则：

规范化以减少冗余
使用合适的数据类型
定义主键
建立外键关系
为常见查询添加索引

4. Generate DDL

4. 生成DDL

Provide CREATE TABLE statements with explanations.

提供带解释的CREATE TABLE语句。

Data Type Guidelines

数据类型指南

Identifiers

标识符

Use Case	PostgreSQL	MySQL	SQLite
Auto-increment ID	`SERIAL` / `BIGSERIAL`	`INT AUTO_INCREMENT`	`INTEGER PRIMARY KEY`
UUID	`UUID`	`CHAR(36)`	`TEXT`

使用场景	PostgreSQL	MySQL	SQLite
自增ID	`SERIAL` / `BIGSERIAL`	`INT AUTO_INCREMENT`	`INTEGER PRIMARY KEY`
UUID	`UUID`	`CHAR(36)`	`TEXT`

Text

文本

Use Case	PostgreSQL	MySQL	SQLite
Short text (<255)	`VARCHAR(n)`	`VARCHAR(n)`	`TEXT`
Long text	`TEXT`	`TEXT`	`TEXT`
Fixed length	`CHAR(n)`	`CHAR(n)`	`TEXT`

使用场景	PostgreSQL	MySQL	SQLite
短文本(<255)	`VARCHAR(n)`	`VARCHAR(n)`	`TEXT`
长文本	`TEXT`	`TEXT`	`TEXT`
固定长度	`CHAR(n)`	`CHAR(n)`	`TEXT`

Numbers

数字

Use Case	PostgreSQL	MySQL	SQLite
Integer	`INTEGER`	`INT`	`INTEGER`
Big integer	`BIGINT`	`BIGINT`	`INTEGER`
Decimal (money)	`NUMERIC(10,2)`	`DECIMAL(10,2)`	`REAL`
Float	`REAL`	`FLOAT`	`REAL`

使用场景	PostgreSQL	MySQL	SQLite
整数	`INTEGER`	`INT`	`INTEGER`
大整数	`BIGINT`	`BIGINT`	`INTEGER`
小数(金额)	`NUMERIC(10,2)`	`DECIMAL(10,2)`	`REAL`
浮点数	`REAL`	`FLOAT`	`REAL`

Dates

日期

Use Case	PostgreSQL	MySQL	SQLite
Date only	`DATE`	`DATE`	`TEXT`
Timestamp	`TIMESTAMP`	`DATETIME`	`TEXT`
With timezone	`TIMESTAMPTZ`	`TIMESTAMP`	`TEXT`

使用场景	PostgreSQL	MySQL	SQLite
仅日期	`DATE`	`DATE`	`TEXT`
时间戳	`TIMESTAMP`	`DATETIME`	`TEXT`
带时区	`TIMESTAMPTZ`	`TIMESTAMP`	`TEXT`

Boolean

布尔值

PostgreSQL	MySQL	SQLite
`BOOLEAN`	`TINYINT(1)`	`INTEGER`

PostgreSQL	MySQL	SQLite
`BOOLEAN`	`TINYINT(1)`	`INTEGER`

Common Patterns

常见模式

Users Table

用户表

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

sql

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_email ON users(email);

One-to-Many (Orders → Order Items)

一对多（订单 → 订单项）

sql

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total NUMERIC(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price NUMERIC(10,2) NOT NULL
);

CREATE INDEX idx_order_items_order ON order_items(order_id);

sql

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'pending',
    total NUMERIC(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL DEFAULT 1,
    unit_price NUMERIC(10,2) NOT NULL
);

CREATE INDEX idx_order_items_order ON order_items(order_id);

Many-to-Many (Users ↔ Roles)

多对多（用户 ↔ 角色）

sql

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

CREATE TABLE user_roles (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

sql

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

CREATE TABLE user_roles (
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

Soft Delete Pattern

软删除模式

sql

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    deleted_at TIMESTAMP NULL,  -- NULL = not deleted
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Query active posts
SELECT * FROM posts WHERE deleted_at IS NULL;

sql

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT,
    deleted_at TIMESTAMP NULL,  -- NULL = 未删除
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查询未删除的帖子
SELECT * FROM posts WHERE deleted_at IS NULL;

Audit Trail Pattern

审计追踪模式

sql

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id INTEGER NOT NULL,
    action VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE
    old_values JSONB,
    new_values JSONB,
    user_id INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);

sql

CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    table_name VARCHAR(50) NOT NULL,
    record_id INTEGER NOT NULL,
    action VARCHAR(10) NOT NULL,  -- INSERT, UPDATE, DELETE
    old_values JSONB,
    new_values JSONB,
    user_id INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_table_record ON audit_log(table_name, record_id);

Best Practices

最佳实践

Always define PRIMARY KEY - Every table needs one
Use foreign keys - Enforce referential integrity
Add NOT NULL - Unless the column is truly optional
Create indexes - On foreign keys and frequently queried columns
Use appropriate types - Don't store numbers as strings
Add timestamps -
```
created_at
```
and
```
updated_at
```
are almost always useful
Name consistently -
```
user_id
```
not
```
userId
```
or
```
UserID
```
Avoid reserved words - Don't name columns
```
order
```
,
```
user
```
,
```
group
```

始终定义PRIMARY KEY - 每个表都需要主键
使用外键 - 强制引用完整性
添加NOT NULL约束 - 除非该列确实是可选的
创建索引 - 在外键和频繁查询的列上
使用合适的数据类型 - 不要将数字存储为字符串
添加时间戳 -
```
created_at
```
和
```
updated_at
```
几乎总是有用的
命名保持一致 - 使用
```
user_id
```
而非
```
userId
```
或
```
UserID
```
避免保留字 - 不要将列命名为
```
order
```
、
```
user
```
、
```
group
```

Migration Safety

迁移安全

When modifying existing tables:

sql

-- Safe: Adding nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Safe: Adding column with default
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT true;

-- Caution: Adding NOT NULL (requires default or backfill)
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- Caution: Dropping column (data loss)
ALTER TABLE users DROP COLUMN old_column;

-- Caution: Changing type (may fail on existing data)
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;

修改现有表时：

sql

-- 安全操作：添加可为空的列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 安全操作：添加带默认值的列
ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT true;

-- 注意：添加NOT NULL约束（需要默认值或回填数据）
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';

-- 注意：删除列（会丢失数据）
ALTER TABLE users DROP COLUMN old_column;

-- 注意：修改数据类型（可能因现有数据而失败）
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;