name: 資料庫模式設計 slug: "database-schema-design" version: "1.0.0" displayName: "資料庫模式設計" summary: "為任何應用領域設計規範化的資料庫模式，包括表、關係、索引和約束。" description: 為任何應用領域設計規範化的資料庫模式，包括表、關係、索引和約束。 license: MIT metadata: author: AI Agent Skills Community version: 1.0.0

Database Schema Design

此技能使AI代理能夠根據應用需求設計穩健、規範化的關聯資料庫模式。該代理分析實體，定義具有適當資料型別和約束的表，建立關係（一對一、一對多、多對多），應用規範化至3NF，為查詢效能建立索引，並生成完整的SQL DDL指令碼以供執行。

Workflow

1. 收集並分析需求： 與使用者訪談或解析規範文件，識別所有實體、其屬性以及它們之間的關係。明確基數（1:1, 1:N, M:N）、必需欄位與可選欄位，以及任何特定領域的約束，如唯一郵箱、正數價格或列舉狀態。在繼續之前明確記錄假設。

2. 建模實體和關係： 將需求轉化為邏輯資料模型。將每個實體定義為表，選擇適當的主鍵（優先使用代理整數或UUID鍵以確保穩定性），並對映關係。對於一對多，在“多”端新增外部索引鍵；對於多對多，建立一個連線表，其複合主鍵引用兩個父表；對於一對一，使用共享主鍵或唯一外部索引鍵。

3. 應用規範化： 按照範式審查模式。確保每個非鍵列依賴於整個主鍵（2NF）且僅依賴於主鍵（3NF）。拆分包含傳遞依賴的表。僅在讀操作密集的查詢模式下合理地反規範化，並記錄權衡。

4. 定義約束和索引： 新增NOT NULL、UNIQUE、CHECK和DEFAULT約束以在資料庫級別強制資料完整性。在外部索引鍵列、WHERE子句中使用的列以及用於排序或分組的列上建立索引。考慮為多列查詢模式建立複合索引。

5. 生成SQL DDL指令碼： 生成完整的CREATE TABLE語句，包含所有列、型別、約束和索引。使用IF NOT EXISTS確保冪等性。按順序排列語句，使被引用的表在引用表之前建立。

6. 驗證並迭代： 根據原始需求審查模式。確認所有實體都被表示，所有關係都正確建模，且沒有資料完整性漏洞。根據反饋進行調整。

支援的技術

• 關聯式資料庫： PostgreSQL, MySQL, MariaDB, SQLite, SQL Server, Oracle
• 模式工具： dbdiagram.io, pgModeler, MySQL Workbench, DBeaver

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

• 遷移框架： Flyway, Liquibase, Alembic, Prisma Migrate, Knex

Usage

提供您的應用領域及其資料需求的描述。包括主要實體、其屬性以及它們之間的關係。代理將生成一個規範化的模式和完整的DDL。您可以請求特定資料庫（例如，PostgreSQL vs. MySQL語法）或要求對模式進行修改，如新增審計列或軟刪除。

Examples

示例 1：電子商務應用模式

請求： 為具有使用者、產品、訂單和訂單項的電子商務應用設計一個模式。

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    sku VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status VARCHAR(20) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0),
    shipping_address TEXT NOT NULL,
    ordered_at TIMESTAMP NOT NULL DEFAULT NOW()
);

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) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
    UNIQUE (order_id, product_id)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

示例 2：通過模式遷移新增評論功能

請求： 在現有的電子商務模式中新增一個產品評論表。每個使用者對每件產品只能留下一條評論，包含評分和可選評論內容。

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    UNIQUE (user_id, product_id)
);

CREATE INDEX idx_reviews_product_id ON reviews(product_id);
CREATE INDEX idx_reviews_user_id ON reviews(user_id);
CREATE INDEX idx_reviews_rating ON reviews(rating);

此操作通過UNIQUE約束強制每個使用者對每件產品僅能有一條評論，限制評分為1-5，並級聯刪除以確保刪除使用者或產品時也會刪除其評論。

最佳實踐

• 始終顯式定義外部索引鍵 以在資料庫級別強制參照完整性，而不是僅依賴應用程式程式碼。
• 使用CHECK約束來實現領域規則 如正數價格、有效狀態列舉和評分範圍，防止無效資料進入資料庫。
• 索引所有外部索引鍵列 因為它們用於JOIN和查詢；未索引的外部索引鍵在級聯操作期間會導致全表掃描。
• 優先使用代理鍵而非自然鍵 作為主鍵以避免當自然值更改時（如郵箱地址或SKU）出現的問題。
• 在所有表中新增created_at和updated_at時間戳 用於審計和除錯；使用資料庫預設值確保一致性。
• 明確記錄反規範化的決策 當你偏離範式以提升效能時，以便未來的開發者理解其中的權衡。

Edge Cases

• 迴圈外部索引鍵依賴： 當兩個表相互引用時，先建立一個表而不包含FK，再建立第二個表，然後ALTER TABLE新增缺失的FK。在PostgreSQL中使用延遲約束處理事務中的迴圈插入。
• 自引用關係： 對於層次結構資料（如具有子分類的類別），使用可為空的parent_id列引用同一張表。新增CHECK約束或觸發器以防止某行成為自己的父級。
• 多型關聯： 當多個表需要引用共享實體時（例如，對帖子和產品的評論），優先使用獨立的FK列並配合CHECK約束確保恰好有一個非空，而不是通用的entity_type + entity_id模式，因為後者無法強制參照完整性。
• 大文本或二進位制資料： 將BLOB和大文本儲存在單獨的表中並通過外部索引鍵連結，以保持主錶行大小較小並避免不需要這些大數據的查詢變慢。
• 多租戶模式： 決定是使用帶有tenant_id列的共享表（更簡單）還是為每個租戶使用獨立的模式（更強隔離性）。在所有索引中新增tenant_id，並通過行級安全策略強制執行。

這是一個質量中上的資料庫設計技能，包含清晰的設計流程和實用的最佳實踐。兩個程式碼示例能幫助理解模式設計方法，支援多種主流資料庫。對於需要設計規範化資料庫模式的使用者有一定參考價值，但示例數量偏少，文件結構也較為基礎，實際使用中可能需要結合具體資料庫文件補充細節。