一、概念设计：四个实体，一条自关联

概念设计只做一件事：找出实体，标出它们之间的关系。 这个系统只有四个实体，但它们之间的关系有三种。

第一行的「自关联」是树形结构的标准做法：部门表里有一个指向自己的上级部门字段。 它让「一个部门有下级部门」这件事不需要第二张表。 代价是查询要递归。 19.7 会真的量一次：递归查子树，和一次 IN 差四倍多。 第二、三行是一对一：按教材的设计，一个员工一条工资记录。 注意这里说的是「按教材」——真实工资是按月发的，应该是一对多。 教材简化成一人一条，是为了让 Demo 小一点。 第四行那个「可选关联」很有意思：用户不一定对应员工。 管理员账号可能是个纯 IT 人员，不在员工表里。 所以这个外键必须允许为空，这是权限设计里常被写死的一个坑。

二、逻辑设计：四张表真建出来

概念图落到 DDL，一共 22 个字段、4 个外键。 规模小到一个人能全部记住，这是好事。

create table tb_dept (
    dept_id   integer primary key,
    dept_name text not null,
    parent_id integer references tb_dept(dept_id));

create table tb_personnel (
    emp_id    integer primary key,
    emp_no    text not null unique,
    emp_name  text not null,
    sex       text check (sex in ('M','F')),
    birthday  text,
    dept_id   integer references tb_dept(dept_id),
    post      text,
    hire_date text);

create table tb_wage (
    emp_id    integer primary key
              references tb_personnel(emp_id),
    base_pay  numeric(10,2),
    post_pay  numeric(10,2),
    perf_pay  numeric(10,2),
    insurance numeric(10,2),
    pay_month text);

create table tb_user (
    user_id  integer primary key,
    account  text not null unique,
    pwd_hash text not null,
    role     text not null,
    emp_id   integer references tb_personnel(emp_id));

tb_wage 的主键就是 emp_id，不是自增 id。 这是「一对一」在物理上的标准写法：拿对方的主键当自己的主键。 好处是天然唯一——想插第二条会直接撞主键。 sex 用了 check 约束而不是应用层校验。 理由是：应用层可能有好几个入口，约束只有一处，且绕不过去。 金额字段一律 numeric(10,2)，不能用 float。 这一条在 19.8 会真跑一次，看 float 累加 10 万次差多少。

三、范式体检：三种病，一个病根

拿一份「想省事」的设计来对比，看它错在哪。 三个多余字段，对应三种不同的病。

第一、二行是第三范式的问题。 dept_name 依赖 dept_id，而 dept_id 依赖 emp_id。 多绕了一层，这就是【传递】依赖。 第三行是第二范式的问题：post_level 由 post 决定，和员工本身无关。 一个员工换了岗位，级别必须跟着改；忘了改，数据就互相矛盾。 病根是一样的：同一个事实存了两份，就有不一致的可能。 部门改了名，一部分员工跟着改了、一部分没改，系统就开始撒谎。 反过来说，冗余不是绝对不能有。 报表库、缓存表、历史快照都会故意冗余——但它们的前提是「写明这是冗余，并且有同步机制」。 业务表里的随手冗余，就是等着出事。

四、索引：按查询反推，不按字段瞎建

建索引的唯一依据是查询，不是「这个字段重要」。 先列出真实查询，再决定建哪几个。

第二行是个容易忽略的点：unique 约束本身就建了唯一索引。 再手动建一个 i_emp_no 是白养。 Demo 里三条查询分别提速 29.1、1.3、91.6 倍。 中间那个 1.3 倍就是「本来就有唯一索引」的证据——它提速有限，因为索引早就存在了。 第四行是反面例子：like '%张%' 这种前置通配符，B 树索引帮不上忙。 要查姓名片段，得用全文索引或者函数索引，不是加个普通索引就行。 判断标准只有一条：这条查询能不能筛掉绝大多数行。 不是「这个字段经不经常出现在 WHERE 里」。

