一、五张表的结构

注意 tb_checkin 里存的是 room_id，不是房号。 这是「用代理主键、不拿业务字段当外键」的常规做法——房号改了（比如酒店重编号），住单不用动。 房态 state 用中文枚举，看着不专业但是对的： 它只有三个值，而且这个词业务人员天天说。 用 CHECK 把范围锁死，比用数字码再加一张字典表省事得多。 什么时候该用数字码？ 值会变多、要支持多语言、或者要排序时。 房态只有三种、不排序、不用多语言——中文枚举就是最合适的。

二、约束现在就撞一遍

三条都被拦住了：价格不能为负、房号不能重复、有住单的房删不掉。 这些规则写在表上，任何入口都绕不过去——写在代码里，换个入口就漏。 第三条要特别注意：SQLite 必须先 PRAGMA foreign_keys = ON， 不然那条删除会「成功」——然后住单就指向一个不存在的房（14.4 实测过这个坑）。 还有一条隐含的约束值得想： 「同一间房同时只能有一张在住单」—— 这个没法用普通 UNIQUE 表达（因为历史住单可以有多张）， 得用「部分索引」或者在事务里锁。 18.7 会再说。

三、表间关系：查一次就知道设计对不对

SELECT r.rno, g.gname, ci.in_time, ci.days, ci.state
  FROM tb_checkin ci
  JOIN tb_room  r ON r.id = ci.room_id
  JOIN tb_guest g ON g.id = ci.guest_id

一条查询连了三张表，这就是「关系型」的意义： 数据各存一份，靠外键连起来，查的时候拼一次。 如果当初把「客人姓名」也塞进 tb_checkin， 这条查询就变成单表——看起来简单了，代价是同一个人的姓名存了几十份，改一次要改几十处。 这个判断办法很好用： 写完查询觉得「怎么这么绕」，往往不是查询写得差，是表设计有问题。 反过来说，如果为了少连一张表而冗余字段，短期舒服、长期难受。

四、索引：现在只加一个就够

小酒店的数据量（几万单）根本不需要索引。 但有一类查询会天天跑：按日期查报表。 所以现在只加一个就够：

CREATE INDEX ix_ci_intime ON tb_checkin (in_time);
CREATE INDEX ix_ci_state  ON tb_checkin (state);

两个索引，理由不同： in_time 是为了「查某个月的入住」（范围查询，14.2 讲过范围能走索引）； state 是为了「查所有在住的」—— 虽然它选择性低（只有两三种值），但因为这个查询太频繁，收益大于代价。 其余的表一概不加。 索引不是「加了总比不加好」—— 每个索引都在拖慢写入（实测插 2 万行多花 47%，14.1 的数）。 等真慢了再加，那时候才知道该加在哪。

五、动手 Demo：建表、撞约束、三表连接验证

这一段建五张表、塞样例数据，然后三条约束各撞一次， 最后用一条三表连接的查询验证「设计对不对」。 第三条撞击（删有住单的房）需要先打开外键开关——不然它会「成功」。

c = sqlite3.connect(u"hotel.db", isolation_level=None)
c.execute(u"PRAGMA foreign_keys = ON")     # 不开这个，外键是摆设
c.executescript(DDL)                       # 五张表

# ① 价格不能为负
c.execute(u"INSERT INTO tb_room (rno, rtype, price, state)"
          u" VALUES ('999','标准间',-1,'空闲')")   # -> CHECK failed

# ② 房号不能重复
c.execute(u"INSERT INTO tb_room (rno, rtype, price, state)"
          u" VALUES ('101','标准间',288,'空闲')")  # -> UNIQUE failed

# ③ 有住单的房删不掉
c.execute(u"INSERT INTO tb_checkin (room_id, guest_id, in_time, days, state)"
          u" VALUES (1, 1, '2026-05-01 14:00', 2, '在住')")
c.execute(u"DELETE FROM tb_room WHERE id = 1")       # -> FK failed

实跑输出：

--------------------------------------------------------------------
数据库设计：表结构决定后面好不好写
--------------------------------------------------------------------


  ① 五张表的结构
  --------------------------------------------------------------------
