数据库设计规范模板

阶段 4 模板：数据库 Schema 设计

文档信息

字段	值
项目名称	`{项目名称}`
版本	`1.0.0`
创建日期	`{日期}`
数据库类型	PostgreSQL / MySQL / SQL Server

1. 数据库概述

1.1 数据库策略

属性	值
类型	关系型 / 文档型 / 混合
实例	单机 / 主从 / 集群
字符集	UTF-8 (utf8mb4)
排序规则	utf8mb4_unicode_ci

1.2 模式组织

模式/数据库	用途	负责人
public	主应用数据	app_user
audit	审计日志	audit_user

2. 实体关系图

2.1 高层 ERD

erDiagram
    USER ||--o{ ORDER : "下单"
    USER ||--o{ ADDRESS : "拥有"
    ORDER ||--|{ ORDER_ITEM : "包含"
    ORDER_ITEM }o--|| PRODUCT : "引用"
    PRODUCT }o--|| CATEGORY : "属于"

    USER {
        uuid id PK
        string email UK
        string password_hash
        string name
        timestamp created_at
        timestamp updated_at
    }

    ORDER {
        uuid id PK
        uuid user_id FK
        string status
        decimal total
        timestamp created_at
    }

    ORDER_ITEM {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        integer quantity
        decimal unit_price
    }

    PRODUCT {
        uuid id PK
        string name
        string sku UK
        decimal price
        integer stock
    }

2.2 实体数量估算

实体	预估行数（初始）	增长率
users	1,000	100/月
orders	5,000	500/月
products	500	50/月

3. 表定义

3.1 用户表

CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email           VARCHAR(255) NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    name            VARCHAR(100),
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
    email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMP WITH TIME ZONE,

    CONSTRAINT uk_users_email UNIQUE (email),
    CONSTRAINT ck_users_status CHECK (status IN ('active', 'suspended', 'deleted'))
);

字段规格：

字段	类型	可空	默认值	描述
id	UUID	否	gen_random_uuid()	主键
email	VARCHAR(255)	否	-	用户邮箱（唯一）
password_hash	VARCHAR(255)	否	-	Bcrypt 哈希
name	VARCHAR(100)	是	NULL	显示名称
status	VARCHAR(20)	否	'active'	账户状态
email_verified	BOOLEAN	否	FALSE	邮箱验证标志
created_at	TIMESTAMPTZ	否	NOW()	创建时间
updated_at	TIMESTAMPTZ	否	NOW()	更新时间
deleted_at	TIMESTAMPTZ	是	NULL	软删除时间

索引：

索引名称	字段	类型	用途
uk_users_email	email	UNIQUE	邮箱查找
idx_users_status	status	BTREE	按状态筛选
idx_users_created_at	created_at	BTREE	排序

3.2 订单表

