資訊發布系統中，不同分類 (新聞、產品、活動、下載) 所需欄位差異大：共同欄位 (標題、發布時間、內文) 之外，產品需規格/價格/圖片，活動需地點/報名截止/人數，下載需檔案/版本/相容性。如何設計資料庫結構兼顧彈性、查詢效能、維護成本？
| 方案 | 核心思想 | 優點 | 缺點 | 適用場景 |
|---|---|---|---|---|
| 單大表 (Wide Table) | 所有可能欄位建在一張表 | 查詢簡單 (單表)、無 Join、開發直觀 | 大量 NULL、DDL 變更鎖表、稀疏欄位浪費空間、超過欄位數限制 | 分類少 (<5)、欄位差異小、原型快速開發 |
| 核心表 + 延伸表 (Class Table Inheritance) | 共同欄位放 content，各分類建 content_product, content_event 等 1:1 延伸表 |
結構正規化、型別嚴格、查詢可 Join、擴充不影響核心 | 查詢需 Join/Union、新增分類需建表+修改代碼、異構查詢複雜 | 分類穩定、欄位型別固定、需嚴格資料完整性 |
| EAV (Entity-Attribute-Value) | attributes 定義欄位中繼，values 儲存實際值 (長表) |
極度彈性、無需 DDL、動態新增欄位 | 查詢極慢 (多次 Self-Join)、型別驗證困難、索引難建、SQL 複雜 | 欄位極度動態、使用者自訂欄位、表單產生器 |
| JSON/JSONB 欄位 (PostgreSQL/MySQL 5.7+) | 核心表含 extra_data JSON，分類差異欄位存入 JSON |
結構與彈性平衡、單表查詢、支援索引 (GIN/虛擬欄位)、無 DDL | 型別約束弱、跨資料庫遷移麻煩、舊版 MySQL 不支援 | 現代首選：分類多、欄位半結構化、需快速迭代 |
| 文件導向 (MongoDB/Elasticsearch) | 每筆文件結構獨立，Schema-less | 天然適配異構資料、水平擴展、全文搜尋強 | 交易支援弱、運維成本高、團隊技術棧擴大 | 微服務架構、大量非結構化內容、需強大搜尋 |
CREATE TABLE `contents` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`type` VARCHAR(30) NOT NULL COMMENT 'news/product/event/download',
`title` VARCHAR(255) NOT NULL,
`body` LONGTEXT NOT NULL,
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1=發布 0=草稿 -1=刪除',
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`published_at` DATETIME NULL,
`extra` JSON NOT NULL COMMENT '分類專屬欄位',
PRIMARY KEY (`id`),
INDEX `idx_type_status` (`type`, `status`),
INDEX `idx_published` (`published_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;-- 產品
{
"price": 1299,
"currency": "TWD",
"specs": {"CPU": "i7", "RAM": "16GB", "SSD": "512GB"},
"images": ["prod1.jpg", "prod2.jpg"],
"sku": "NB-2024-001"
}
-- 活動
{
"venue": "台北國際會議中心",
"start_at": "2024-10-01 09:00:00",
"end_at": "2024-10-01 17:00:00",
"capacity": 200,
"register_deadline": "2024-09-25 23:59:59",
"speakers": [{"name": "張三", "topic": "AI 趨勢"}]
}
-- 下載
{
"file_url": "/files/driver_v2.1.zip",
"file_size": 10245678,
"version": "2.1.0",
"platforms": ["Windows", "macOS", "Linux"],
"checksum": "sha256:abc123..."
}-- 查詢價格 > 1000 的產品
SELECT id, title, extra->>'$.price' AS price
FROM contents
WHERE type = 'product'
AND JSON_EXTRACT(extra, '$.price') > 1000
AND status = 1;
-- 查詢下月舉行的活動
SELECT id, title, extra->>'$.venue' AS venue, extra->>'$.start_at' AS start_at
FROM contents
WHERE type = 'event'
AND JSON_EXTRACT(extra, '$.start_at') BETWEEN '2024-11-01' AND '2024-11-30';
-- 建立虛擬欄位索引 (MySQL 8.0)
ALTER TABLE contents
ADD COLUMN price_virtual DECIMAL(10,2)
GENERATED ALWAYS AS (JSON_EXTRACT(extra, '$.price')) VIRTUAL,
ADD INDEX idx_price (price_virtual);<?php
class ContentRepository {
private $pdo;
public function __construct(PDO $pdo) { $this->pdo = $pdo; }
public function create(array $data): int {
$sql = "INSERT INTO contents (type, title, body, status, published_at, extra)
VALUES (?, ?, ?, ?, ?, ?)";
$stmt = $this->pdo->prepare($sql);
$stmt->execute([
$data['type'],
$data['title'],
$data['body'],
$data['status'] ?? 1,
$data['published_at'] ?? null,
json_encode($data['extra'] ?? [], JSON_UNESCAPED_UNICODE)
]);
return (int)$this->pdo->lastInsertId();
}
public function findByTypeAndFilter(string $type, array $filters = []): array {
$sql = "SELECT * FROM contents WHERE type = ? AND status = 1";
$params = [$type];
foreach ($filters as $path => $value) {
$sql .= " AND JSON_EXTRACT(extra, ?) = ?";
$params[] = $path; // 如 '$.price'
$params[] = $value;
}
$stmt = $this->pdo->prepare($sql);
$stmt->execute($params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
?>extra JSON 欄位至現有表extraextra資訊發布系統的彈性資料模型，核心表 + JSON 擴充欄位 是現代關聯式資料庫 (MySQL 5.7+, PostgreSQL) 的最佳平衡點：保留結構化查詢優勢，同時獲得 Schema-less 彈性。避免單大表的 NULL 洪水、EAV 的效能地獄、多表繼承的 Join 地獄。若專案已採用微服務/文件資料庫，直接使用 MongoDB 更自然。