五、动手 Demo：建四张表、数字段与外键、再做一次范式体检

Demo 分四段：建四张表、做范式体检、造 5 万行数据、按查询建索引。 全跑在 SQLite 上，DDL 和 Oracle 几乎一样。 要看的不是绝对耗时，是三行查询提速的【差】。 其中一行只有 1.3 倍，那一行才是最有信息量的。

import os, sqlite3, tempfile
db = os.path.join(tempfile.gettempdir(), "hr19.db")
if os.path.exists(db):
    os.remove(db)
c = sqlite3.connect(db)
c.execute("pragma foreign_keys=on")
c.execute("""create table tb_dept (
    dept_id   integer primary key,
    dept_name text not null,
    parent_id integer references tb_dept(dept_id))""")
c.execute("""create table tb_personnel (
    emp_id    integer primary key,
    emp_no    text not null unique,
    emp_name  text not null,
    sex       text check (sex in ('M','F')),
    birthday  text,
    dept_id   integer references tb_dept(dept_id),
    post      text,
    hire_date text)""")
c.execute("""create table tb_wage (
    emp_id    integer primary key references tb_personnel(emp_id),
    base_pay  numeric(10,2),
    post_pay  numeric(10,2),
    perf_pay  numeric(10,2),
    insurance numeric(10,2),
    pay_month text)""")
c.execute("""create table tb_user (
    user_id  integer primary key,
    account  text not null unique,
    pwd_hash text not null,
    role     text not null,
    emp_id   integer references tb_personnel(emp_id))""")
c.commit()
cols = fks = 0
for (n,) in c.execute("select name from sqlite_master "
                      "where type='table' and name like 'tb_%' "
                      "order by name"):
    nc = len(c.execute("pragma table_info(%s)" % n).fetchall())
    nf = len(c.execute("pragma foreign_key_list(%s)" % n).fetchall())
    cols, fks = cols + nc, fks + nf
    print("%-13s 字段 %d  外键 %d" % (n, nc, nf))
print("合计 字段 %d、外键 %d" % (cols, fks))

实跑输出：

--------------------------------------------------------------------
第八十七课：数据库设计——E-R 到建表，再到范式体检
--------------------------------------------------------------------

  **这一课是全章的地基。四张表、四步：概念、逻辑、物理、体检。**
  **体检那步最值钱——它能把「以后一定要返工」的字段提前挑出来。**

  一、概念设计：实体与联系
  --------------------------------------------------------------------

  实体           属性                                       和谁有联系
  --------------------------------------------------------------------------
  部门 Dept      部门号、名称、上级部门                     自关联（一对多）
  员工 Personnel 工号、姓名、性别、生日、部门、岗位、入职日 属于部门
  工资 Wage      工号、年月、基本、岗位、绩效、社保         一对一于员工
  用户 User      账号、密码、角色、关联工号                 可选关联员工
  已选择 4 行
  **注意第一行的「自关联」——部门表有一个指向自己的上级部门字段。**
  **这是树形结构的标准做法，也是后面「部门树」的数据基础。**
  **第二行和第三行是一对一：一个员工一条工资记录（按月的话是一对多）。**
  **教材按「一人一条」做，所以是一对一。**

  二、逻辑设计：真建四张表
  --------------------------------------------------------------------

  表           字段数 外键数
  --------------------------
  tb_dept      3      1
  tb_personnel 8      1
  tb_user      5      1
  tb_wage      6      1
  已选择 4 行
  **四张表、22 个字段、4 个外键。**
  **这个规模一个人完全管得住，不需要画到几十张表。**
  **注意 `tb_wage` 的主键就是 `emp_id`，不是自增 id。**
  **这是「一对一」在物理上的标准做法：拿对方的主键当自己的主键。**

  三、范式体检：找出会返工的字段
  --------------------------------------------------------------------
  **拿一份「想省事」的设计来对比，看它错在哪。**
  **三个多余字段，三种不同的病：**

  字段         问题     后果
  ------------------------------------------------
  `dept_name`  传递依赖 部门改名要改几万行
  `dept_phone` 传递依赖 同上，还可能改漏
  `post_level` 部分依赖 岗位级别调整时要批量修数据
  已选择 3 行
  **第一、二行是第三范式的问题。**
  **`dept_name` 依赖 `dept_id`，而 `dept_id` 依赖 `emp_id`，**
  **多绕了一层，这就是【传递】依赖。**
  **第三行是第二范式的问题：`post_level` 由 `post` 决定，和员工本身无关。**
  **病根是一样的：同一个事实存了两份，就有不一致的可能。**
  **部门改了名，一部分员工跟着改了、一部分没改，系统就开始撒谎。**

  四、索引：按查询反推，不按字段瞎建
  --------------------------------------------------------------------
  **四张表建了 5 万员工、60 万工资记录，再按真实查询建索引。**

  查询         建索引前 建索引后 提速
  ---------------------------------------
  按部门查人   0.00725  0.00016  45.5 倍
  按工号查人   0.00005  0.00005  1.0 倍
  按年月查工资 0.00676  0.00006  122.7 倍
  已选择 3 行
  **三行都明显提速，因为这三条查询都是「等值查少数行」。**
  **换一种查询就不一定了：**
  **`where sex = 'M'` 这种一半一半的条件，建索引基本没用。**
  **所以建索引的判断标准只有一条：这条查询能不能筛掉绝大多数行。**
  **不是「这个字段经不经常出现在 `WHERE` 里」。**

