从 UML 到 BCNF:车辆共享数据库的 18 张表是怎么定下来的
项目代码与报告:https://github.com/yicongl1/Vehicle-Share-Database
车辆共享平台 vehicleShare 的业务听起来不复杂:车主发布车辆,租客预订,取车还车,最后开票。但真正落到关系模型里,会冒出一连串问题:个人、企业、企业司机算不算同一种用户?一次租赁要存几份车况检查?保险、发票、评论应该独立成表还是跟着合同走?被举报三次的公告由谁保证下架?
这是 UTC NF18 的课程项目。我们最终得到 18 张表:1 张用户母表、3 张用户子表、1 张司机表、1 张车辆表、1 张公告表、1 张合同表、2 张车况检查/发票表、1 张评论表,以及保险、国家、选项、时段和 3 张多对多关联表。下面按设计顺序拆一遍。
先处理“人”的继承
平台上有三类主体:车主、个人租客、企业。它们共享登录名、密码、账号状态,但属性不同:
- 车主有电话、邮箱、姓名、年龄;
- 租客还多一个驾照和有效期;
- 企业有地址、城市、邮编,下面还挂多个司机。
最直接的写法是一张 Utilisateur 表,把所有字段都放进去,再用可空列区分类型。那样查询简单,但会出现大量 NULL,而且数据库无法阻止“企业填了驾照”或“个人填了邮编”这种组合。
我们采用母表 + 子表的继承建模:
CREATE TABLE Utilisateur (
pseudo VARCHAR(255) PRIMARY KEY,
mot_de_passe VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL,
actif BOOLEAN,
CHECK (type = 'proprietaire' OR type = 'locataire' OR type = 'entreprise')
);
CREATE TABLE Proprietaire (
pseudo VARCHAR(255) PRIMARY KEY,
telephone VARCHAR(10) CHECK (telephone LIKE '__________') NOT NULL,
age INT CHECK (age >= 18) NOT NULL,
-- ...
FOREIGN KEY (pseudo) REFERENCES Utilisateur(pseudo)
);
子表的主键同时是外键,保证一个账号只能有一种身份。企业司机 Conducteur 则单独一张表,通过 entreprise 外键挂在企业下,因为司机不是平台账号,不需要登录。
这里有一个取舍:母表和子表之间没有数据库层面的“类型必须匹配”约束。也就是说,理论上可以往 Proprietaire 里插一个 type = 'locataire' 的账号。要彻底堵住,需要触发器或应用层校验。我们选择在应用层检查,因为课程重点是把结构表达清楚,而不是把每一道业务规则都写成触发器。
交易链:公告 → 合同 → 检查/发票/评论
核心交易链是:
Annonce → Contrat_location → Etat_des_lieux(取车、还车各一条)
→ Facture
→ Commentaire
几个关键决定:
- 公告和车辆分开。 同一辆车可以先后发布多条公告,公告有自己的标题、评分、举报数和启用状态;车辆本身保存车牌、品牌、型号、里程和油量。
- 合同同时记录车主、车辆和租客。 车主字段看起来冗余(可以通过车辆反查),但合同是历史凭证:车辆可能过户,公告可能删除,合同必须能独立还原当时的车主。
- 租客和企业二选一。 合同表里
locataire和entreprise都可空,但加了一条约束:
CHECK ((locataire IS NOT NULL AND entreprise IS NULL)
OR (locataire IS NULL AND entreprise IS NOT NULL))
这条规则如果只写在 Python 里,任何绕过应用的写入都能破坏它;写进数据库后,它成为表结构的一部分。
- 车况检查用
type区分取车/还车。Etat_des_lieux有type = 'debut' | 'fin',同一合同最多两条。更严格的做法是给(contrat, type)加唯一约束,我们当时没有加,是一个可以改进的点。
哪些规则应该写进数据库
这是这个项目里最值得讨论的部分。我们的 CHECK 大致分三类:
第一类:行内格式与范围。 电话 10 位、年龄 ≥ 18、评分 0–5、油量 0–1、邮编 1–99999、年份 1901–2024。这些规则只依赖当前行,适合 CHECK。
第二类:行内跨字段的业务规则。 公告被举报三次必须停用:
CHECK (NOT(nombre_signalement >= 3 AND activite))
合同租客与企业二选一;合同结束日期必须晚于开始日期;时段 fin > debut。这些也能用 CHECK 表达。
第三类:依赖时间或其它行的规则。 例如“驾照必须在有效期内”“不能预订已经被占用的时段”“合同结束后才能评论”。这些不适合硬塞进 CHECK:
CHECK (validite > NOW())让约束依赖执行时刻,语义会随日期变化,备份/恢复和批量导入都可能出问题;- “时段是否被占用”需要查询其它合同,
CHECK做不到; - 评论需要先确认合同已结束,这是跨表、跨时间的判断。
我们的做法是:数据库负责结构和行内一致性,应用层负责时间、占用和权限。这个分工不是“谁更高级”,而是约束应该放在它能被可靠执行的地方。
规范化:先看函数依赖
18 张表容易让人觉得“是不是过度拆分”。判断标准不是表的数量,而是函数依赖:
Utilisateur的非主属性只依赖pseudo;Vehicule的非主属性只依赖immatriculation;Contrat_location的租客、企业、车辆、车主都依赖id_contrat;Est_disponible、Peut_circuler、Possede是纯关联表,主键是两列的组合,非主属性为空。
没有传递依赖,也没有非主属性依赖部分主键,基本满足 3NF;关联表的主键就是全部属性,满足 BCNF。真正需要警惕的是“把多个值塞进一列”,比如把多个可行驶国家写成逗号分隔的字符串——那会同时破坏原子性和查询能力。
回头看,三处会改
- 金额用
NUMERIC,不用FLOAT。 发票金额是钱,二进制浮点会出现 0.1 + 0.2 这类误差。 - 外键加索引。 建表脚本里没有显式索引;PostgreSQL 不会自动为外键列建索引,
Contrat_location.vehicule、Annonce.vehicule这些列在数据量上来后会成为连接和删除的瓶颈。 - 车况检查加唯一约束。
UNIQUE (contrat, type)能从结构上保证“取车、还车各一条”,比应用层判断更稳。
小结
这个项目最重要的不是 18 张表,而是三次判断:
- 继承用母表 + 子表,还是用一张宽表;
- 规则写进
CHECK、触发器,还是留给应用; - 什么时候该拆表,什么时候该承认“这是一段文档,不是一行关系”。
NoSQL 版本把保险、发票、评论改成 JSON 列,正好是第三问的另一个答案;下一篇单独讲。
源码、建表脚本与报告:https://github.com/yicongl1/Vehicle-Share-Database
相关技能
- SQL
- Python