AI Skills
database-schema-designer

database-schema-designer

熱門

設計穩健、可擴展的 SQL 與 NoSQL 資料庫綱要。提供正規化指南、索引策略、遷移模式、約束設計與效能最佳化。確保資料完整性、查詢效能與可維護的資料模型。

2208星標
212分支
更新於 2026/3/5
獲取技能源代碼

相關技能

deprecation-and-migration7.8萬prisma-expert4.4萬create-spring-boot-kotlin-project3.7萬
SKILL.md
readonlyread-only
name
database-schema-designer
description

Design robust, scalable database schemas for SQL and NoSQL databases. Provides normalization guidelines, indexing strategies, migration patterns, constraint design, and performance optimization. Ensures data integrity, query performance, and maintainable data models.

Database Schema Designer

設計可直接用於生產環境的資料庫綱要，內建最佳實務。

快速開始

只要描述你的資料模型：

design a schema for an e-commerce platform with users, products, orders

你會得到完整的 SQL 綱要，例如：

CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  total DECIMAL(10,2) NOT NULL,
  INDEX idx_orders_user (user_id)
);

請在請求中包含：

  • 實體（使用者、產品、訂單）
  • 關鍵關聯（使用者擁有訂單、訂單包含項目）
  • 規模提示（高流量、數百萬筆記錄）
  • 資料庫偏好（SQL/NoSQL）——未指定時預設為 SQL

觸發詞

觸發詞 範例
design schema "design a schema for user authentication"
database design "database design for multi-tenant SaaS"
create tables "create tables for a blog system"
schema for "schema for inventory management"
model data "model data for real-time analytics"
I need a database "I need a database for tracking orders"
design NoSQL "design NoSQL schema for product catalog"

關鍵術語

術語 定義
正規化 組織資料以減少重複（1NF → 2NF → 3NF）
3NF 第三正規化形式——無遞移相依性
OLTP 線上交易處理——寫入密集，需正規化
OLAP 線上分析處理——讀取密集，適合反正規化
外鍵 (FK) 參照另一資料表主鍵的欄位
索引 加速查詢的資料結構（代價是寫入變慢）
存取模式 應用程式讀寫資料的方式（查詢、JOIN、篩選）
反正規化 刻意重複資料以加速讀取

快速參考

任務 方法 關鍵考量
新綱要 先正規化到 3NF 領域建模優先於 UI
SQL vs NoSQL 由存取模式決定 讀寫比例很重要
主鍵 INT 或 UUID 分散式系統用 UUID
外鍵 務必加上約束 ON DELETE 策略至關重要
索引 FK + WHERE 欄位 欄位順序很重要
遷移 務必可逆 先確保向後相容

流程概述

你的資料需求
    |
    v
+-----------------------------------------------------+
| 階段 1：分析                                        |
| * 識別實體與關聯                                    |
| * 決定存取模式（讀取密集 vs 寫入密集）              |
| * 根據需求選擇 SQL 或 NoSQL                         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 階段 2：設計                                        |
| * 正規化到 3NF（SQL）或嵌入/參照（NoSQL）           |
| * 定義主鍵與外鍵                                    |
| * 選擇適當的資料型別                                |
| * 加入約束（UNIQUE、CHECK、NOT NULL）               |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 階段 3：最佳化                                      |
| * 規劃索引策略                                      |
| * 考慮為讀取密集查詢進行反正規化                    |
| * 加入時間戳（created_at, updated_at）              |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| 階段 4：遷移                                        |
| * 產生遷移腳本（up + down）                         |
| * 確保向後相容                                      |
| * 規劃零停機部署                                    |
+-----------------------------------------------------+
    |
    v
生產就緒綱要

指令

指令 使用時機 動作
design schema for {domain} 從頭開始 產生完整綱要
normalize {table} 修正現有資料表 套用正規化規則
add indexes for {table} 效能問題 產生索引策略
migration for {change} 綱要演進 建立可逆遷移
review schema 程式碼審查 稽核現有綱要