CREATE TABLE orders (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'pending',
    total_amount    DECIMAL(10, 2) NOT NULL,
    currency        VARCHAR(3) NOT NULL DEFAULT 'CNY',
    shipping_address JSONB,
    notes           TEXT,
    created_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMP WITH TIME ZONE,

    CONSTRAINT fk_orders_user FOREIGN KEY (user_id)
        REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT ck_orders_status CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    CONSTRAINT ck_orders_total CHECK (total_amount >= 0)
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

每个表重复此结构

4. 索引策略

4.1 索引类型

类型	用途	示例
BTREE	等值、范围查询	默认索引类型
HASH	仅等值查询	精确匹配查找
GIN	全文、JSON	JSONB 字段、数组
GiST	几何、全文	空间数据

4.2 索引设计规则

规则	描述
选择性	索引高基数的列
覆盖索引	包含频繁访问的列
组合索引	按选择性排序（最高选择性在前）
避免过度索引	每个索引都有写入成本

4.3 查询到索引映射

查询模式	推荐索引
`WHERE email = ?`	email 上建 UNIQUE
`WHERE user_id = ? ORDER BY created_at DESC`	组合索引 (user_id, created_at DESC)
`WHERE status = ? AND created_at > ?`	组合索引 (status, created_at)
name 上全文搜索	带 tsvector 的 GIN

5. 约束

5.1 主键

表	字段	类型	生成方式
users	id	UUID	gen_random_uuid()
orders	id	UUID	gen_random_uuid()

5.2 外键

约束	源表	目标表	删除时	更新时
fk_orders_user	orders.user_id	users.id	RESTRICT	CASCADE
fk_order_items_order	order_items.order_id	orders.id	CASCADE	CASCADE
fk_order_items_product	order_items.product_id	products.id	RESTRICT	CASCADE

5.3 检查约束

表	约束	规则
users	ck_users_status	status IN ('active', 'suspended', 'deleted')
orders	ck_orders_total	total_amount >= 0
products	ck_products_price	price >= 0
products	ck_products_stock	stock >= 0

5.4 唯一约束

表	字段	用途
users	email	每邮箱一个账户
products	sku	唯一产品标识

6. 数据类型参考

6.1 标准类型

用途	PostgreSQL	MySQL	SQL Server	备注
主键	UUID	CHAR(36)	UNIQUEIDENTIFIER	分布式系统首选 UUID
短字符串	VARCHAR(50)	VARCHAR(50)	NVARCHAR(50)	名称、代码
中字符串	VARCHAR(255)	VARCHAR(255)	NVARCHAR(255)	邮箱、URL
长字符串	TEXT	TEXT	NVARCHAR(MAX)	描述、内容
整数	INTEGER	INT	INT	计数、ID
金额	DECIMAL(10,2)	DECIMAL(10,2)	DECIMAL(10,2)	财务金额
布尔	BOOLEAN	TINYINT(1)	BIT	标志位
时间戳	TIMESTAMPTZ	DATETIME	DATETIMEOFFSET	始终使用时区
JSON	JSONB	JSON	NVARCHAR(MAX)	灵活模式数据

6.2 类型选择指南

数据	推荐类型	理由
邮箱	VARCHAR(255)	标准最大长度
手机号	VARCHAR(20)	国际格式
金额	DECIMAL(m,n)	需要精度
状态	VARCHAR(20) + CHECK	明确的值
时间戳	TIMESTAMPTZ	时区感知

7. 迁移策略

7.1 迁移命名

{时间戳}_{操作}_{描述}.sql

示例：
20240115120000_create_users_table.sql
20240115130000_add_email_verified_to_users.sql
20240115140000_create_orders_index.sql

7.2 迁移模板

-- 迁移：创建用户表
-- 创建日期：2024-01-15

-- Up
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);

-- Down
DROP TABLE IF EXISTS users;

7.3 迁移最佳实践

实践	描述
向后兼容	新列可空或有默认值
不加锁	并发创建索引 (CONCURRENTLY)
批量更新	分批处理大批量更新
先测试	在生产数据副本的测试环境运行

8. 查询模式

8.1 常用查询

获取用户及其订单

SELECT
    u.id,
    u.email,
    u.name,
    json_agg(
        json_build_object(
            'id', o.id,
            'status', o.status,
            'total', o.total_amount
        )
    ) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id = $1
GROUP BY u.id;

带筛选的分页列表

SELECT *
FROM orders
WHERE status = $1
  AND created_at >= $2
ORDER BY created_at DESC
LIMIT $3 OFFSET $4;

8.2 查询优化指南

指南	描述
使用 EXPLAIN ANALYZE	验证查询计划
避免 SELECT *	只选择需要的列
使用预编译语句	参数化查询
批量处理大操作	分块处理

9. 数据完整性

9.1 事务隔离级别

级别	用途
READ COMMITTED	默认，大多数操作
REPEATABLE READ	报表，需要一致性
SERIALIZABLE	关键财务操作

9.2 事务模板

BEGIN;

-- 检查前置条件
SELECT status FROM orders WHERE id = $1 FOR UPDATE;

-- 执行操作
UPDATE orders SET status = 'confirmed' WHERE id = $1;
INSERT INTO order_events (order_id, event, created_at)
VALUES ($1, 'confirmed', NOW());

COMMIT;

9.3 数据校验层级

层级	校验
应用层	Schema 校验、业务规则
数据库	CHECK 约束、触发器
引用	外键约束

10. 备份与恢复

