我第一次独立负责数据库设计时本来以为把业务需求问清楚再对着几页Excel摆一摆就能直接开建表。结果被现实狠狠教育了一顿业务方说的“一个客户有多个联系方式”“一条借阅记录只对应一个读者”听起来全是常识可真到建表的时候到底该建几张表、外键放谁身上、哪些字段存了也白存全变成了一道道选择题。那时回头翻教材第七章才反应过来数据库设计不是从建表开始的而是从一张E-R图开始的。这篇笔记不打算复述教材原文而是把第七章里关于数据库设计和E-R模型的核心概念、边界判断、转换规则、以及我在实际项目中踩过的坑串起来讲一遍。适合正在学数据库系统的学生也适合刚接触逻辑建模、被各种“实体、属性、联系”绕晕的开发者。看完你至少能搞明白一件事为什么那么多看起来差不多的建模方案有的好用有的上线后天天出Bug。1. 先搞清楚E-R模型在整个数据库设计流程中的位置1.1 需求收集到物理设计概念模型卡在哪一环数据库设计通常被分成五个阶段需求收集与分析、概念设计、逻辑设计、物理设计、实施与测试运行。E-R模型就是概念设计阶段的代表成果它的输入是需求分析得出的业务规则输出是一张和具体数据库产品无关的“数据蓝图”。很多新手会跳过概念设计直接拿需求去建表理由也很充分反正MySQL和Oracle最后都要建表中间画一张图不是多此一举吗这个想法的隐患在于需求在一开始通常是不完整的直接进入表结构设计你会把大量精力耗在纠结“这个字段该放哪张表”“外键怎么关联”上而业务规则本身还没被确认。等业务人员说“不对一个订单其实可以拆成多个子订单”你面对的可能已经是十几张被改得面目全非的表了。E-R模型的价值就在这里它逼着你先用业务人员能看懂的方式把“有哪些对象、对象之间什么关系、每个对象带什么信息”谈清楚。这就像盖房子先画总体布局图再画施工图。总体布局图上不需要标每根钢筋多粗但房间的格局、门开在哪、走廊怎么连通这些必须定下来。E-R图就是数据世界的总体布局图它不关心主键用int还是bigint不关心索引怎么建只关心数据长什么样、数据之间怎么关联。1.2 为什么用E-R图而不是直接写表结构我后来带人做项目时坚持要他们先画E-R图理由有三个。第一个理由是沟通你给业务方看一张满是SQL建表语句的文档人家第一反应是头晕你给一张矩形、菱形、椭圆构成的图说“这是客户这是订单客户和订单之间是一对多”业务方马上就能参与讨论。E-R图是一种业务语言不是技术语言。第二个理由是稳定。概念模型相比表结构对需求变化的承受能力强得多。需求从“一个读者只能借一本书”变成“一个读者能借多本书”在E-R图上就是把联系旁边的“1”改成“N”一分钟搞定在已建好的表结构上改可能要迁移数据、重构外键。概念层的小改动到物理层可能就是大手术。第三个理由有点反直觉但它最实用画E-R图的过程本身就是在做冗余检查。比如你想把“一个员工有两个手机号”直接建成“员工表里放phone1、phone2两个字段”在E-R图里你会看到一个多值属性很别扭于是你会很自然地拆出一张“员工电话表”。这种“画着画着就觉得不对劲”的感觉恰恰是E-R模型帮你提前暴露了不合理设计。2. E-R模型的核心构件与最容易被绕晕的边界问题2.1 实体集、属性、联系集的准确含义E-R模型只有三个基本构件实体集、属性、联系集。实体集用矩形表示是一类具有相同性质的对象的集合比如“读者”是所有读者这类人构成的集合而不是某一条具体记录。属性用椭圆表示描述实体集的某个特征比如读者的姓名、学号、借书证号。联系集用菱形表示描述多个实体集之间的关联比如“借阅”就是读者和图书之间的一个联系集。这里最容易犯的错误是把实体集当成单条记录。画图时默认每个矩形代表一类对象而不是一个对象这个观念影响深远读者是一个实体集图书是另一个实体集借阅记录是联系集它本身带一个属性“借书日期”。如果你满脑子都是“这一行数据、那一行数据”你会不自觉地把字段和外键混进图里失去概念建模的意义。另一个常被忽略的点是键。实体集需要主码来唯一标识画E-R图时在主码属性下加下划线。联系集没有主码它的身份靠参与它的实体集主码联合起来确定。很多人在E-R图上不标主码标注的理由是“图还没到那么细的程度”但主码不定下来后面的转换就无从谈起。我的习惯是画实体集的同时就把主码候选列出来不用画得太详细但心里必须有数。2.2 多值属性、派生属性、复合属性这些特殊情况怎么处理第七章花了不小篇幅讲特殊属性这部分看起来简单实际建模时却不那么容易。复合属性是可以再拆分的属性比如通信地址可以拆成省份、城市、街道、邮编。处理方式是如果业务经常按单个子项查询或更新就把每个子项单独作为字段如果业务总是把整个地址当作一个整体读写不拆也行。这件事没有绝对答案取决于查询习惯而不是数据库理论。多值属性是真正的重点。假设一个员工有多个电话号码如果你在员工表里放phone1、phone2两个字段需求变成“最多能存5个号码”时就要改表结构如果直接用一个字段存“138...139...”后续想查某个号码属于谁只能写恶心的模糊匹配。在E-R图里多值属性会用双椭圆表示转换规则很明确单独建一张表表的外键指向原实体主键用“原实体主键该属性值”。员工电话表的主键可以是(员工ID, 手机号)这样天然不会有重复号码。派生属性更是个陷阱。年龄可以由出生日期推算E-R图里用虚线椭圆表示。我的建议是凡是由其他数据计算而来的属性一律不存。年龄年年变存下来了就得年年更新一旦忘了更新统计报表就出错。存出生日期想算什么年龄现算永远一致。同样的道理适用于“订单总金额”和“订单明细金额”总金额可以从明细汇总出来但很多系统为了查询性能会存冗余的总金额这是有意为之的冗余需要配合同步逻辑属于另一码事了。新手上路先记住“可推导的就不存”这个原则。2.3 属性和实体、联系和实体之间的边界之争我见过很多人卡在同一个地方某个信息到底算属性还是应该独立成一个实体比如员工属于某个部门部门有一个名字那“部门”是员工的属性还是单独的实体判断标准其实很简单看这个信息本身有没有属性、有没有独立生命。如果部门只有“部门名”这一个孤零零的值那它就是员工的属性没必要建实体如果部门还带办公地点、部门预算、负责人那它明显有自己的属性和生命周期必须单独建实体。我把这个判断法叫“挂灯笼原则”一个东西下面还能挂其他信息它就是实体一个东西下面什么也挂不住只是孤零零一个值它就是属性。联系和实体之间的边界同样模糊。比如“学生选修课程”如果只关心“谁选了哪门课”它就是一个联系如果还想记录选课时间、平时成绩、期末成绩这个“选修”就不再只是联系而应该升级成一个“选课记录”实体。我在实际项目里处理过很多类似情况一开始把“上下架操作记录”当作商品和操作员之间的联系后来要查“某商品最近30天被操作过几次、每次前后价格多少”联系根本承载不了这么多信息只能回头重构成实体。建议是凡是要为“联系”本身记录多个附加信息或者要对“联系”本身进行多对多关联就别犹豫把它升级为实体。3. 映射基数决定数据规则也决定外键往哪里放3.1 一对一、一对多、多对多的建模方式映射基数是E-R模型里最有实操价值的部分。它描述的是一个实体集的成员可以和另一个实体集的多少个成员发生关联。经典三件套一对一、一对多、多对多。用业务语言说就是一个经理管几个下属、一个班级包含多少学生、一个学生能选多少门课。教学里经常用表格总结这三种情况我把建表策略也一并整理出来映射基数E-R图标注关系模式转换策略外键位置一对一两侧都标1任选一侧加另一侧的主键作为外键加UNIQUE约束看哪一侧更稳定、访问更频繁一对多一侧标1多侧标N在“多”的那一侧加“一”的那一侧的主键作为外键永远放在“多”的一侧多对多两侧都标N新建中间表中间表包含双方主键作为外键不存在外键放谁身上的问题为什么一对多时外键必须放在“多”的那一侧而不是放在“一”的那一侧这得回到外键的工作方式上。外键是存在某一行里、用来指向另一张表的一个值。一个班级有五十个学生如果把班级的主键放在学生这一侧每个学生身上带一个“所属班级编号”五十行数据里自然能看到同一个班级编号出现五十次完全合理。反过来如果把学生主键放在班级这一侧一个班级那行数据里要放五十个学生编号一个字段放不下除非你违法范式用逗号分隔成一个字符串那就别想好好查数据了。多对多需要中间表这一点绝大多数人都知道但有一个细节常被忽略中间表的主键到底用联合主键还是额外加一个自增主键。联合主键(学生ID, 课程ID)能防止同一个学生重复选同一门课直接在数据库层面锁死业务规则额外加一个自增主键则灵活允许同一个学生重复选课并分配不同批次。两种做法没有高下之分但要结合业务来选。我的经验是先想想“重复关联数据允不允许存在”允许就用代理主键不允许就用联合主键别稀里糊涂选了唯一约束外的方案。3.2 三元联系和递归联系的建模技巧二元联系搞明白之后三元联系容易让新手措手不及。三元联系是三个实体集同时参与的关联比如“供应商向某个仓库供应某种产品”涉及供应商、仓库、产品三个实体集。转换策略和二元多对多类似建一张联系表把三个实体的主键都放进去作为外键联系属性也放进去。三元联系的主键设置比二元更讲究。一种可靠的做法是如果某个实体在联系中扮演“多”的角色其他两个实体可以唯一确定它那主键就是这两个外键的组合更多时候为了省心很多人直接用一个自增代理主键再给三个外键加唯一约束防止完全相同的组合重复出现。我不建议不分场合地给所有联系表都加代理主键它会让数据模型丧失“重复即非法”的语义约束。先分析基数和唯一性再决定主键是一种更严谨的做法。递归联系也经常被弄晕它的本质是同一个实体集内部发生的联系。最经典的例子是员工和经理经理本身也是员工所以“管理”联系的两个参与者都是员工实体集。在E-R图上需要用角色名区分比如员工实体集旁边引出两条线进入“管理”联系集一条标注“员工”一条标注“经理”。转换到表结构时就是在员工表里加一个“经理ID”字段外键指向员工表自身的员工ID。这里有个很实用的细节顶级员工的经理ID为空不要觉得空值可怕这是表达“他没有上级”的正常方式。为了迁就这个空值而给经理单独建一张表完全是过度设计。3.3 弱实体与存在依赖什么时候需要单独建模弱实体是第七章里概念上比较绕、但现实场景非常常见的一类实体。弱实体有两个特征第一它不能仅凭自己的属性唯一标识第二它的存在依赖于另一个强实体。E-R图中弱实体用双矩形表示它和强实体之间的识别联系用双菱形表示它的部分码用虚线下划线表示。举几个真实场景。酒店的房间房间编号如果没有酒店ID作前缀很容易在两个酒店里重复所以房间号是“酒店ID房间号”拼起来的房间不能离开酒店独立存在。订单明细明细序号在同一个订单内从1开始编号离开了订单明细就没有意义。银行卡账户里的交易流水也类似流水号常常只在某账户维度下唯一。这些实体在转换时主键是“强实体主键弱实体部分码”外键指向强实体。处理弱实体最容易犯的错是把它的部分码当成完整主键。订单明细表如果你只用一个明细自增ID做主键看起来没问题但业务语义上“每张订单从1开始编号明细”这件事就丢了。而且一旦强实体被删除弱实体的数据就成了孤儿数据。E-R建模阶段看清谁是弱实体转换时才能把存在依赖和数据生命周期一并考虑进去删除策略也好设计。4. 从E-R图到关系模式转换的完整决策过程4.1 一套可直接套用的转换步骤从E-R图到关系模式是有固定套路可循的。我习惯按下面这个顺序来每走一步都相当于对E-R图做一次“翻译”不容易漏东西。第一步每个强实体集转成一张表实体属性变成表的列实体主码变成表的主键。第二步弱实体集单独建表主键由强实体主键加上弱实体部分码组成同时把强实体主键作为外键引入。第三步多值属性不能留在原表里单独建一张表原实体主键作为外键和外加的多值属性共同组成主键。第四步复合属性按使用习惯决定拆成多列还是保持原样。第五步一对一联系任意选一侧加外键别忘了加UNIQUE约束。第六步一对多联系在“多”侧加外键。第七步多对多联系建独立联系表双方主键作为外键加上联系本身的属性。这套流程看起来琐碎但它把“画图”和“建表”之间所有常见的坑都堵住了。每次我帮别人Review表结构发现的大部分问题都能对应到上面某一条多值属性被塞进原表了一对多联系外键放反了多对多联系没有中间表。对照这个清单逐项筛查比凭经验肉眼找要可靠得多。4.2 属性合并、实体拆分的取舍原则转换过程中最需要判断力的是“属性到底合在实体表里还是拆出去”。数据库设计永远在性能和规范化之间取平衡第七章强调的是模型合理性但落地时离不开取舍。我的经验法则有三个判断维度。第一看属性是否有独立更新频率。员工地址是一个复合属性如果业务经常只改“街道”而其他不变拆成多列更合理如果整个地址总是整体覆盖拆不拆影响不大。第二看是否会产生大量空值。一个可选属性如果大部分员工都没填写单独开一张表存有值的部分会减少主表空间的浪费逻辑上也更清晰。第三看是否会被独立引用。属性如果经常被其他表作为引用对象它就该升级成实体。实体拆分也是同样的逻辑。把“联系人”从“客户”里拆出来是因为联系人自身有姓名、职位、电话、微信一堆属性还有可能被多个客户共享。不要为了“看起来规范”就把所有名词都拆实体一个只有名称、没有其他属性的状态对象“启用/停用”硬拆成一张状态表就是典型的过度设计。先按“挂灯笼原则”判断它是不是实体再做决定。4.3 一个完整的转换实例从需求到表结构纸上谈兵到这里用个完整例子串一遍。假设要给某培训机构做学员管理系统需求大概是学员属于某个班级讲师讲授课程课程安排在班级上课学员选课选课后有成绩完成课程的学生会拿到证书。先画E-R图的骨架。实体有学员、班级、讲师、课程、上课安排、选课记录、证书。联系关系如下班级到学员是一对多一个班有多个学员一个学员属于一个班讲师到课程是多对多一个讲师能讲多门课一门课可能由多位讲师讲班级和课程之间是多对多一个班要上多门课一门课也要排进多个班学员和课程之间是多对多一个学员能选多门课一门课能有多人选证书依赖学员和课程记录学员完成了哪门课。按转换步骤实体先转表学员表、班级表、讲师表、课程表。讲师和课程的多对多转讲师授课表班级和课程的多对多转课程安排表学员和课程的多对多转选课表。学员电话是多值属性单独建学员电话表。证书这个弱实体主键用(证书编号)还是(学员ID课程ID)取决于业务如果证书编号有全局唯一规则它就是强实体如果证书编号只在“学员课程”维度下唯一就按弱实体方式建表。为演示弱实体这里假设证书编号在某个学员的某门课程范围内唯一表结构示意如下CREATE TABLE class ( class_id CHAR(10) PRIMARY KEY, class_name VARCHAR(50) NOT NULL, start_date DATE ); CREATE TABLE student ( student_id CHAR(10) PRIMARY KEY, student_name VARCHAR(50) NOT NULL, birth_date DATE, class_id CHAR(10) REFERENCES class(class_id) ); CREATE TABLE student_phone ( student_id CHAR(10) REFERENCES student(student_id), phone VARCHAR(20), PRIMARY KEY (student_id, phone) ); CREATE TABLE course ( course_id CHAR(10) PRIMARY KEY, course_name VARCHAR(50) NOT NULL, credit DECIMAL(3,1) ); CREATE TABLE teacher ( teacher_id CHAR(10) PRIMARY KEY, teacher_name VARCHAR(50) NOT NULL, title VARCHAR(30) ); CREATE TABLE teacher_course ( teacher_id CHAR(10) REFERENCES teacher(teacher_id), course_id CHAR(10) REFERENCES course(course_id), PRIMARY KEY (teacher_id, course_id) ); CREATE TABLE course_schedule ( class_id CHAR(10) REFERENCES class(class_id), course_id CHAR(10) REFERENCES course(course_id), weekly_hours INT, PRIMARY KEY (class_id, course_id) ); CREATE TABLE enrollment ( student_id CHAR(10) REFERENCES student(student_id), course_id CHAR(10) REFERENCES course(course_id), enroll_date DATE, score DECIMAL(4,1), PRIMARY KEY (student_id, course_id) ); CREATE TABLE certificate ( student_id CHAR(10) REFERENCES student(student_id), course_id CHAR(10) REFERENCES course(course_id), cert_code VARCHAR(20), issue_date DATE, PRIMARY KEY (student_id, course_id) );这个例子里班级-学员的一对多通过学员表的class_id外键实现讲师-课程、班级-课程、学员-课程的多对多都建了中间表选课记录把选课时间、成绩放在联系表上证书表则体现了弱实体风格的主键设计。整套转换几乎没有二义性。5. E-R设计质量的判断标准与实践避坑清单5.1 冗余与不一致最隐蔽的两个设计坏味道E-R设计中的坏味道不像代码那样会在运行时报错它们藏在模型里等数据量变大后才集中爆发。最隐蔽的就是冗余和不一致。冗余的例子常见于把可推导的数据抄来抄去。比如某系统里学生表存了“学院名称”学院表也存“学院名称”表面看查询方便了但某天学院改名改一张表不够还要全量更新学生表里的冗余字段漏一条统计就出现两个名字。E-R建模时看到一个属性出现在两个实体集里就要警惕这个属性是真冗余还是两个语义完全不同的属性真冗余若没有同步方案支撑迟早会出问题。不一致比冗余更难排查。不同表里含义相近的字段一个叫“状态”一个叫“status”一个取值“1/0”一个取值“Y/N”数据对不上的时候非常痛苦。E-R图阶段强制你统一属性命名和取值口径跨表对比一遍能提前发现很多语义冲突。我见过最典型的案例是“订单金额”和“实付金额”混用看似一字之差业务上差着折扣和运费不建模直接建表的人很容易在需求评审时漏掉这种细节。5.2 过度建模与命名混乱新手爱犯、老手也翻车和冗余相反过度建模是把简单问题复杂化。我见过有人把订单的“状态”字段单独拆成一张状态表理由是为了扩展性结果每查一次订单状态都要连表。还有人把一对一关联硬建中间表一个用户对应一份身份信息也建了user和identity_info两张表加外键除了让查询多一次JOIN以外毫无收益。高质量E-R设计的标准之一是“足够用且不过度”能用属性表达的就不建实体能用一对多表达的就不建中间表。命名混乱问题更隐蔽。实体名、属性名、联系名没有统一规范有的人用中文有的人用拼音缩写有的人英文大小写随机。E-R图是给人看的图命名混乱直接导致沟通成本飙升。我的建议是实体名一律用名词联系名一律用动词短语属性名全库统一风格主键就叫“实体名ID”外键直接用被引用实体名加ID。这套规则不需要多高级但能让你三个月后回来看图依然一眼看懂。5.3 我的检查清单与验收流程画完E-R图后我习惯过一遍检查清单它能挡掉绝大多数低级问题检查项自查标准主键每个实体都有明确主键吗弱实体是否带了强实体的主键属性原子性有没有存多个值到一个字段里的情况多值属性是否单独建表外键位置一对多联系的外键是否都放在“多”的一侧多对多每个多对多联系是否都有中间表中间表是否有合理主键映射基数联系旁的基数标注是否符合业务规则业务方确认过吗派生属性是否还有可从其他字段算出来、本不该存储的数据命名实体、属性、联系命名是否一致、清晰空值语义允许为空的字段空的含义是否明确没有值/尚未发生/不适用过完清单后还有一个更重要的验收动作用模拟数据“反向验图”。随便想几条业务查询比如“找出选了数据库课程但没有成绩的学员”“统计每个班级本学期的开课门数”再看E-R图能不能轻松回答。回答不了说明图里漏了实体或联系回答得很别扭说明建模思路有问题别急着往物理设计走。这一步成本极低收益却很大。6. 工具推荐与实际项目推进建议6.1 常用的画图工具怎么选工欲善其事必先利其器。画E-R图的选择很多关键是搞清楚你的使用场景。如果只是自己学习、快速梳理思路用在线绘图工具或者老牌的桌面绘图软件都行这类工具画矩形、菱形、椭圆很方便重点是轻量、易上手。如果要把E-R图交付给团队评审可以选能导出高清晰度图片、支持多人协作的工具。如果需要直接从模型生成建表脚本那就值得用专业的数据建模工具它能双向同步图与表结构改图自动更新SQL。我个人的习惯是概念设计阶段用轻量绘图工具因为它不逼着我关心数据类型和物理细节画错了随手改逻辑到物理设计阶段再换专业建模工具把图里的实体逐步落成具体表。没必要从一开始就追求“一键建表”的工具那会把你过早拉回字段、类型的细节里失去概念建模的意义。6.2 从一个需求到可落地表的完整工作流一个靠谱的建模工作流我实践下来大概是这样的先收集需求把所有业务人员提到的名词、动词、数量关系都记录下来形成业务名词清单然后从中圈出实体——凡是“有属性、有生命周期”的名词列成候选实体接着确定联系和基数关系把E-R图画出来画完先自己过一遍检查清单再找业务方评审重点确认每个联系的映射基数评审通过后再做关系模式转换生成SQL建表脚本最后回头反向验证确保所有业务问题都能用这套表结构回答。这里有个容易忽略的环节评审会必须请业务人员参加。开发人员容易只关心技术实现业务人员才是基数规则的权威。一个“讲师可以同时讲授几门课”的问题开发猜的答案和业务实际的答案往往不一样这种分歧越早暴露越好。E-R图就是那个让分歧无处遁形的工具。另外还想多说一句E-R图画完不要丢它应该随需求变化持续更新。很多团队把E-R图当成开工前的文档建完表就再也没人看半年后新增了七八张表图和数据模型彻底对不上。我的做法是把E-R图作为需求和开发之间的“翻译层”需求变更先改图确认了再落SQL。虽然每次改图都多点工作量但省掉的是后期改表结构和数据迁移的巨大返工成本。
我第一次独立负责数据库设计时本来以为把业务需求问清楚再对着几页Excel摆一摆就能直接开建表。结果被现实狠狠教育了一顿业务方说的“一个客户有多个联系方式”“一条借阅记录只对应一个读者”听起来全是常识可真到建表的时候到底该建几张表、外键放谁身上、哪些字段存了也白存全变成了一道道选择题。那时回头翻教材第七章才反应过来数据库设计不是从建表开始的而是从一张E-R图开始的。这篇笔记不打算复述教材原文而是把第七章里关于数据库设计和E-R模型的核心概念、边界判断、转换规则、以及我在实际项目中踩过的坑串起来讲一遍。适合正在学数据库系统的学生也适合刚接触逻辑建模、被各种“实体、属性、联系”绕晕的开发者。看完你至少能搞明白一件事为什么那么多看起来差不多的建模方案有的好用有的上线后天天出Bug。1. 先搞清楚E-R模型在整个数据库设计流程中的位置1.1 需求收集到物理设计概念模型卡在哪一环数据库设计通常被分成五个阶段需求收集与分析、概念设计、逻辑设计、物理设计、实施与测试运行。E-R模型就是概念设计阶段的代表成果它的输入是需求分析得出的业务规则输出是一张和具体数据库产品无关的“数据蓝图”。很多新手会跳过概念设计直接拿需求去建表理由也很充分反正MySQL和Oracle最后都要建表中间画一张图不是多此一举吗这个想法的隐患在于需求在一开始通常是不完整的直接进入表结构设计你会把大量精力耗在纠结“这个字段该放哪张表”“外键怎么关联”上而业务规则本身还没被确认。等业务人员说“不对一个订单其实可以拆成多个子订单”你面对的可能已经是十几张被改得面目全非的表了。E-R模型的价值就在这里它逼着你先用业务人员能看懂的方式把“有哪些对象、对象之间什么关系、每个对象带什么信息”谈清楚。这就像盖房子先画总体布局图再画施工图。总体布局图上不需要标每根钢筋多粗但房间的格局、门开在哪、走廊怎么连通这些必须定下来。E-R图就是数据世界的总体布局图它不关心主键用int还是bigint不关心索引怎么建只关心数据长什么样、数据之间怎么关联。1.2 为什么用E-R图而不是直接写表结构我后来带人做项目时坚持要他们先画E-R图理由有三个。第一个理由是沟通你给业务方看一张满是SQL建表语句的文档人家第一反应是头晕你给一张矩形、菱形、椭圆构成的图说“这是客户这是订单客户和订单之间是一对多”业务方马上就能参与讨论。E-R图是一种业务语言不是技术语言。第二个理由是稳定。概念模型相比表结构对需求变化的承受能力强得多。需求从“一个读者只能借一本书”变成“一个读者能借多本书”在E-R图上就是把联系旁边的“1”改成“N”一分钟搞定在已建好的表结构上改可能要迁移数据、重构外键。概念层的小改动到物理层可能就是大手术。第三个理由有点反直觉但它最实用画E-R图的过程本身就是在做冗余检查。比如你想把“一个员工有两个手机号”直接建成“员工表里放phone1、phone2两个字段”在E-R图里你会看到一个多值属性很别扭于是你会很自然地拆出一张“员工电话表”。这种“画着画着就觉得不对劲”的感觉恰恰是E-R模型帮你提前暴露了不合理设计。2. E-R模型的核心构件与最容易被绕晕的边界问题2.1 实体集、属性、联系集的准确含义E-R模型只有三个基本构件实体集、属性、联系集。实体集用矩形表示是一类具有相同性质的对象的集合比如“读者”是所有读者这类人构成的集合而不是某一条具体记录。属性用椭圆表示描述实体集的某个特征比如读者的姓名、学号、借书证号。联系集用菱形表示描述多个实体集之间的关联比如“借阅”就是读者和图书之间的一个联系集。这里最容易犯的错误是把实体集当成单条记录。画图时默认每个矩形代表一类对象而不是一个对象这个观念影响深远读者是一个实体集图书是另一个实体集借阅记录是联系集它本身带一个属性“借书日期”。如果你满脑子都是“这一行数据、那一行数据”你会不自觉地把字段和外键混进图里失去概念建模的意义。另一个常被忽略的点是键。实体集需要主码来唯一标识画E-R图时在主码属性下加下划线。联系集没有主码它的身份靠参与它的实体集主码联合起来确定。很多人在E-R图上不标主码标注的理由是“图还没到那么细的程度”但主码不定下来后面的转换就无从谈起。我的习惯是画实体集的同时就把主码候选列出来不用画得太详细但心里必须有数。2.2 多值属性、派生属性、复合属性这些特殊情况怎么处理第七章花了不小篇幅讲特殊属性这部分看起来简单实际建模时却不那么容易。复合属性是可以再拆分的属性比如通信地址可以拆成省份、城市、街道、邮编。处理方式是如果业务经常按单个子项查询或更新就把每个子项单独作为字段如果业务总是把整个地址当作一个整体读写不拆也行。这件事没有绝对答案取决于查询习惯而不是数据库理论。多值属性是真正的重点。假设一个员工有多个电话号码如果你在员工表里放phone1、phone2两个字段需求变成“最多能存5个号码”时就要改表结构如果直接用一个字段存“138...139...”后续想查某个号码属于谁只能写恶心的模糊匹配。在E-R图里多值属性会用双椭圆表示转换规则很明确单独建一张表表的外键指向原实体主键用“原实体主键该属性值”。员工电话表的主键可以是(员工ID, 手机号)这样天然不会有重复号码。派生属性更是个陷阱。年龄可以由出生日期推算E-R图里用虚线椭圆表示。我的建议是凡是由其他数据计算而来的属性一律不存。年龄年年变存下来了就得年年更新一旦忘了更新统计报表就出错。存出生日期想算什么年龄现算永远一致。同样的道理适用于“订单总金额”和“订单明细金额”总金额可以从明细汇总出来但很多系统为了查询性能会存冗余的总金额这是有意为之的冗余需要配合同步逻辑属于另一码事了。新手上路先记住“可推导的就不存”这个原则。2.3 属性和实体、联系和实体之间的边界之争我见过很多人卡在同一个地方某个信息到底算属性还是应该独立成一个实体比如员工属于某个部门部门有一个名字那“部门”是员工的属性还是单独的实体判断标准其实很简单看这个信息本身有没有属性、有没有独立生命。如果部门只有“部门名”这一个孤零零的值那它就是员工的属性没必要建实体如果部门还带办公地点、部门预算、负责人那它明显有自己的属性和生命周期必须单独建实体。我把这个判断法叫“挂灯笼原则”一个东西下面还能挂其他信息它就是实体一个东西下面什么也挂不住只是孤零零一个值它就是属性。联系和实体之间的边界同样模糊。比如“学生选修课程”如果只关心“谁选了哪门课”它就是一个联系如果还想记录选课时间、平时成绩、期末成绩这个“选修”就不再只是联系而应该升级成一个“选课记录”实体。我在实际项目里处理过很多类似情况一开始把“上下架操作记录”当作商品和操作员之间的联系后来要查“某商品最近30天被操作过几次、每次前后价格多少”联系根本承载不了这么多信息只能回头重构成实体。建议是凡是要为“联系”本身记录多个附加信息或者要对“联系”本身进行多对多关联就别犹豫把它升级为实体。3. 映射基数决定数据规则也决定外键往哪里放3.1 一对一、一对多、多对多的建模方式映射基数是E-R模型里最有实操价值的部分。它描述的是一个实体集的成员可以和另一个实体集的多少个成员发生关联。经典三件套一对一、一对多、多对多。用业务语言说就是一个经理管几个下属、一个班级包含多少学生、一个学生能选多少门课。教学里经常用表格总结这三种情况我把建表策略也一并整理出来映射基数E-R图标注关系模式转换策略外键位置一对一两侧都标1任选一侧加另一侧的主键作为外键加UNIQUE约束看哪一侧更稳定、访问更频繁一对多一侧标1多侧标N在“多”的那一侧加“一”的那一侧的主键作为外键永远放在“多”的一侧多对多两侧都标N新建中间表中间表包含双方主键作为外键不存在外键放谁身上的问题为什么一对多时外键必须放在“多”的那一侧而不是放在“一”的那一侧这得回到外键的工作方式上。外键是存在某一行里、用来指向另一张表的一个值。一个班级有五十个学生如果把班级的主键放在学生这一侧每个学生身上带一个“所属班级编号”五十行数据里自然能看到同一个班级编号出现五十次完全合理。反过来如果把学生主键放在班级这一侧一个班级那行数据里要放五十个学生编号一个字段放不下除非你违法范式用逗号分隔成一个字符串那就别想好好查数据了。多对多需要中间表这一点绝大多数人都知道但有一个细节常被忽略中间表的主键到底用联合主键还是额外加一个自增主键。联合主键(学生ID, 课程ID)能防止同一个学生重复选同一门课直接在数据库层面锁死业务规则额外加一个自增主键则灵活允许同一个学生重复选课并分配不同批次。两种做法没有高下之分但要结合业务来选。我的经验是先想想“重复关联数据允不允许存在”允许就用代理主键不允许就用联合主键别稀里糊涂选了唯一约束外的方案。3.2 三元联系和递归联系的建模技巧二元联系搞明白之后三元联系容易让新手措手不及。三元联系是三个实体集同时参与的关联比如“供应商向某个仓库供应某种产品”涉及供应商、仓库、产品三个实体集。转换策略和二元多对多类似建一张联系表把三个实体的主键都放进去作为外键联系属性也放进去。三元联系的主键设置比二元更讲究。一种可靠的做法是如果某个实体在联系中扮演“多”的角色其他两个实体可以唯一确定它那主键就是这两个外键的组合更多时候为了省心很多人直接用一个自增代理主键再给三个外键加唯一约束防止完全相同的组合重复出现。我不建议不分场合地给所有联系表都加代理主键它会让数据模型丧失“重复即非法”的语义约束。先分析基数和唯一性再决定主键是一种更严谨的做法。递归联系也经常被弄晕它的本质是同一个实体集内部发生的联系。最经典的例子是员工和经理经理本身也是员工所以“管理”联系的两个参与者都是员工实体集。在E-R图上需要用角色名区分比如员工实体集旁边引出两条线进入“管理”联系集一条标注“员工”一条标注“经理”。转换到表结构时就是在员工表里加一个“经理ID”字段外键指向员工表自身的员工ID。这里有个很实用的细节顶级员工的经理ID为空不要觉得空值可怕这是表达“他没有上级”的正常方式。为了迁就这个空值而给经理单独建一张表完全是过度设计。3.3 弱实体与存在依赖什么时候需要单独建模弱实体是第七章里概念上比较绕、但现实场景非常常见的一类实体。弱实体有两个特征第一它不能仅凭自己的属性唯一标识第二它的存在依赖于另一个强实体。E-R图中弱实体用双矩形表示它和强实体之间的识别联系用双菱形表示它的部分码用虚线下划线表示。举几个真实场景。酒店的房间房间编号如果没有酒店ID作前缀很容易在两个酒店里重复所以房间号是“酒店ID房间号”拼起来的房间不能离开酒店独立存在。订单明细明细序号在同一个订单内从1开始编号离开了订单明细就没有意义。银行卡账户里的交易流水也类似流水号常常只在某账户维度下唯一。这些实体在转换时主键是“强实体主键弱实体部分码”外键指向强实体。处理弱实体最容易犯的错是把它的部分码当成完整主键。订单明细表如果你只用一个明细自增ID做主键看起来没问题但业务语义上“每张订单从1开始编号明细”这件事就丢了。而且一旦强实体被删除弱实体的数据就成了孤儿数据。E-R建模阶段看清谁是弱实体转换时才能把存在依赖和数据生命周期一并考虑进去删除策略也好设计。4. 从E-R图到关系模式转换的完整决策过程4.1 一套可直接套用的转换步骤从E-R图到关系模式是有固定套路可循的。我习惯按下面这个顺序来每走一步都相当于对E-R图做一次“翻译”不容易漏东西。第一步每个强实体集转成一张表实体属性变成表的列实体主码变成表的主键。第二步弱实体集单独建表主键由强实体主键加上弱实体部分码组成同时把强实体主键作为外键引入。第三步多值属性不能留在原表里单独建一张表原实体主键作为外键和外加的多值属性共同组成主键。第四步复合属性按使用习惯决定拆成多列还是保持原样。第五步一对一联系任意选一侧加外键别忘了加UNIQUE约束。第六步一对多联系在“多”侧加外键。第七步多对多联系建独立联系表双方主键作为外键加上联系本身的属性。这套流程看起来琐碎但它把“画图”和“建表”之间所有常见的坑都堵住了。每次我帮别人Review表结构发现的大部分问题都能对应到上面某一条多值属性被塞进原表了一对多联系外键放反了多对多联系没有中间表。对照这个清单逐项筛查比凭经验肉眼找要可靠得多。4.2 属性合并、实体拆分的取舍原则转换过程中最需要判断力的是“属性到底合在实体表里还是拆出去”。数据库设计永远在性能和规范化之间取平衡第七章强调的是模型合理性但落地时离不开取舍。我的经验法则有三个判断维度。第一看属性是否有独立更新频率。员工地址是一个复合属性如果业务经常只改“街道”而其他不变拆成多列更合理如果整个地址总是整体覆盖拆不拆影响不大。第二看是否会产生大量空值。一个可选属性如果大部分员工都没填写单独开一张表存有值的部分会减少主表空间的浪费逻辑上也更清晰。第三看是否会被独立引用。属性如果经常被其他表作为引用对象它就该升级成实体。实体拆分也是同样的逻辑。把“联系人”从“客户”里拆出来是因为联系人自身有姓名、职位、电话、微信一堆属性还有可能被多个客户共享。不要为了“看起来规范”就把所有名词都拆实体一个只有名称、没有其他属性的状态对象“启用/停用”硬拆成一张状态表就是典型的过度设计。先按“挂灯笼原则”判断它是不是实体再做决定。4.3 一个完整的转换实例从需求到表结构纸上谈兵到这里用个完整例子串一遍。假设要给某培训机构做学员管理系统需求大概是学员属于某个班级讲师讲授课程课程安排在班级上课学员选课选课后有成绩完成课程的学生会拿到证书。先画E-R图的骨架。实体有学员、班级、讲师、课程、上课安排、选课记录、证书。联系关系如下班级到学员是一对多一个班有多个学员一个学员属于一个班讲师到课程是多对多一个讲师能讲多门课一门课可能由多位讲师讲班级和课程之间是多对多一个班要上多门课一门课也要排进多个班学员和课程之间是多对多一个学员能选多门课一门课能有多人选证书依赖学员和课程记录学员完成了哪门课。按转换步骤实体先转表学员表、班级表、讲师表、课程表。讲师和课程的多对多转讲师授课表班级和课程的多对多转课程安排表学员和课程的多对多转选课表。学员电话是多值属性单独建学员电话表。证书这个弱实体主键用(证书编号)还是(学员ID课程ID)取决于业务如果证书编号有全局唯一规则它就是强实体如果证书编号只在“学员课程”维度下唯一就按弱实体方式建表。为演示弱实体这里假设证书编号在某个学员的某门课程范围内唯一表结构示意如下CREATE TABLE class ( class_id CHAR(10) PRIMARY KEY, class_name VARCHAR(50) NOT NULL, start_date DATE ); CREATE TABLE student ( student_id CHAR(10) PRIMARY KEY, student_name VARCHAR(50) NOT NULL, birth_date DATE, class_id CHAR(10) REFERENCES class(class_id) ); CREATE TABLE student_phone ( student_id CHAR(10) REFERENCES student(student_id), phone VARCHAR(20), PRIMARY KEY (student_id, phone) ); CREATE TABLE course ( course_id CHAR(10) PRIMARY KEY, course_name VARCHAR(50) NOT NULL, credit DECIMAL(3,1) ); CREATE TABLE teacher ( teacher_id CHAR(10) PRIMARY KEY, teacher_name VARCHAR(50) NOT NULL, title VARCHAR(30) ); CREATE TABLE teacher_course ( teacher_id CHAR(10) REFERENCES teacher(teacher_id), course_id CHAR(10) REFERENCES course(course_id), PRIMARY KEY (teacher_id, course_id) ); CREATE TABLE course_schedule ( class_id CHAR(10) REFERENCES class(class_id), course_id CHAR(10) REFERENCES course(course_id), weekly_hours INT, PRIMARY KEY (class_id, course_id) ); CREATE TABLE enrollment ( student_id CHAR(10) REFERENCES student(student_id), course_id CHAR(10) REFERENCES course(course_id), enroll_date DATE, score DECIMAL(4,1), PRIMARY KEY (student_id, course_id) ); CREATE TABLE certificate ( student_id CHAR(10) REFERENCES student(student_id), course_id CHAR(10) REFERENCES course(course_id), cert_code VARCHAR(20), issue_date DATE, PRIMARY KEY (student_id, course_id) );这个例子里班级-学员的一对多通过学员表的class_id外键实现讲师-课程、班级-课程、学员-课程的多对多都建了中间表选课记录把选课时间、成绩放在联系表上证书表则体现了弱实体风格的主键设计。整套转换几乎没有二义性。5. E-R设计质量的判断标准与实践避坑清单5.1 冗余与不一致最隐蔽的两个设计坏味道E-R设计中的坏味道不像代码那样会在运行时报错它们藏在模型里等数据量变大后才集中爆发。最隐蔽的就是冗余和不一致。冗余的例子常见于把可推导的数据抄来抄去。比如某系统里学生表存了“学院名称”学院表也存“学院名称”表面看查询方便了但某天学院改名改一张表不够还要全量更新学生表里的冗余字段漏一条统计就出现两个名字。E-R建模时看到一个属性出现在两个实体集里就要警惕这个属性是真冗余还是两个语义完全不同的属性真冗余若没有同步方案支撑迟早会出问题。不一致比冗余更难排查。不同表里含义相近的字段一个叫“状态”一个叫“status”一个取值“1/0”一个取值“Y/N”数据对不上的时候非常痛苦。E-R图阶段强制你统一属性命名和取值口径跨表对比一遍能提前发现很多语义冲突。我见过最典型的案例是“订单金额”和“实付金额”混用看似一字之差业务上差着折扣和运费不建模直接建表的人很容易在需求评审时漏掉这种细节。5.2 过度建模与命名混乱新手爱犯、老手也翻车和冗余相反过度建模是把简单问题复杂化。我见过有人把订单的“状态”字段单独拆成一张状态表理由是为了扩展性结果每查一次订单状态都要连表。还有人把一对一关联硬建中间表一个用户对应一份身份信息也建了user和identity_info两张表加外键除了让查询多一次JOIN以外毫无收益。高质量E-R设计的标准之一是“足够用且不过度”能用属性表达的就不建实体能用一对多表达的就不建中间表。命名混乱问题更隐蔽。实体名、属性名、联系名没有统一规范有的人用中文有的人用拼音缩写有的人英文大小写随机。E-R图是给人看的图命名混乱直接导致沟通成本飙升。我的建议是实体名一律用名词联系名一律用动词短语属性名全库统一风格主键就叫“实体名ID”外键直接用被引用实体名加ID。这套规则不需要多高级但能让你三个月后回来看图依然一眼看懂。5.3 我的检查清单与验收流程画完E-R图后我习惯过一遍检查清单它能挡掉绝大多数低级问题检查项自查标准主键每个实体都有明确主键吗弱实体是否带了强实体的主键属性原子性有没有存多个值到一个字段里的情况多值属性是否单独建表外键位置一对多联系的外键是否都放在“多”的一侧多对多每个多对多联系是否都有中间表中间表是否有合理主键映射基数联系旁的基数标注是否符合业务规则业务方确认过吗派生属性是否还有可从其他字段算出来、本不该存储的数据命名实体、属性、联系命名是否一致、清晰空值语义允许为空的字段空的含义是否明确没有值/尚未发生/不适用过完清单后还有一个更重要的验收动作用模拟数据“反向验图”。随便想几条业务查询比如“找出选了数据库课程但没有成绩的学员”“统计每个班级本学期的开课门数”再看E-R图能不能轻松回答。回答不了说明图里漏了实体或联系回答得很别扭说明建模思路有问题别急着往物理设计走。这一步成本极低收益却很大。6. 工具推荐与实际项目推进建议6.1 常用的画图工具怎么选工欲善其事必先利其器。画E-R图的选择很多关键是搞清楚你的使用场景。如果只是自己学习、快速梳理思路用在线绘图工具或者老牌的桌面绘图软件都行这类工具画矩形、菱形、椭圆很方便重点是轻量、易上手。如果要把E-R图交付给团队评审可以选能导出高清晰度图片、支持多人协作的工具。如果需要直接从模型生成建表脚本那就值得用专业的数据建模工具它能双向同步图与表结构改图自动更新SQL。我个人的习惯是概念设计阶段用轻量绘图工具因为它不逼着我关心数据类型和物理细节画错了随手改逻辑到物理设计阶段再换专业建模工具把图里的实体逐步落成具体表。没必要从一开始就追求“一键建表”的工具那会把你过早拉回字段、类型的细节里失去概念建模的意义。6.2 从一个需求到可落地表的完整工作流一个靠谱的建模工作流我实践下来大概是这样的先收集需求把所有业务人员提到的名词、动词、数量关系都记录下来形成业务名词清单然后从中圈出实体——凡是“有属性、有生命周期”的名词列成候选实体接着确定联系和基数关系把E-R图画出来画完先自己过一遍检查清单再找业务方评审重点确认每个联系的映射基数评审通过后再做关系模式转换生成SQL建表脚本最后回头反向验证确保所有业务问题都能用这套表结构回答。这里有个容易忽略的环节评审会必须请业务人员参加。开发人员容易只关心技术实现业务人员才是基数规则的权威。一个“讲师可以同时讲授几门课”的问题开发猜的答案和业务实际的答案往往不一样这种分歧越早暴露越好。E-R图就是那个让分歧无处遁形的工具。另外还想多说一句E-R图画完不要丢它应该随需求变化持续更新。很多团队把E-R图当成开工前的文档建完表就再也没人看半年后新增了七八张表图和数据模型彻底对不上。我的做法是把E-R图作为需求和开发之间的“翻译层”需求变更先改图确认了再落SQL。虽然每次改图都多点工作量但省掉的是后期改表结构和数据迁移的巨大返工成本。
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