工作流程： 從 design schema 開始 → 用 normalize 迭代 → 用 add indexes 最佳化 → 用 migration 演進

核心原則

原則 為什麼 實作方式
以領域為模型 UI 會變，領域不會 實體名稱反映商業概念
資料完整性優先 資料損毀修復成本高 在資料庫層級設定約束
針對存取模式最佳化 無法同時最佳化兩者 OLTP：正規化，OLAP：反正規化
預先規劃擴展 事後補救很痛苦 索引策略 + 分割計畫

反模式

避免 原因 替代方案
到處用 VARCHAR(255) 浪費儲存空間，隱藏意圖 依欄位適當設定大小
用 FLOAT 存金額 四捨五入誤差 DECIMAL(10,2)
缺少 FK 約束 孤立資料 務必定義外鍵
FK 上沒有索引 JOIN 變慢 為每個外鍵建立索引
用字串存日期 無法比較/排序 DATE、TIMESTAMP 型別
查詢中用 SELECT * 擷取不必要的資料 明確列出欄位
不可逆的遷移 無法回滾 務必撰寫 DOWN 遷移
新增 NOT NULL 卻無預設值 破壞現有資料列 先設為可空，補資料，再加約束

驗證檢查清單

設計完綱要後：

  • [ ] 每個資料表都有主鍵
  • [ ] 所有關聯都有外鍵約束
  • [ ] 每個 FK 都定義了 ON DELETE 策略
  • [ ] 所有外鍵上都有索引
  • [ ] 經常查詢的欄位上有索引
  • [ ] 使用適當的資料型別（金額用 DECIMAL 等）
  • [ ] 必要欄位設為 NOT NULL
  • [ ] 需要時加上 UNIQUE 約束
  • [ ] 用 CHECK 約束進行驗證
  • [ ] 有 created_at 和 updated_at 時間戳
  • [ ] 遷移腳本可逆
  • [ ] 在暫存環境中用生產資料測試過

<details>
<summary><strong>深入探討：正規化（SQL）</strong></summary>

正規化形式

形式 規則 違反範例
1NF 原子值，無重複群組 product_ids = '1,2,3'
2NF 1NF + 無部分相依 order_items 中的 customer_name
3NF 2NF + 無遞移相依 從 postal_code 推導出 country

第一正規化形式 (1NF)

-- 錯誤：欄位中含多個值
CREATE TABLE orders (
  id INT PRIMARY KEY,
  product_ids VARCHAR(255)  -- '101,102,103'
);

-- 正確：用獨立資料表存放項目
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_id INT
);

第二正規化形式 (2NF)

-- 錯誤：customer_name 只相依於 customer_id
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  customer_name VARCHAR(100),  -- 部分相依！
  PRIMARY KEY (order_id, product_id)
);

-- 正確：客戶資料放在獨立資料表
CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100)
);

第三正規化形式 (3NF)

-- 錯誤：country 相依於 postal_code
CREATE TABLE customers (
  id INT PRIMARY KEY,
  postal_code VARCHAR(10),
  country VARCHAR(50)  -- 遞移相依！
);

-- 正確：建立獨立的 postal_codes 資料表
CREATE TABLE postal_codes (
  code VARCHAR(10) PRIMARY KEY,
  country VARCHAR(50)
);

何時該反正規化

情境 反正規化策略
讀取密集的報表 預先計算的彙總值
昂貴的 JOIN 快取的衍生欄位
分析儀表板 具體化檢視表 
-- 為效能而反正規化
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10,2),  -- 計算而得
  item_count INT               -- 計算而得
);

</details>

<details>
<summary><strong>深入探討：資料型別</strong></summary>

字串型別

型別 使用時機 範例
CHAR(n) 固定長度 州代碼、ISO 日期
VARCHAR(n) 可變長度 姓名、電子郵件
TEXT 長內容 文章、描述 
-- 適當的大小
email VARCHAR(255)
phone VARCHAR(20)
country_code CHAR(2)

數值型別