10.1 备份策略

类型	频率	保留期	工具
全量	每天	30 天	pg_dump
增量	每小时	7 天	WAL 归档
时间点	持续	7 天	WAL + 基础备份

10.2 备份命令

# 全量备份
pg_dump -Fc -Z9 -f backup_$(date +%Y%m%d).dump dbname

# 仅结构
pg_dump -s -f schema.sql dbname

# 仅数据
pg_dump -a -f data.sql dbname

10.3 恢复流程

场景	恢复步骤
表删除	从备份恢复到临时库，复制表
数据损坏	时间点恢复
数据库丢失	从最新备份完整恢复

11. 性能监控

11.1 关键指标

指标	告警阈值
连接数	> max_connections 的 80%
查询时间 (P95)	> 500ms
锁等待时间	> 5s
表膨胀	> 30%
索引使用率	< 90%

11.2 监控查询

-- 慢查询
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 表膨胀
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

-- 索引使用
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

11.3 维护任务

任务	频率	命令
VACUUM ANALYZE	每天（自动）	autovacuum
REINDEX	每月	REINDEX TABLE CONCURRENTLY
统计信息更新	每天	ANALYZE

12. 安全

12.1 访问控制

角色	权限	用途
app_user	应用表的 SELECT, INSERT, UPDATE, DELETE	应用访问
readonly_user	应用表的 SELECT	报表
migration_user	应用表的 ALL, CREATE	Schema 迁移

12.2 行级安全（如适用）

-- 启用 RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 策略：用户只能看到自己的订单
CREATE POLICY orders_select_policy ON orders
    FOR SELECT
    USING (user_id = current_setting('app.current_user_id')::UUID);

12.3 数据加密

数据	加密方式
静态数据	TDE / 磁盘加密
传输数据	TLS 1.3
PII 字段	应用层加密

13. 审计日志

13.1 审计表结构

CREATE TABLE audit_logs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name      VARCHAR(100) NOT NULL,
    operation       VARCHAR(10) NOT NULL,
    record_id       UUID NOT NULL,
    old_values      JSONB,
    new_values      JSONB,
    changed_by      UUID,
    changed_at      TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    ip_address      INET
);

CREATE INDEX idx_audit_logs_table_record ON audit_logs(table_name, record_id);
CREATE INDEX idx_audit_logs_changed_at ON audit_logs(changed_at);

13.2 审计触发器

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_logs (table_name, operation, record_id, old_values, new_values, changed_by)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        COALESCE(NEW.id, OLD.id),
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN row_to_json(OLD) END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) END,
        current_setting('app.current_user_id', true)::UUID
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

校验清单

所有实体有主键
外键关系已定义
查询模式有合适的索引
检查约束强制数据有效性
迁移策略已文档化
备份流程已定义
性能监控已规划
访问控制已实现
审计日志已配置

database-spec-template.md 13 KB Permalink History Raw

数据库设计规范模板

文档信息

1. 数据库概述

1.1 数据库策略

1.2 模式组织

2. 实体关系图

2.1 高层 ERD

2.2 实体数量估算

3. 表定义

3.1 用户表

3.2 订单表

4. 索引策略

4.1 索引类型

4.2 索引设计规则

4.3 查询到索引映射

5. 约束

5.1 主键

5.2 外键

5.3 检查约束

5.4 唯一约束

6. 数据类型参考

6.1 标准类型

6.2 类型选择指南

7. 迁移策略

7.1 迁移命名

7.2 迁移模板

7.3 迁移最佳实践

8. 查询模式

8.1 常用查询

获取用户及其订单

带筛选的分页列表

8.2 查询优化指南

9. 数据完整性

9.1 事务隔离级别

9.2 事务模板

9.3 数据校验层级

10. 备份与恢复

10.1 备份策略

10.2 备份命令

10.3 恢复流程

11. 性能监控

11.1 关键指标

11.2 监控查询

11.3 维护任务

12. 安全

12.1 访问控制

12.2 行级安全（如适用）

12.3 数据加密

13. 审计日志

13.1 审计表结构

13.2 审计触发器

校验清单

database-spec-template.md 13 KB

Permalink History Raw