schema-designer

架构设计器

帮助用户设计数据库架构、创建表并建立数据关系模型。

适用场景

在用户提出以下请求时启用：

• “创建一个用于存储订单的表”
• “为博客设计一个架构”
• “添加一个用于跟踪用户偏好的列”
• “这种关系应该如何建模？”

工作流

1. 了解需求

提出澄清问题：

• 需要存储哪些数据？
• 实体之间有哪些关系？
• 常见查询有哪些？
• 预计数据量有多大？

2. 检查现有架构

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

3. 设计架构

遵循数据库设计原则：

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

4. 生成 DDL

提供 CREATE TABLE 语句并加以说明。

数据类型指南

标识符

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

文本

用例	PostgreSQL	MySQL	SQLite
短文本（<255）	VARCHAR(n)	VARCHAR(n)	TEXT
长文本	TEXT	TEXT	TEXT
定长文本	CHAR(n)	CHAR(n)	TEXT

数字

用例	PostgreSQL	MySQL	SQLite
整数	INTEGER	INT	INTEGER
大整数	BIGINT	BIGINT	INTEGER
小数（货币）	NUMERIC(10,2)	DECIMAL(10,2)	REAL
浮点数	REAL	FLOAT	REAL

日期

用例	PostgreSQL	MySQL	SQLite
仅日期	DATE	DATE	TEXT
时间戳	TIMESTAMP	DATETIME	TEXT
带时区	TIMESTAMPTZ	TIMESTAMP	TEXT

布尔值

PostgreSQL	MySQL	SQLite
BOOLEAN	TINYINT(1)	INTEGER

常见模式

用户表

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

一对多（订单 → 订单项）

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

多对多（用户 ↔ 角色）

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

软删除模式

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;

审计跟踪模式

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

最佳实践

1. 始终定义 PRIMARY KEY - 每张表都需要主键
2. 使用外键 - 保证引用完整性
3. 添加 NOT NULL - 除非该列确实可选
4. 创建索引 - 为外键和经常查询的列创建索引
5. 使用合适的类型 - 不要将数字存储为字符串
6. 添加时间戳 - created_at 和 updated_at 几乎总是有用
7. 统一命名 - 使用 user_id，而不是 userId 或 UserID
8. 避免使用保留字 - 不要将列命名为 order、user、group

迁移安全性

修改现有表时：

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