一、七张表各管一段

「一行代表」这一列是主键的定义。 tb_employee 一行是一个人，所以工号唯一； tb_attend 一行是「某人某天」，所以 (emp_id, adate) 联合唯一。 「一行代表什么」说不清的表，一定是设计错了。 比如把调岗记录塞进 tb_employee—— 那一行就既代表「这个人」又代表「这次调动」，两件事挤在一行，早晚打架。

二、部门那棵树：parent 指向自己

parent 指向 tb_dept 自己，这就是自引用。 根节点的 parent 是 NULL—— 用 NULL 表示「没有上级」，比用 0 好（0 会被外键拦，还得插一条 id=0 的假记录）。 查这种树必须递归。 普通 JOIN 一次只能下一层， 三层树要写两次 JOIN，五层要写四次，层数一变 SQL 就得改。 递归的写法是「先定起点，再拿上一层的结果接下一层」—— 起点是根（parent IS NULL），递归部分是「谁的 parent 等于上一层」。 这个结构在 SQL Server 里叫 CTE（公用表表达式）。

三、递归的第一条：先说清「从哪开始」

递归查询最容易错的地方是「锚点写错」。 写成 WHERE parent = 1，就只从技术部开始——别的分公司全查不到。 第二个容易错的地方是「没有终止条件」。 如果数据里出现了环（A 的父是 B，B 的父是 A），递归会一直转下去—— SQL Server 靠 MAXRECURSION 兜底（默认 100 层），SQLite 会一直跑到内存耗尽。 所以「造环」这件事必须在写入时拦住，数据库不拦。 办法是改上级之前先查一遍「新上级是不是自己的子孙」——这条检查只能在业务层或者触发器里做。

四、四条约束试撞：制度真的能拦住错数据

四条里最有价值的是最后一条。 外键的默认行为是 RESTRICT（拒绝删除）， 它保护的不是「部门表」，是「员工表里的 dept_id 不会指向空气」。 如果真要让「删部门时员工跟着走」，得写 ON DELETE CASCADE。 但人事系统里没有人这么干——删部门导致删员工，这是事故不是功能。 这一条可以推广成一句话： 级联删除只在「子记录没有独立价值」时才用（订单明细跟着订单走）。 一条员工记录有独立价值，就绝不能级联。

五、视图：把三张表拼成人话

v_emp 把三张表拼成界面上能直接显示的样子。 每个列表窗体都直接 SELECT * FROM v_emp，不用每次写三表 JOIN。 视图在这里还有个隐形好处： 以后部门表改名、加个排序字段，只要视图跟着改，所有窗体的 SQL 一行都不用动。 代价是「看不出来它读了哪几张表」。 所以视图的命名要能说明用途（v_emp 是员工视图， v_salary 是工资视图），别叫 v1、v2。

六、动手 Demo：建七张表，跑通部门树的递归查询

这一段把七张表建出来、灌上样例数据，然后用递归把部门树从根走一遍。 注意第四节那四条试撞——每一条都是「制度原话落成约束」之后的实际效果。

