database-schema-designer

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 時間戳
[ ] 遷移腳本可逆
[ ] 在暫存環境中用生產資料測試過

正規化形式

形式	規則	違反範例
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>

字串型別

型別	使用時機	範例
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>

何時建立索引

一律建立索引	原因
外鍵	加速 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>

主鍵

-- 自動遞增（簡單）
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>

一對多

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>深入探討：NoSQL 設計（MongoDB）</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>

遷移最佳實務

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

新增欄位（零停機）

-- 步驟 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>深入探討：效能最佳化</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>

擴充點

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

您可能也喜歡

uncloud

240Kdatabase

Use when managing an Uncloud cluster — deploying services, configuring Caddy ingress, adding static proxy routes for non-cluster devices, publishing ports, scaling, inspecting logs, or managing machines and volumes with the `uc` CLI.

affaan-m

獲取

prisma-patterns

238Kdatabase

適用於 TypeScript 後端的 Prisma ORM 設計模式 — 涵蓋 Schema 設計、查詢最佳化、事務處理（Transaction）、分頁機制，以及關鍵陷阱解析（如 updateMany 僅返回計數而非記錄筆數、$transaction 超時、migrate dev 重置資料庫、批次寫入時跳過 @updatedAt，以及 Serverless 環境下的連線耗盡問題）。

affaan-m

獲取

database-architect

47Kdatabase

Expert database architect specializing in data layer design from scratch, technology selection, schema modeling, and scalable database architectures.

sickn33

獲取

繼續探索

瀏覽 database 熱門 Skills 最近更新

database-schema-designer

相關技能

Database Schema Designer

快速開始

觸發詞

關鍵術語

快速參考

流程概述

指令

核心原則

反模式

驗證檢查清單

正規化形式

第一正規化形式 (1NF)

第二正規化形式 (2NF)

第三正規化形式 (3NF)

何時該反正規化

字串型別

數值型別

日期/時間型別

布林值

何時建立索引

索引型別

複合索引順序

索引陷阱

主鍵

外鍵

其他約束

一對多

多對多

自我參照

多型

嵌入 vs 參照

嵌入文件

參照文件

MongoDB 索引

遷移最佳實務

新增欄位（零停機）

重新命名欄位（零停機）

遷移範本

查詢分析

N+1 查詢問題

最佳化技巧

擴充點

您可能也喜歡

uncloud

prisma-patterns

database-architect

繼續探索