ER 建模与范式规范化 · Java 数据库实战

第 6 章 · ER 建模、范式与 shop-db 概念设计

本章目标：读懂并绘制 ER 图（实体-关系图）；掌握 1:1、1:N、N:M cardinality；理解 第一～第三范式（1NF/2NF/3NF） 与 反范式 权衡；完成 shop-db 完整概念模型（含选修实体：购物车、支付、分类树）；输出 docs/shop-db-er.md 与 Mermaid 图；衔接 ch05 DDL 与 spring-boot-web Entity 设计；对照 python-database ch06 平行结构。

学时建议：4～5 小时（含 ER 绘制与范式分析 2 小时）

前置：java-database ch05 DDL 已导入；理解 JOIN 与 FK。

6.1 场景说明：先设计再建表

在 ~/learn-java/shop-db/docs/ 维护 shop-db 概念文档，供团队评审后再写 SQL（ch05）与 JPA Entity（spring-boot-web）。虚构产品 贤紫优选 的 API 域为 api.example.com，核心域：

域	实体
用户	users
商品	products
交易	orders, order_lines
选修	categories, cart_items, payments

本章聚焦概念层；物理表已在 ch05 实现子集。

6.2 ER 图要素

符号	含义
矩形	实体（Entity）如 Product
椭圆	属性（Attribute）如 price
菱形	关系（Relationship）如「下单」
连线 cardinality	1、N、M

Chen 记法 vs Crow's Foot：本课 Mermaid 用 Crow's Foot（乌鸦脚）更常见。

主键属性：id 下划线；业务键：slug、email（UNIQUE 非 PK）。

6.3 shop-db 核心 ER（Mermaid）

erDiagram
    USERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_LINES : contains
    PRODUCTS ||--o{ ORDER_LINES : "sold in"

    USERS {
        bigint id PK
        varchar email UK
        varchar password_hash
        datetime created_at
    }

    PRODUCTS {
        bigint id PK
        varchar slug UK
        varchar title
        varchar category
        int price "分"
        tinyint is_published
    }

    ORDERS {
        bigint id PK
        bigint user_id FK
        varchar status
        int total_amount "分"
        datetime created_at
    }

    ORDER_LINES {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        int quantity
        int unit_price "快照分"
    }

阅读：||--o{ 表示一端必有多端可选（1:N）；||--|{ 表示订单至少一行明细（业务上可放宽为 0..*）。

6.4 cardinality：1:1、1:N、N:M

1:N（一对多） — shop-db 主体：

关系	一	多	外键位置
用户-订单	User	Order	orders.user_id
订单-明细	Order	OrderLine	order_lines.order_id
商品-明细	Product	OrderLine	order_lines.product_id

1:1（一对一） — 选修：

关系	说明	实现
User - UserProfile	扩展资料、地址簿	profile.user_id UNIQUE FK
Order - Payment	单笔单支付	payment.order_id UNIQUE FK

-- 1:1 示例：用户资料
CREATE TABLE user_profiles (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  display_name VARCHAR(100),
  avatar_url VARCHAR(500),
  CONSTRAINT fk_profile_user FOREIGN KEY (user_id) REFERENCES users(id)
);

N:M（多对多） — 必须中间表：

关系	中间表	场景
Product - Tag	product_tags	商品标签
User - Role	user_roles	RBAC（spring-boot-web Security）

CREATE TABLE tags (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE product_tags (
  product_id BIGINT UNSIGNED NOT NULL,
  tag_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, tag_id),
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
);

订单-商品：表面 N:M，经 order_lines 解构为两个 1:N，并带 quantity、unit_price 关系属性。

6.5 关系属性与快照

OrderLine 不仅是关联表，还存储：

属性	为何不在 Product 上读
`unit_price`	下单后改价不影响历史订单
`quantity`	购买数量
`product_id`	指向当前商品；商品下架仍保留 FK

反例（坏设计）：order_lines 只存 product_id，报表用 JOIN products.price —— 对账错误。

api.example.com 订单 PDF：必须读 order_lines.unit_price。

6.6 第一范式（1NF）

规则：字段原子性，无重复组。

违反 1NF	修正
products.tags = `"books,hot,sale"` 逗号分隔	product_tags 中间表
order_lines 存 `"1,2,3"` 多个 product_id	每行一个 product_id
users.phones 存两个手机号一行	user_phones 表或 JSON 列（选修）

shop-db products 每行一个 slug、一个 price → 满足 1NF。

6.7 第二范式（2NF）

规则：在 1NF 基础上，非主键字段完全依赖于主键（针对复合主键）。

示例坏表（复合 PK (order_id, product_id)）：

order_id	product_id	product_title	quantity
1	5	键盘	1

product_title 仅依赖 product_id → 部分依赖 → 违反 2NF。

修正：拆到 products；order_lines 只留 order_id, product_id, quantity, unit_price（unit_price 依赖整行业务语义，视为依赖联合业务键 order_line.id 更简单——order_lines 用 surrogate id PK 时，所有非键列依赖 id → 2NF 自动满足）。

shop-db 采用单列 id PK → 2NF 重点在不要把可独立实体塞进宽表。

6.8 第三范式（3NF）

规则：非主键字段不传递依赖于主键。

坏表示例：

order_line_id	order_id	user_email	product_slug
1	10	user@x.com	java-concurrency

user_email 依赖 order_id → user_id → email，传递依赖 users 表。

修正：

order_lines → orders → users
order_lines → products

不在 order_lines 冗余 user_email；报表用 JOIN（ch03）。

选修反范式：数据仓库宽表可冗余 email 加速 BI——OLTP shop_db 保持 3NF。

6.9 反范式：何时故意冗余

场景	冗余字段	收益	代价
订单列表	orders.total_amount	免每次 SUM lines	与明细同步（事务维护）
商品列表	products.sales_count	免 COUNT 子查询	下单时 +1
分类名	products.category 字符串	简单	改名需批量 UPDATE

shop-db 已采用：

orders.total_amount — 与 order_lines 同事务更新（ch04）；
products.category — 字符串非规范化 category 表（MVP 可接受；扩展见 6.10）。

Redis 缓存 slug 详情（spring-boot-web）不算关系库反范式，是另一存储层。

6.10 选修：categories 规范化

MVP 用 products.category VARCHAR；扩展为树形分类：

erDiagram
    CATEGORIES ||--o{ PRODUCTS : classifies