name: design-db description: [PRD2PLAN] 資料庫表結構設計規範。涵蓋命名約定、資料型別、索引設計、約束、關係對映、軟刪除、遷移管理。適用於所有後端技術棧的資料庫設計與評審。 allowed-tools: disable: false

資料庫設計規範

一、命名約定

表命名

• 小寫 + 下劃線：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 位小數

  小蔥技能7w4.net持續更新中。

索引命名

二、必備欄位

每張業務表建議包含

審計欄位選型

規則：一個專案內只能選一種，禁止混用。審計欄位名從專案現有約定中提取，新專案自由選擇。

三、資料型別

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 列

這是一份質量較高的資料庫設計規範，內容非常全面，涵蓋了命名、資料型別、索引、約束等核心知識點，用表格和示例講解得很清楚，對開發工作很有幫助。美中不足的是缺少實際的程式碼示例和演示檔案，主要以文字說明為主，實用性可以進一步提升。總體來說，這是一個值得參考的好規範。