型別 範圍 使用時機
TINYINT -128 到 127 年齡、狀態碼
SMALLINT -32K 到 32K 數量
INT -2.1B 到 2.1B ID、計數
BIGINT 非常大 大型 ID、時間戳
DECIMAL(p,s) 精確精度 金額
FLOAT/DOUBLE 近似值 科學資料 
-- 金額務必用 DECIMAL
price DECIMAL(10, 2)  -- $99,999,999.99

-- 金額絕不能用 FLOAT
price FLOAT  -- 四捨五入誤差！

日期/時間型別

DATE        -- 2025-10-31
TIME        -- 14:30:00
DATETIME    -- 2025-10-31 14:30:00
TIMESTAMP   -- 自動時區轉換

-- 一律以 UTC 儲存
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

布林值

-- PostgreSQL
is_active BOOLEAN DEFAULT TRUE

-- MySQL
is_active TINYINT(1) DEFAULT 1

</details>

<details>
<summary><strong>深入探討：索引策略</strong></summary>

何時建立索引

一律建立索引 原因
外鍵 加速 JOIN
WHERE 子句中的欄位 加速篩選
ORDER BY 中的欄位 加速排序
唯一約束 強制唯一性 
-- 外鍵索引
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- 查詢模式索引
CREATE INDEX idx_orders_status_date ON orders(status, created_at);

索引型別

型別 最佳用途 範例
B-Tree 範圍、相等 price > 100
Hash 僅精確比對 email = 'x@y.com'
全文 文字搜尋 MATCH AGAINST
部分 資料子集 WHERE is_active = true

複合索引順序

CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- 使用索引（customer_id 在前）
SELECT * FROM orders WHERE customer_id = 123;
SELECT * FROM orders WHERE customer_id = 123 AND status = 'pending';

-- 不使用索引（只有 status）
SELECT * FROM orders WHERE status = 'pending';

規則： 選擇性最高的欄位在前，或最常單獨查詢的欄位在前。

索引陷阱

陷阱 問題 解決方案
過度索引 寫入變慢 只為查詢的欄位建立索引
欄位順序錯誤 索引未使用 符合查詢模式
缺少 FK 索引 JOIN 變慢 一律為 FK 建立索引

</details>

<details>
<summary><strong>深入探討：約束</strong></summary>

主鍵

-- 自動遞增（簡單）
id INT AUTO_INCREMENT PRIMARY KEY

-- UUID（分散式系統）
id CHAR(36) PRIMARY KEY DEFAULT (UUID())

-- 複合（關聯表）
PRIMARY KEY (student_id, course_id)

外鍵

FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE     -- 刪除父項時一併刪除子項
  ON DELETE RESTRICT    -- 若有參照則禁止刪除
  ON DELETE SET NULL    -- 父項刪除時設為 NULL
  ON UPDATE CASCADE     -- 父項更新時同步更新子項
策略 使用時機
CASCADE 相依資料（order_items）
RESTRICT 重要參照（防止意外）
SET NULL 可選關聯

其他約束

-- 唯一
email VARCHAR(255) UNIQUE NOT NULL

-- 複合唯一
UNIQUE (student_id, course_id)

-- 檢查
price DECIMAL(10,2) CHECK (price >= 0)
discount INT CHECK (discount BETWEEN 0 AND 100)

-- 非空
name VARCHAR(100) NOT NULL

</details>

<details>
<summary><strong>深入探討：關聯模式</strong></summary>

一對多

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id)
);

CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INT NOT NULL,
  quantity INT NOT NULL
);

多對多

-- 關聯表
CREATE TABLE enrollments (
  student_id INT REFERENCES students(id) ON DELETE CASCADE,
  course_id INT REFERENCES courses(id) ON DELETE CASCADE,
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id)
);

自我參照

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  manager_id INT REFERENCES employees(id)
);

多型

-- 方法 1：獨立 FK（完整性較強）
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  post_id INT REFERENCES posts(id),
  photo_id INT REFERENCES photos(id),
  CHECK (
    (post_id IS NOT NULL AND photo_id IS NULL) OR
    (post_id IS NULL AND photo_id IS NOT NULL)
  )
);

