DatabaseSchemaDocumentationSkill database-schema-documentation

数据库架构文档

数据库: PostgreSQL 14.x 版本: 2.0 最后更新: 2025-01-15 架构版本: 20250115120000

概览

此数据库支持电子商务应用，包括用户管理、产品目录、订单和支付处理。

实体关系图

erDiagram
    用户 ||--o{ 订单 : 下单
    用户 ||--o{ 地址 : 拥有
    用户 ||--o{ 支付方式 : 拥有
    订单 ||--|{ 订单项 : 包含
    订单 ||--|| 支付 : 拥有
    产品 ||--o{ 订单项 : 包含
    产品 }o--|| 类别 : 属于
    产品 ||--o{ 产品图片 : 拥有
    产品 ||--o{ 库存 : 跟踪

    用户 {
        uuid id PK
        字符串 email UK
        字符串 password_hash
        字符串 name
        时间戳 created_at
        时间戳 updated_at
    }

    订单 {
        uuid id PK
        uuid user_id FK
        字符串 status
        小数 total_amount
        时间戳 created_at
        时间戳 updated_at
    }

    订单项 {
        uuid id PK
        uuid order_id FK
        uuid product_id FK
        整数 quantity
        小数 price
    }

    产品 {
        uuid id PK
        字符串 name
        文本 description
        小数 price
        uuid category_id FK
        布尔 active
    }

---

表格

用户

存储用户账户信息。

列:

列	类型	空值	默认值	描述
id	uuid	否	gen_random_uuid()	主键
email	varchar(255)	否	-	用户邮箱（唯一）
password_hash	varchar(255)	否	-	bcrypt哈希密码
name	varchar(255)	否	-	用户全名
email_verified	布尔	否	假	电子邮件验证状态
two_factor_enabled	布尔	否	假	2FA启用标志
two_factor_secret	varchar(32)	是	-	TOTP密钥
created_at	时间戳	否	now()	记录创建时间
updated_at	时间戳	否	now()	最后更新时间
deleted_at	时间戳	是	-	软删除时间戳
last_login_at	时间戳	是	-	最后登录时间戳

索引:

CREATE UNIQUE INDEX idx_users_email ON 用户(email);
CREATE INDEX idx_users_created_at ON 用户(created_at);
CREATE INDEX idx_users_deleted_at ON 用户(deleted_at) WHERE deleted_at IS NULL;

约束:

ALTER TABLE 用户
  ADD CONSTRAINT users_email_format
  CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

ALTER TABLE 用户
  ADD CONSTRAINT users_name_length
  CHECK (length(name) >= 2);

触发器:

-- 更新 updated_at 时间戳
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON 用户
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

示例数据:

INSERT INTO 用户 (email, password_hash, name, email_verified)
VALUES
  ('john@example.com', '$2b$12$...', 'John Doe', 真),
  ('jane@example.com', '$2b$12$...', 'Jane Smith', 真);

---

产品

存储产品目录信息。

列:

列	类型	空值	默认值	描述
id	uuid	否	gen_random_uuid()	主键
name	varchar(255)	否	-	产品名称
slug	varchar(255)	否	-	URL友好名称（唯一）
description	文本	是	-	产品描述
price	小数(10,2)	否	-	产品价格（美元）
compare_at_price	小数(10,2)	是	-	原价（促销）
sku	varchar(100)	否	-	库存单位（唯一）
category_id	uuid	否	-	外键到类别
brand	varchar(100)	是	-	产品品牌
active	布尔	否	真	产品可见性
featured	布尔	否	假	特色产品标志
metadata	jsonb	是	-	额外产品元数据
created_at	时间戳	否	now()	记录创建时间
updated_at	时间戳	否	now()	最后更新时间

索引:

CREATE UNIQUE INDEX idx_products_slug ON 产品(slug);
CREATE UNIQUE INDEX idx_products_sku ON 产品(sku);
CREATE INDEX idx_products_category_id ON 产品(category_id);
CREATE INDEX idx_products_active ON 产品(active);
CREATE INDEX idx_products_featured ON 产品(featured) WHERE featured = 真;
CREATE INDEX idx_products_metadata ON 产品 USING gin(metadata);

外键:

ALTER TABLE 产品
  ADD CONSTRAINT fk_products_category
  FOREIGN KEY (category_id)
  REFERENCES categories(id)
  ON DELETE RESTRICT;

全文搜索:

-- 添加全文搜索列
ALTER TABLE 产品 ADD COLUMN search_vector tsvector;

-- 创建全文索引
CREATE INDEX idx_products_search ON 产品 USING gin(search_vector);

-- 触发器更新搜索向量
CREATE TRIGGER products_search_vector_update
  BEFORE INSERT OR UPDATE ON 产品
  FOR EACH ROW
  EXECUTE FUNCTION
    tsvector_update_trigger(
      search_vector, 'pg_catalog.english',
      name, description, brand
    );

---

订单

存储客户订单。

列:

列	类型	空值	默认值	描述
id	uuid	否	gen_random_uuid()	主键
order_number	varchar(20)	否	-	人类可读订单ID（唯一）
user_id	uuid	否	-	外键到用户
status	varchar(20)	否	‘pending’	订单状态
subtotal	小数(10,2)	否	-	商品小计
tax	小数(10,2)	否	0	税额
shipping	小数(10,2)	否	0	运费
total	小数(10,2)	否	-	总金额
currency	字符(3)	否	‘USD’	货币代码
notes	文本	是	-	订单备注
shipping_address	jsonb	否	-	收货地址
billing_address	jsonb	否	-	账单地址
created_at	时间戳	否	now()	订单创建时间
updated_at	时间戳	否	now()	最后更新时间
confirmed_at	时间戳	是	-	订单确认时间
shipped_at	时间戳	是	-	发货时间
delivered_at	时间戳	是	-	交货时间
cancelled_at	时间戳	是	-	取消时间

索引:

CREATE UNIQUE INDEX idx_orders_order_number ON 订单(order_number);
CREATE INDEX idx_orders_user_id ON 订单(user_id);
CREATE INDEX idx_orders_status ON 订单(status);
CREATE INDEX idx_orders_created_at ON 订单(created_at);

约束:

ALTER TABLE 订单
  ADD CONSTRAINT orders_status_check
  CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded'));

ALTER TABLE 订单
  ADD CONSTRAINT orders_total_positive
  CHECK (total >= 0);

计算列:

-- 总金额由小计+税+运费计算得出
ALTER TABLE 订单
  ADD CONSTRAINT orders_total_computation
  CHECK (total = subtotal + tax + shipping);

---

订单项

每个订单的行项。

列:

列	类型	空值	默认值	描述
id	uuid	否	gen_random_uuid()	主键
order_id	uuid	否	-	外键到订单
product_id	uuid	否	-	外键到产品
product_snapshot	jsonb	否	-	订单时的产品数据
quantity	整数	否	-	订购数量
unit_price	小数(10,2)	否	-	单价
subtotal	小数(10,2)	否	-	行项总计
created_at	时间戳	否	now()	记录创建时间

索引:

CREATE INDEX idx_order_items_order_id ON 订单项(order_id);
CREATE INDEX idx_order_items_product_id ON 订单项(product_id);

外键:

ALTER TABLE 订单项
  ADD CONSTRAINT fk_order_items_order
  FOREIGN KEY (order_id)
  REFERENCES 订单(id)
  ON DELETE CASCADE;

ALTER TABLE 订单项
  ADD CONSTRAINT fk_order_items_product
  FOREIGN KEY (product_id)
  REFERENCES 产品(id)
  ON DELETE RESTRICT;

约束:

ALTER TABLE 订单项
  ADD CONSTRAINT order_items_quantity_positive
  CHECK (quantity > 0);

ALTER TABLE 订单项
  ADD CONSTRAINT order_items_subtotal_computation
  CHECK (subtotal = quantity * unit_price);

---

视图

active_products_view

显示只有活跃的产品及其类别信息。

CREATE VIEW active_products_view AS
SELECT
  p.id,
  p.name,
  p.slug,
  p.description,
  p.price,
  p.compare_at_price,
  p.sku,
  p.brand,
  c.name as category_name,
  c.slug as category_slug,
  (SELECT COUNT(*) FROM 订单项 oi WHERE oi.product_id = p.id) as times_ordered,
  (SELECT AVG(rating) FROM 产品评价 pr WHERE pr.product_id = p.id) as avg_rating
FROM 产品 p
JOIN categories c ON p.category_id = c.id
WHERE p.active = 真;

user_order_summary

每个用户的聚合订单统计信息。

CREATE MATERIALIZED VIEW user_order_summary AS
SELECT
  u.id as user_id,
  u.email,
  u.name,
  COUNT(o.id) as total_orders,
  SUM(o.total) as total_spent,
  AVG(o.total) as average_order_value,
  MAX(o.created_at) as last_order_date,
  MIN(o.created_at) as first_order_date
FROM 用户 u
LEFT JOIN 订单 o ON u.id = o.user_id AND o.status != 'cancelled'
GROUP BY u.id, u.email, u.name;

-- 刷新策略
CREATE INDEX idx_user_order_summary_user_id ON user_order_summary(user_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY user_order_summary;

---

函数

calculate_order_total

计算订单总额，包括税费和运费。

CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal 小数,
  p_tax_rate 小数,
  p_shipping 小数
)
RETURNS 小数 AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::numeric, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

update_updated_at_column

触发器函数，自动更新updated_at时间戳。

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

---

数据字典

枚举类型

-- 订单状态值
CREATE TYPE order_status AS ENUM (
  'pending',
  'confirmed',
  'processing',
  'shipped',
  'delivered',
  'cancelled',
  'refunded'
);

-- 支付状态值
CREATE TYPE payment_status AS ENUM (
  'pending',
  'processing',
  'succeeded',
  'failed',
  'refunded'
);

JSONB结构

shipping_address格式

{
  "street": "123 Main St",
  "street2": "Apt 4B",
  "city": "New York",
  "state": "NY",
  "postalCode": "10001",
  "country": "US"
}

product_snapshot格式

{
  "name": "产品名称",
  "sku": "PROD-123",
  "price": 99.99,
  "image": "https://cdn.example.com/product.jpg"
}

---

迁移

迁移: 20250115120000_add_two_factor_auth

-- 上
ALTER TABLE 用户 ADD COLUMN two_factor_enabled 布尔 DEFAULT 假;
ALTER TABLE 用户 ADD COLUMN two_factor_secret VARCHAR(32);

CREATE TABLE two_factor_backup_codes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES 用户(id) ON DELETE CASCADE,
  code_hash VARCHAR(255) NOT NULL,
  used_at 时间戳,
  created_at 时间戳 DEFAULT NOW()
);

CREATE INDEX idx_2fa_backup_codes_user_id ON two_factor_backup_codes(user_id);

-- 下
DROP TABLE two_factor_backup_codes;
ALTER TABLE 用户 DROP COLUMN two_factor_secret;
ALTER TABLE 用户 DROP COLUMN two_factor_enabled;

---

性能优化

推荐索引

-- 频繁查询的列
CREATE INDEX CONCURRENTLY idx_users_email_verified ON 用户(email_verified);
CREATE INDEX CONCURRENTLY idx_products_price ON 产品(price);
CREATE INDEX CONCURRENTLY idx_orders_user_status ON 订单(user_id, status);

-- 常用查询的复合索引
CREATE INDEX CONCURRENTLY idx_products_category_active
  ON 产品(category_id, active)
  WHERE active = 真;

CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON 订单(user_id, created_at DESC);

查询优化

-- EXPLAIN ANALYZE用于慢查询
EXPLAIN ANALYZE
SELECT p.*, c.name as category_name
FROM 产品 p
JOIN categories c ON p.category_id = c.id
WHERE p.active = 真
ORDER BY p.created_at DESC
LIMIT 20;

-- 如有需要，添加覆盖索引
CREATE INDEX idx_products_active_created
  ON 产品(active, created_at DESC)
  INCLUDE (name, price, slug);

---

备份与恢复

备份计划

• 全备份: 每天凌晨2点UTC
• 增量备份: 每6小时一次
• WAL归档: 持续
• 保留期: 30天

备份命令

# 全备份
pg_dump -h localhost -U postgres -Fc database_name > backup.dump

# 恢复
pg_restore -h localhost -U postgres -d database_name backup.dump

# 备份特定表
pg_dump -h localhost -U postgres -t 用户 -t 订单 database_name > tables.sql

---

数据保留政策

表	保留期	归档策略
用户	无限期	软删除后2年不活跃
订单	7年	2年后移动到归档
订单项	7年	与订单一起移动到归档
日志	90天	保留期后删除

## 最佳实践

### ✅ 做
- 记录所有表格和列
- 创建ERD图
- 记录索引和约束
- 包含示例数据
- 记录外键关系
- 显示JSONB字段结构
- 记录触发器和函数
- 包含迁移脚本
- 精确指定数据类型
- 记录性能考虑

### ❌ 不做
- 跳过约束文档
- 忘记版本控制架构变更
- 忽略性能影响
- 跳过索引文档
- 忘记记录枚举值

## 资源

- [PostgreSQL文档](https://www.postgresql.org/docs/)
- [dbdiagram.io](https://dbdiagram.io/) - ERD工具
- [SchemaSpy](https://schemaspy.org/) - 架构文档生成器
- [Mermaid ERD语法](https://mermaid.js.org/syntax/entityRelationshipDiagram.html)