一、五张表，四条关系

关系有四条：分类一对多商品、会员一对多订单、订单一对多明细、商品一对多明细。 一对多的外键永远放在「多」的那一边，四条都是这个写法。 这四条在 22、23 章都练过，真正新的是下面三个决定。 三个决定都跟「时间」有关——历史要保住当时的样子，这个要求在三范式里没有位置。

二、决定一：明细表要冗余

第二、三行就是「冗余」。按三范式，它们该由 goods_id 联表查出来，不该存。 但联表查出来的是【今天】的名字和价格，不是【成交那天】的。 一张三个月前的订单，如果显示的是今天的价格，它在财务上就是错的——对账对不上，退款也算不清。 所以这两列不是「没规范化」，是【快照】：语义是「下单那一刻的值」，不是「商品的当前值」。 判断标准很简单：这个字段会不会变？会变，而且历史记录要保持原样，就得存快照。 收货地址、下单时的会员等级、用的优惠券面额，都是同一类东西。

三、决定二：二级分类用自关联

CREATE TABLE tb_category (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  cname     TEXT NOT NULL,
  parent_id INTEGER DEFAULT 0
);

parent_id = 0 表示顶级，非 0 表示挂在某个父类下。 查「某个父类下有哪些子类」就是自己连自己：

SELECT c.cname AS 子类, p.cname AS 父类
FROM tb_category c JOIN tb_category p ON p.id = c.parent_id
WHERE c.parent_id = 2;

自关联的好处是「层数不受表结构限制」，三级、四级也能塞进去。 代价是「查一整棵树」变麻烦了：层数不定，就没法用固定条数的 JOIN。 MySQL 8.0 起有 WITH RECURSIVE，一条 SQL 能把整棵树拉平； 5.7 及以前没有，只能在代码里一层层查，层数多了就是 N 次查询。 实际项目通常只用两级，所以这个代价不明显——这是「够用就好」的一个例子。

四、决定三：商品不能真删

tb_goods 上有一个 is_on 字段，是「上架／下架」的开关。 为什么不直接 DELETE？ 因为明细表里有外键指向它——删了商品，历史订单的明细就指向了不存在的行。 数据库会拦住（FOREIGN KEY constraint failed）。如果你为了让它通过而关掉外键检查， 那就从「报错」变成了「静默的数据损坏」——报错至少还会被人看见。 所以商城的「删除商品」实际上是「下架」：UPDATE tb_goods SET is_on = 0。 后台页面上那个「删除」按钮，背后改的是一个状态字段。 这条规律可以推而广之：凡是【被历史记录引用】的表，都不该真删。 会员、分类、优惠券都一样。「删除」在业务上往往是「改状态」。

五、约束是最后一道闸

四条全被拦住。 第二条值得单独看：明细写了一个不存在的商品，被外键挡下——这一条防的正是上一节说的场景。 第四行是 23 章讲过的「查重代码加唯一约束，两层都要」：代码里的查重是为了友好提示， 唯一约束是为了在并发下真的不重复。商城同样适用。

六、动手 Demo：五张核心表：商品、订单、明细怎么连

这一段建五张表（两条外键、两个唯一约束），灌五百件商品和二十个会员； 然后演示自关联查父子，最后故意违反四条约束，看数据库的反应。

import sqlite3

c = sqlite3.connect(u"mall2.db", isolation_level=None)
c.execute(u"PRAGMA foreign_keys = ON")
c.executescript(u"""
CREATE TABLE tb_category (id INTEGER PRIMARY KEY AUTOINCREMENT,
  cname TEXT NOT NULL, parent_id INTEGER DEFAULT 0);
CREATE TABLE tb_goods (id INTEGER PRIMARY KEY AUTOINCREMENT,
  gname TEXT NOT NULL, cat_id INTEGER, price REAL NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0, is_on INTEGER DEFAULT 1,
  FOREIGN KEY (cat_id) REFERENCES tb_category(id));
CREATE TABLE tb_member (id INTEGER PRIMARY KEY AUTOINCREMENT,
  uname TEXT NOT NULL UNIQUE, upass TEXT NOT NULL);
CREATE TABLE tb_order (id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_no TEXT NOT NULL UNIQUE, member_id INTEGER NOT NULL,
  total REAL NOT NULL, state TEXT DEFAULT '待付款',
  FOREIGN KEY (member_id) REFERENCES tb_member(id));
CREATE TABLE tb_order_item (id INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id INTEGER NOT NULL, goods_id INTEGER NOT NULL,
  gname TEXT, price REAL, qty INTEGER,
  FOREIGN KEY (order_id) REFERENCES tb_order(id) ON DELETE CASCADE,
  FOREIGN KEY (goods_id) REFERENCES tb_goods(id));
""")

# 自关联：数码下面挂手机、电脑
c.execute(u"INSERT INTO tb_category (cname, parent_id) VALUES ('数码', 0)")
pid = c.execute(u"SELECT last_insert_rowid()").fetchone()[0]
c.executemany(u"INSERT INTO tb_category (cname, parent_id) VALUES (?,?)",
              [(u"手机", pid), (u"电脑", pid)])
for r in c.execute(u"SELECT c.cname, p.cname FROM tb_category c "
                   u"JOIN tb_category p ON p.id = c.parent_id "
                   u"WHERE c.parent_id = ?", (pid,)):
    print(u"子类 %s 父类 %s" % r)