with c.execute(''') as cur:
    pass

sql = '''
WITH RECURSIVE t(id, dname, path, lv) AS (
  SELECT id, dname, dname, 1
    FROM tb_dept WHERE parent IS NULL       -- 锚点：根
  UNION ALL
  SELECT d.id, d.dname, t.path || ' / ' || d.dname, t.lv + 1
    FROM tb_dept d JOIN t ON d.parent = t.id   -- 递归：往下一层
)
SELECT id, path, lv FROM t ORDER BY path
'''
for r in c.execute(sql):
    print(r)

# 四条约束试撞
for label, sql in TESTS:
    try:
        c.execute(sql)
        print(label, '竟然成功了')
    except Exception as e:
        print(label, '被拦住：', e)

实跑输出：

--------------------------------------------------------------------
数据库设计：员工、部门、岗位、考勤、工资
--------------------------------------------------------------------


  ① 七张表的分工
  --------------------------------------------------------------------
| 表 | 存什么 | 一行代表 |
|---|---|---|
| tb_dept | 部门，自引用成树 | 一个部门 |
| tb_post | 岗位、职级、薪酬档 | 一个岗位 |
| tb_employee | 员工主档 | 一个在职（或离职）的人 |
| tb_transfer | 调岗记录 | 一次调动 |
| tb_attend | 每日考勤 | 某人某天 |
| tb_salary | 月度工资 | 某人某月 |
| tb_user | 登录账号 | 一个能登进来的人 |

  看「一行代表」这一列：这就是主键该管什么。tb_employee 一行是一个人，所以工号唯一；
  tb_attend 一行是「某人某天」，所以 (emp_id, adate) 联合唯一。
  「一行代表什么」说不清楚的表，一定是设计错了。
  比如把调岗记录塞进 employee 表，那一行就既代表「这个人」又代表「这次调动」——两件事挤在一行，
  早晚打架。

  ② 部门是一棵树：parent 指自己
  --------------------------------------------------------------------

  1> SELECT id, dname, parent, mgr FROM tb_dept ORDER BY id
  id  dname     parent  mgr
  ----------------------------
  1   总公司    NULL    陈国强
  2   技术部    1       王志远
  3   市场部    1       郑晓丹
  4   人事部    1       李慧
  5   财务部    1       NULL
  6   研发一组  2       孙浩
  7   研发二组  2       周雅静
  (7 行受影响)
  parent 指向 tb_dept 自己，这就是自引用。根节点（总公司）的 parent 是 NULL。


  1> WITH RECURSIVE t(id, dname, path, lv) AS ( SELECT id, dname, dname, 1 FROM
         tb_dept WHERE parent IS NULL UNION ALL SELECT d.id, d.dname, t.path || ' / ' ||
         d.dname, t.lv + 1 FROM tb_dept d JOIN t ON d.parent = t.id )
     SELECT id, path, lv
     FROM t
     ORDER BY path
  id  path                        lv
  ----------------------------------
  1   总公司                      1
  4   总公司 / 人事部             2
  3   总公司 / 市场部             2
  2   总公司 / 技术部             2
  6   总公司 / 技术部 / 研发一组  3
  7   总公司 / 技术部 / 研发二组  3
  5   总公司 / 财务部             2
  (7 行受影响)
  ! WITH RECURSIVE 是 SQLite 的写法；SQL Server 写 WITH t AS (...)，去掉 RECURSIVE。
  自引用表的查询必须递归，普通 JOIN 一次只能下一层。
  这是「树」和「链」的根本区别——链有多长一眼看得出，树的深度要看数据。
  代价是：改一个部门的上级，可能造出一个环（A 的父是 B，B 的父是 A）。
  数据库不拦这个，得在业务层或触发器里查一遍「新上级是不是自己的子孙」。

  ③ 表间关系：四处试撞
  --------------------------------------------------------------------

| 试的事 | 结果 |
|---|---|
| 插一个 dept_id=99 的员工 | 外键拦住（无此部门） |
| 插 sex='未知' 的员工 | CHECK 拦住 |
| 同一人同一天插两条考勤 | UNIQUE 拦住 |
| 删掉有员工的部门 | 外键拦住（RESTRICT） |


  甲 不存在的部门：
    → 被拦住：FOREIGN KEY constraint failed

  乙 非法的性别：
    → 被拦住：CHECK constraint failed: sex IN ('男', '女')

  丙 重复考勤：
    → 被拦住：UNIQUE constraint failed: tb_attend.emp_id, tb_attend.adate

  丁 删有人的部门：
    → 被拦住：FOREIGN KEY constraint failed
  四条里最有价值的是丁那条。
  外键的默认行为是 RESTRICT（拒绝删除），它保护的不是「部门表」，
  是「员工表里的 dept_id 不会指向空气」。
  如果真要让「删部门时员工跟着走」，得写 ON DELETE CASCADE。
  但人事系统里没有人这么干——删部门导致删员工，这是事故不是功能。

  ④ 建出来看看：员工视图
  --------------------------------------------------------------------


  1> SELECT * FROM v_emp ORDER BY eno
  eno   ename   sex  dept      post      level  hire        state
  ---------------------------------------------------------------
  E001  陈国强  男   总公司    总经理    10     2015-03-01  在职
  E002  李慧    女   人事部    部门经理  8      2017-06-15  在职
  E003  王志远  男   技术部    部门经理  8      2016-09-01  在职
  E004  赵敏    女   人事部    专员      3      2019-04-08  在职
  E005  孙浩    男   研发一组  主管      6      2018-11-20  在职
  E006  周雅静  女   研发一组  工程师    4      2020-07-01  在职
  E007  吴鹏    男   研发二组  工程师    4      2021-03-15  在职
  E008  郑晓丹  女   市场部    专员      3      2022-05-10  在职
  (8 行受影响)
  v_emp 把三张表拼成「人话」：工号、姓名、性别、部门名、岗位名、职级、入职日、状态。
  界面上每一行列表都直接用它，不用每次写三表 JOIN。
  视图在这里还有个隐形好处：以后部门表改名、加工序，只要视图跟着改，所有窗体的 SQL 一行都不用动。

递归结果里 lv 那一列是层数（根是 1），path 是「从根到自己的路径」。 路径这一列在界面上直接当树控件的文本用，排序也用它——同一父节点下的兄弟会自动挨在一起。 path 用字符串拼是图省事。 正式项目里更好的做法是存「层级码」（001、001001、 001001002），既好排序又好查子树（WHERE code LIKE '001001%'）。 但层级码有个硬伤：插入一个中间层，后面所有码都得改。 所以「parent 指针」和「层级码」经常两个都存——一个保证正确，一个保证查询快。

30 秒实验

1. 把锚点改成 WHERE parent = 2，看结果少了几行。这就是「只从技术部开始」的效果。
2. 试插 sex='未知' 的员工，把报错信息抄下来。再想想：这个报错该不该原样显示给用户？ 不该——要翻译成「性别只能选男或女」。
3. 想一想：如果要把「研发一组」整个搬到「市场部」下，要改几行？ 一行（改 parent）。但改完还要检查什么？ 检查「市场部」不是「研发一组」的子孙，否则就成环了。

常见误区

1. 「树用 JOIN 查就行了」。 一次 JOIN 只能下一层。 层数一变 SQL 就得改，而且改不对还查不出来。
2. 「外键会拖慢性能，先不建」。 外键保护的是数据一致性，不是性能。 真到了性能瓶颈，先看索引，别先删约束。
3. 「删部门时让员工跟着删就行」。 这是事故不是功能。 员工有独立价值（工资、考勤、历史）， 级联删除只适合「子记录没有独立价值」的场合。

下一篇 19.4，写公共模块： 一个实体类对应一行、一个 Db 类管四个方法——顺便试试「姓名里带个单引号」会发生什么。

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

相关笔记

• 系统设计
• 数据库设计
• 连接查询
• 关系的创建与维护
• 🪟 SQL Server从入门到精通 · 索引