为什么要学习数据建模模式？
在数据库设计中，某些结构会反复出现：层次结构（菜单、分类、组织架构）、标签/标签、多语言支持、历史记录管理等。了解这些模式的最佳实践方案，可以减少试错，设计出更高效的Schema。
模式1：层次结构（树形结构）
分类、菜单、组织架构、评论线程等具有父子关系的树形结构。
方法1：邻接表（Adjacency List）— 最简单
CREATE TABLE categories (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INT REFERENCES categories(id) -- 自引用
);
- 优点：结构简单，直接父/子查询快速
- 缺点：获取完整树需要递归查询
-- PostgreSQL递归CTE获取完整树
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.depth + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY depth, name;
方法2：闭包表（Closure Table）— 最灵活
CREATE TABLE category_closure (
ancestor_id INT REFERENCES categories(id),
descendant_id INT REFERENCES categories(id),
depth INT,
PRIMARY KEY (ancestor_id, descendant_id)
);
将所有祖先-后代关系存储在独立表中，读写均高效。
模式2：标签（Tag）系统
在文章、商品等上添加多个标签，典型的N:M关系。
基本结构
CREATE TABLE tags (
id INT PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE post_tags (
post_id INT REFERENCES posts(id) ON DELETE CASCADE,
tag_id INT REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
标签检索
-- 包含所有指定标签的文章（AND条件）
SELECT p.title
FROM posts p
JOIN post_tags pt ON p.id = pt.post_id
JOIN tags t ON pt.tag_id = t.id
WHERE t.name IN ('database', 'sql')
GROUP BY p.id, p.title
HAVING COUNT(DISTINCT t.name) = 2;
PostgreSQL数组简化方案
CREATE TABLE posts (
id INT PRIMARY KEY,
title VARCHAR(255),
tags TEXT[]
);
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
SELECT title FROM posts WHERE tags @> ARRAY['database', 'sql'];
模式3：多语言（i18n）支持
方法1：翻译表
CREATE TABLE products (
id UUID PRIMARY KEY,
price DECIMAL(10,2)
);
CREATE TABLE product_translations (
product_id UUID REFERENCES products(id) ON DELETE CASCADE,
language CHAR(2) NOT NULL,
name VARCHAR(255) NOT NULL,
description TEXT,
PRIMARY KEY (product_id, language)
);
-- 获取中文，无则回退到英文
SELECT p.id, COALESCE(t_zh.name, t_en.name) AS name
FROM products p
LEFT JOIN product_translations t_zh ON p.id = t_zh.product_id AND t_zh.language = 'zh'
LEFT JOIN product_translations t_en ON p.id = t_en.product_id AND t_en.language = 'en';
方法2：JSONB列（PostgreSQL）
CREATE TABLE products (
id UUID PRIMARY KEY,
price DECIMAL(10,2),
name JSONB -- {"zh": "智能手机", "en": "Smartphone", "ko": "스마트폰"}
);
CREATE INDEX idx_products_name ON products USING GIN(name);
SELECT name->>'zh' AS name_zh FROM products;
模式4：软删除（Soft Delete）
不物理删除，而是用deleted_at时间戳标记已删除。
CREATE TABLE users (
id UUID PRIMARY KEY,
email VARCHAR(255),
deleted_at TIMESTAMP -- NULL=活跃，有值=已删除
);
SELECT * FROM users WHERE deleted_at IS NULL;
-- 允许删除后重新使用邮箱的部分唯一索引
CREATE UNIQUE INDEX idx_users_email_active
ON users(email) WHERE deleted_at IS NULL;
使用Erdmates可视化模式
Erdmates的ERD编辑器帮助您可视化设计这些复杂的数据建模模式——闭包表的自引用关系、翻译表的复合键结构等，并将其以直观的图表形式与团队共享。
总结
选择正确的数据建模模式直接影响应用程序的性能和可维护性。由于每种情况的权衡取舍不同，请综合考虑数据的读写比例、查询模式和数据量，选择最优方案。