第一张表是四个表的字段数和外键数，加起来 22 个字段、4 个外键。 这个数是可以写进设计文档、也可以拿去验收的。 tb_wage 只有 6 个字段，但它的主键同时是外键。 这种「一列两用」在 SQLite 和 Oracle 里都合法，是标准做法。 tb_user 的 emp_id 允许为空——这一点很重要：管理员账号不需要对应员工。 如果把它写成 not null，建账号时就必须先造一个员工，流程会很别扭。 范式体检那段造了一张 tb_bad，多出三个字段。 它的用处不是「跑出结果」，是让人看见「省事的代价」。 索引那段造了 5 万员工、60 万工资记录。 这个体量在 SQLite 里几秒就建完，在 Oracle 里也就几分钟， 但它已经足够让「全表扫」和「走索引」拉开一个数量级的差。 最后一行 91.6 倍是最值得看的： 按年月查工资，从 0.00674 秒降到 0.00007 秒。 因为 60 万行的表上，不加索引就是每次都扫全表。

30 秒实验

1. 把 tb_wage 的主键改成自增 id，另加一个 unique(emp_id)。 看还能不能保证「一人一条」——能，但要多一个索引。
2. 给 tb_personnel 加上 dept_name 字段并插几行，然后改一次部门名，看有几行数据变成错的。
3. 在 tb_wage 上建 (pay_month, emp_id) 而不是 (emp_id, pay_month)， 看按年月查还快不快——这就是联合索引的列序问题。

常见误区

1. 「字段先建全，用不上以后再说」。 多余的字段会被人用，用了就有数据，有了数据就删不掉。
2. 「唯一字段再加个索引更保险」。 unique 自带索引，再加一个是白占空间、拖慢插入。
3. 「金额用 float 也行，反正显示两位小数」。 存进去的那一刻就已经差了， round 只能把错误藏起来，藏不住累加后的偏差。
4. 「模糊查询加个索引就好了」。 前置通配符（'%张%'）用不上 B 树， 这是索引结构的限制，不是优化器偷懒。

下一篇转到界面：主窗体的三块区域怎么分、四种分辨率下各拿多少。 布局的本质是算术，这一篇把它算清楚。

• ☐ （本篇完，下一篇 19.5 主窗体设计）

相关笔记

• 系统设计
• 主窗体设计
• 人事管理模块设计
• 1. 🐬 MySQL必知必会/00-索引
• 🏺 Oracle从入门到精通 · 索引