name: design-db description: [PRD2PLAN] 数据库表结构设计规范。涵盖命名约定、数据类型、索引设计、约束、关系映射、软删除、迁移管理。适用于所有后端技术栈的数据库设计与评审。 allowed-tools: disable: false

7w4.net有更好的技能插件。

数据库设计规范

一、命名约定

表命名

• 小写 + 下划线：users、order_items
• 复数名词表示集合：orders、projects、attachments
• 关联表：{表A}_{表B}（按字母序）：role_user、project_tag
• 可按模块加前缀：{module}_xxx（如 biz_orders、sys_config），项目内统一即可
• 禁止：中文字段名、大小写混用、table1 类无意义命名

列命名

• 小写 + 下划线：user_name、created_at
• 主键统一：id（自增 BIGINT）或 UUID 字符串（如 id VARCHAR(36)）
• 外键：{表名单数}_id：user_id → users.id
• 布尔标记：is_xxx：is_active、is_deleted
• 时间：xxx_at：created_at、approved_at
• 金额：xxx_amount，单位分或保留 2 位小数

索引命名

二、必备字段

每张业务表建议包含

审计字段选型

规则：一个项目内只能选一种，禁止混用。审计字段名从项目现有约定中提取，新项目自由选择。

三、数据类型

CHAR vs VARCHAR

• CHAR 仅用于固定长度值（如 MD5: CHAR(32)、手机号、身份证号）
• 其余一律 VARCHAR

四、索引设计

必须建索引的场景

• WHERE 条件列
• JOIN 的 ON 列（外键）
• ORDER BY 列
• GROUP BY 列
• 唯一业务键（UNIQUE 约束天然是索引）

组合索引原则

1. 等值条件在前，范围条件在后
2. 区分度高的列在前
3. 最左前缀匹配

-- ✅ 查询: WHERE user_id = ? AND status = ? ORDER BY created_at
CREATE INDEX idx_orders_user_status_created ON orders(user_id, status, created_at);

-- ❌ 不符合最左前缀
CREATE INDEX idx_orders_created_user_status ON orders(created_at, user_id, status);

索引禁忌

• 不要为每个列单独建索引（浪费空间、拖慢写入）
• 不要在低基数列（如性别、type≤3）上建单列索引
• 不要对大字段建索引（TEXT、长 VARCHAR）
• 组合索引建议不超过 5 列

覆盖索引

查询只需索引中的列时，避免回表：

CREATE INDEX idx_orders_user_status_id_amount ON orders(user_id, status, id, amount);
-- 查询 SELECT id, amount FROM orders WHERE user_id=? AND status=? 时覆盖

五、约束

必须声明

• NOT NULL：业务必填字段
• DEFAULT：有默认值的字段（避免 NULL 带来的判断负担）
• UNIQUE：业务唯一键
• PRIMARY KEY：每表必须有主键

外键策略

规则：无论用哪种，外键列必须建索引。

CHECK 约束

-- 限制枚举范围
status TINYINT NOT NULL CHECK (status IN (0,1,2,3,4))
-- 限制数值范围
age INT CHECK (age > 0 AND age <= 150)

六、表设计

范式原则

• 默认遵循 3NF：消除冗余、依赖传递
• 有意识地反范式：高频查询 JOIN 过多时，冗余一两个字段
• 反范式必须有注释说明原因

纵向拆分

大表 → 热门字段（高频查询）+ 冷门字段（低频率）
                ↓ 1:1 JOIN               ↓ 独立表

横向拆分 / 分区

• 日志类、时序数据：按时间分区
• 多租户：按 tenant_id 分区
• 超大数据量：分表（按时间或 ID hash）

七、关系映射

关联表规则

• 关联表必须包含两方 FK + 关系属性（如有）
• 主键：两 FK 的联合主键，或独立 id
• 必须带 created_at

CREATE TABLE role_user (
    id BIGINT NOT NULL AUTO_INCREMENT,
    role_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_role_user (role_id, user_id),
    KEY idx_role_user_user_id (user_id)
);

八、软删除

方案选型

• deleted_at 可恢复，信息更多
• is_deleted + 唯一约束需包含 is_deleted：UNIQUE (email, is_deleted)

ORM 自动过滤

• 多数 ORM 支持逻辑删除自动过滤（如 MyBatis-Plus @TableLogic、GORM gorm.DeletedAt）
• 手写 SQL 必须手动加 WHERE is_deleted = 0 条件
• 查询视图用 CREATE VIEW active_users AS SELECT ... WHERE is_deleted = 0

九、迁移管理

文件命名

V1.0.0__init_schema.sql
V1.0.1__add_user_phone.sql
V1.0.2__create_report_tables.sql

铁律

• 所有 DDL 进版本控制
• 迁移前向兼容：新加字段给 DEFAULT，不删旧字段
• 破坏性变更多步走：add column → 代码适配 → drop column（分版本）
• 生产禁删表、删列、改名（除非确认无依赖）

十、常见设计模式

配置驱动表

-- 主业务表不存配置细节，通过 type_code 关联
CREATE TABLE config_type (
    id BIGINT PRIMARY KEY,
    type_code VARCHAR(32) NOT NULL,
    name VARCHAR(100),
    enabled TINYINT DEFAULT 1,
    UNIQUE KEY uk_type_code (type_code)
);

多对多 + 额外属性

CREATE TABLE entity_relation (
    id BIGINT PRIMARY KEY,
    entity_a_id BIGINT NOT NULL,
    entity_b_id BIGINT NOT NULL,
    sort_order INT DEFAULT 1,    -- 排序
    is_active TINYINT DEFAULT 1, -- 状态
    UNIQUE KEY uk_entity_relation (entity_a_id, entity_b_id)
);

审计日志表

CREATE TABLE audit_log (
    id BIGINT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    record_id BIGINT NOT NULL,
    action VARCHAR(16) NOT NULL,  -- INSERT / UPDATE / DELETE
    changed_by BIGINT,
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    old_value JSON,
    new_value JSON,
    KEY idx_audit_log_table_record (table_name, record_id)
);

严禁清单

• 没主键 | SELECT * | ENUM 类型
• VARCHAR 不给长度 | 外键无索引
• 金额用 FLOAT / DOUBLE | 低基数列建单独索引
• 生产中直接删表/删列/改名
• NULL 满天飞导致 WHERE 条件遗漏 IS NULL
• 一张表超过 50 列不拆分 | 组合索引超过 5 列

这是一份质量较高的数据库设计规范，内容非常全面，涵盖了命名、数据类型、索引、约束等核心知识点，用表格和示例讲解得很清楚，对开发工作很有帮助。美中不足的是缺少实际的代码示例和演示文件，主要以文字说明为主，实用性可以进一步提升。总体来说，这是一个值得参考的好规范。