| 表 | 主键 | 关键列 | 关键约束 |
|---|---|---|---|
| tb_room | id | rno 房号、rtype 类型、price、state | rno 唯一；price > 0；state 三选一 |
| tb_guest | id | gname、idcard、phone | idcard 唯一 |
| tb_checkin | id | room_id、guest_id、in_time、days、state | 两个外键；days > 0 |
| tb_bill | id | ci_id、room_fee、other、total | 外键指向住单；金额非空 |
| tb_user | id | uname、upass、role | uname 唯一 |

  注意 tb_checkin 里存的是 room_id，不是房号。
  这是「用代理主键、不拿业务字段当外键」的常规做法——房号改了（比如酒店重编号），住单不用动。
  房态 state 用的是中文枚举，看着不专业但是对的：它只有三个值，而且这个词业务人员天天说。
  用 CHECK 把范围锁死，比用数字码再加一张字典表省事得多。

  ② 约束现在就撞一遍
  --------------------------------------------------------------------

  1> INSERT INTO tb_room (rno, rtype, price, state) VALUES ('999', '标准间', -1, '空闲')
  消息 102，级别 15，状态 1
  CHECK constraint failed: price > 0
  SQLite 原话：CHECK constraint failed: price > 0

  1> INSERT INTO tb_room (rno, rtype, price, state) VALUES ('101', '标准间', 288, '空闲')
  消息 102，级别 15，状态 1
  UNIQUE constraint failed: tb_room.rno
  两条都被拦住了：价格不能为负、房号不能重复。
  这些规则写在表上，任何入口都绕不过去——写在代码里，换个入口就漏。

  ③ 表间关系：查一次就知道设计对不对
  --------------------------------------------------------------------


  1> SELECT r.rno, g.gname, ci.in_time, ci.days, ci.state
     FROM tb_checkin ci JOIN tb_room r ON r.id = ci.room_id JOIN tb_guest g ON g.id
         = ci.guest_id
  rno  gname  in_time           days  state
  -----------------------------------------
  101  张伟   2026-05-01 14:00  2     在住
  (1 行受影响)
  一条查询连了三张表，这就是「关系型」的意义：数据各存一份，靠外键连起来，查的时候拼一次。
  如果当初把「客人姓名」也塞进 tb_checkin，这条查询就变成单表——看起来简单了，
  代价是同一个人的姓名存了几十份，改一次要改几十处。

  ④ 反例：删掉还有人在住的房
  --------------------------------------------------------------------

  1> DELETE FROM tb_room WHERE id = 1
  消息 102，级别 15，状态 1
  FOREIGN KEY constraint failed
  房 101 有人住着，删不掉。 这是引用完整性在起作用（14.4 讲的）。
  注意 SQLite 必须先 PRAGMA foreign_keys = ON，不然这条删除会「成功」，
  然后住单就指向一个不存在的房。

三条报错串起来看，就是「约束在替你守门」的样子。 而且它们报的是「哪个约束」，不是「哪条代码」——这就是声明式约束的好处：报错自带解释。 第三条最容易出问题： 如果忘了 PRAGMA foreign_keys = ON，删除会成功、不报错， 然后住单里那个 room_id = 1 就指向一个不存在的房。 这种「孤儿数据」不会立刻出问题，但报表一对账就全乱。 三表连接那条查询能一次成功，说明设计是对的。 如果写起来很别扭（比如不得不用 IN 子查询绕），那多半是「该拆的没拆」或者「该连的没连」。 还有一个隐藏收益： 这几条约束让测试变简单了—— 不用写代码去测「价格能不能为负」，数据库自己会拦。

30 秒实验

1. 给 tb_bill 加一条 CHECK (total = room_fee + other)，然后插一条不相等的记录。 会被拦住——这就是「把业务算式也变成约束」，比在代码里算可靠得多。
2. 把 PRAGMA foreign_keys 那行注释掉，重跑第三条撞击。 删除会成功——然后用一条 NOT IN 的查询把孤儿住单找出来。
3. 想一想：tb_guest.idcard 加了 UNIQUE，那「一个客人用护照和身份证各登记一次」怎么办？ 这说明证件号唯一这条规则本身不完整——真系统里要按「证件类型 + 号码」联合唯一。

常见误区

1. 「外键只是文档，实际不生效」。 SQLite 上默认真的不生效（要 PRAGMA 打开），SQL Server 上默认生效。 所以「有没有外键」和「外键有没有在管」是两件事。
2. 「房号当外键更直观」。 房号是业务字段，会改（重编号、加楼层前缀）。外键指向 id，改房号只需改一处。
3. 「索引先全加上，反正不亏」。 亏在写入上——每个索引都要跟着增删改维护，而且没人用的索引纯属负担。等慢了再加。

下一篇 18.4 讲公共模块：实体类（Room）和数据库操作类（Db）怎么写， 以及为什么「参数化写在公共类里」是安全的关键一步。

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

相关笔记

• 系统设计
• 公共模块设计
• 数据完整性
• 48 课 MySQL从入门到精通
• 🪟 SQL Server从入门到精通 · 索引