-- 方法 2：型別 + ID（彈性大，完整性較弱）
CREATE TABLE comments (
  id INT PRIMARY KEY,
  content TEXT NOT NULL,
  commentable_type VARCHAR(50) NOT NULL,
  commentable_id INT NOT NULL
);

</details>

<details>
<summary><strong>深入探討：NoSQL 設計（MongoDB）</strong></summary>

嵌入 vs 參照

因素 嵌入 參照
存取模式 一起讀取 分開讀取
關聯 1:少數 1:多數
文件大小 小 接近 16MB
更新頻率 少 頻繁

嵌入文件

{
  "_id": "order_123",
  "customer": {
    "id": "cust_456",
    "name": "Jane Smith",
    "email": "jane@example.com"
  },
  "items": [
    { "product_id": "prod_789", "quantity": 2, "price": 29.99 }
  ],
  "total": 109.97
}

參照文件

{
  "_id": "order_123",
  "customer_id": "cust_456",
  "item_ids": ["item_1", "item_2"],
  "total": 109.97
}

MongoDB 索引

// 單一欄位
db.users.createIndex({ email: 1 }, { unique: true });

// 複合
db.orders.createIndex({ customer_id: 1, created_at: -1 });

// 全文搜尋
db.articles.createIndex({ title: "text", content: "text" });

// 地理空間
db.stores.createIndex({ location: "2dsphere" });

</details>

<details>
<summary><strong>深入探討：遷移</strong></summary>

遷移最佳實務

實務 原因
一律可逆 需要回滾
向後相容 零停機部署
綱要先於資料 分離關注點
在暫存環境測試 及早發現問題

新增欄位（零停機）

-- 步驟 1：新增可空欄位
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 步驟 2：部署會寫入新欄位的程式碼

-- 步驟 3：補填現有資料列
UPDATE users SET phone = '' WHERE phone IS NULL;

-- 步驟 4：設為必要（如果需要）
ALTER TABLE users MODIFY phone VARCHAR(20) NOT NULL;

重新命名欄位（零停機）

-- 步驟 1：新增新欄位
ALTER TABLE users ADD COLUMN email_address VARCHAR(255);

-- 步驟 2：複製資料
UPDATE users SET email_address = email;

-- 步驟 3：部署從新欄位讀取的程式碼
-- 步驟 4：部署寫入新欄位的程式碼

-- 步驟 5：刪除舊欄位
ALTER TABLE users DROP COLUMN email;

遷移範本

-- Migration: YYYYMMDDHHMMSS_description.sql

-- UP
BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone);
COMMIT;

-- DOWN
BEGIN;
DROP INDEX idx_users_phone ON users;
ALTER TABLE users DROP COLUMN phone;
COMMIT;

</details>

<details>
<summary><strong>深入探討：效能最佳化</strong></summary>

查詢分析

EXPLAIN SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending';
查看項目 意義
type: ALL 全表掃描（不好）
type: ref 使用索引（好）
key: NULL 未使用索引
rows: high 掃描許多資料列

N+1 查詢問題

# 錯誤：N+1 次查詢
orders = db.query("SELECT * FROM orders")
for order in orders:
    customer = db.query(f"SELECT * FROM customers WHERE id = {order.customer_id}")

# 正確：單次 JOIN
results = db.query("""
    SELECT orders.*, customers.name
    FROM orders
    JOIN customers ON orders.customer_id = customers.id
""")

最佳化技巧

技巧 使用時機
新增索引 WHERE/ORDER BY 慢
反正規化 JOIN 昂貴
分頁 大量結果集
快取 重複查詢
讀取副本 讀取密集負載
分割 非常大的資料表

</details>

擴充點

  1. 資料庫特定模式： 加入 MySQL vs PostgreSQL vs SQLite 的差異
  2. 進階模式： 時間序列、事件溯源、CQRS、多租戶
  3. ORM 整合： TypeORM、Prisma、SQLAlchemy 模式
  4. 監控： 查詢效能追蹤、慢查詢警示