# 四条违约
c.execute(u"INSERT INTO tb_order (order_no, member_id, total) VALUES ('T-DUP',1,1.0)")
cases = [
    (u"订单写了不存在的会员", u"INSERT INTO tb_order (order_no, member_id, total) "
     u"VALUES ('X1', 9999, 1.0)"),
    (u"明细写了不存在的商品", u"INSERT INTO tb_order_item (order_id, goods_id) "
     u"VALUES (1, 99999)"),
    (u"订单号重复", u"INSERT INTO tb_order (order_no, member_id, total) "
     u"VALUES ('T-DUP', 1, 1.0)"),
    (u"会员名重复", u"INSERT INTO tb_member (uname, upass) VALUES ('dup','x')"),
]
for name, sql in cases:
    try:
        c.execute(sql)
        print(u"%s -> 没拦住" % name)
    except sqlite3.Error as e:
        print(u"%s -> %s" % (name, e))
c.close()

实跑输出：

--------------------------------------------------------------------
五张核心表：商品、订单、明细怎么连
--------------------------------------------------------------------

**教材 24.4 讲数据库设计：概念化设计和建库建表。**
**商城的表比前两个项目多，但核心只有五张——掌握这五张，其余都是配件。**

  ① 五张表的关系
  --------------------------------------------------------------------
表               角色      关键点
--------------------------------------------------------
`tb_category`    商品分类  `parent_id` 支持二级分类
`tb_goods`       商品      `cat_id` 外键，`stock` 库存
`tb_member`      会员      `uname` 唯一
`tb_order`       订单主表  `order_no` 唯一，`total` 总额
`tb_order_item`  订单明细  外键 + 级联删除
**第二行的 `stock` 是商城和前两个项目最大的差别：商品有「库存」这个会变的数。**
**库存一出现，就带来「两个人同时买最后一件」的问题——这就是 24.9 那一节要解决的事。**
**第五行的明细表存了 `gname` 和 `price` 两份「冗余」——按理说应该只存 `goods_id`。**
**但这两列是有意冗余的：商品改名、涨价之后，历史订单上显示的应该是「当时」的名字和价格。**
**如果只存 id，那查旧订单会看到今天的价格——这在财务上是错的。**

  ② 二级分类：自关联怎么查
  --------------------------------------------------------------------
子类  父类
----------
手机  数码
电脑  数码
**同一张表自己连自己，这叫自关联。**
**`parent_id = 0` 表示顶级分类，非 0 表示挂在某个父类下。**
**这种设计的优点是「加多少级都不用改表」——虽然一般只用两级。**

  ③ 外键和唯一约束能拦住什么
  --------------------------------------------------------------------
故意违反              数据库的反应
-----------------------------------------------------------------
订单写了不存在的会员  FOREIGN KEY constraint failed
明细写了不存在的商品  FOREIGN KEY constraint failed
订单号重复            UNIQUE constraint failed: tb_order.order_no
会员名重复            UNIQUE constraint failed: tb_member.uname
**四条全拦住，其中第二条值得单独看：明细写了一个不存在的商品，被外键挡住。**
**这条约束防止的是「删了商品之后，历史订单明细指向空白」。**
**所以商品表通常不允许真删——下架改成 `is_on = 0`（24.10 那一节会讲）。**
**这也是商城和前两个项目的一个共同结论：**
**凡是「被历史记录引用」的表，都不该真删；「删除」这个动作在业务上往往是「改状态」。**

这一段先建五张表：两条外键指向会员和商品，一个唯一约束在订单号上，一个在会员名上。 然后演示自关联：先建一个「数码」，再挂「手机」「电脑」两个子类， 用一句自己连自己的 SQL 把父子一起查出来。 最后故意违反四条约束。 四条全是自然报错，没有手工编造——这一点很重要，手写的错误信息证明不了数据库真的会拦。 「明细写了不存在的商品」那一条被外键挡住，它同时证明了两件事：一是约束有效， 二是「删商品」这件事不能随便做。 也就证明了 is_on 这个字段的必要性——它是给「删」找的替代动作。

30 秒实验

1. 把 tb_order_item 的 gname、price 去掉，改成联表查，然后改一次商品名，看旧订单显示什么。
2. 给 tb_category 再加一级（手机 → 苹果 → iPhone），用 WITH RECURSIVE 把整棵树拉平。
3. 把 PRAGMA foreign_keys 关掉，重跑四条违约，看还剩几条能拦住。

常见误区

1. 明细表只存 goods_id。商品一改名、一涨价，历史订单全变。 财务对不上账，而且查不出是哪天开始错的。
2. 商品删除用 DELETE。要么被外键拦住，要么关掉外键后留下悬空明细。 正确做法是 is_on = 0，前端不显示，历史查得到。
3. 分类表用「父类名」当外键。名字一改，整棵树断掉。 外键只能指向主键或唯一键，不能指向「会变的显示名」。

表和约束都到位了，下一篇进公共模块：泛型工具、持久化基类、分页、字符串工具、 实体映射——五个能复用的轮子，也是 SSH 项目里唯一「写一次用到处」的部分。

• ☐ （本篇完，下一篇 24.5 公共模块设计）

相关笔记

• 系统设计
• 系统分析
• 数据库设计
• 🛢️ MySQL从入门到精通 · 索引