写这篇文章之前我先说个背景。很多人在学《数据库系统概念》这本书时第七章“数据库设计和E-R模型”会读得特别煎熬——因为前面的章节都在讲SQL、关系代数、事务这些特别“硬”的东西到了第七章突然变成画图、识别实体、设计关系感觉像换了门课。但恰恰相反这一章才是从“会用数据库”跨到“会设计数据库”的分水岭。我自己当年学这章时也觉得E-R图就是画画矩形菱形后来真去开发业务系统被业务方追着改表结构改到怀疑人生才明白第七章那些看起来“软”的建模知识全是血泪教训换来的。这篇笔记不打算逐字复述教材而是把我认为这一章最能落地的部分拆开讲为什么需要E-R模型、实体联系怎么识别、E-R图怎么画、怎么转换成表以及那些教材里不会明说但实战里一定会遇到的坑。无论你是在校学生准备考试还是工作后想补一补数据库设计的地基这篇笔记都能当个参考。1. 数据库设计流程E-R模型站在哪一环1.1 从需求到建表先走完这四个阶段数据库设计不是打开客户端敲几条CREATE TABLE就完事的它是一个完整的流程。教科书一般把它分成四个阶段需求分析、概念设计、逻辑设计、物理设计。需求分析阶段做的事情是搞清楚业务方到底想要什么。比如做一个学生选课系统你得问清楚系统要管理哪些信息学生能选几门课成绩怎么记录一个老师能教几门课这些听起来很基础但很多项目挂就挂在需求没问清楚就急着建表。我见过有人拿到“做个图书管理系统”一句话需求第二天就把表建好了结果后面发现还要管预约、还要管逾期罚款、还要区分馆藏地和库存量表结构推倒重来光是改外键就改了三天。概念设计阶段的产物就是我们这篇笔记的主角E-R模型。这个阶段要做的是把需求抽象成实体、属性、联系画出一张不依赖任何具体数据库产品的概念图。这一步的核心原则是“别想技术实现”不要考虑MySQL还是PostgreSQL不要考虑主键用自增ID还是UUID更不要考虑索引怎么建。你要做的就是纯粹从业务视角出发把“有哪些东西、这些东西之间什么关系”表达清楚。逻辑设计阶段把E-R模型转换成关系模式也就是我们常说的表结构。物理设计阶段再考虑索引、存储引擎、分区、性能优化这些东西。1.2 为什么概念设计阶段必须“不设防”很多初学者有个毛病画E-R图的时候脑子里已经在想表结构了。比如一看“学生选课”这个联系直接就想着“是不是该建一张student_course表里面放student_id和course_id”这其实是把概念设计和逻辑设计混在一起了。概念设计阶段必须强制自己不设防原因有两点。第一E-R图的表达力比表结构强得多。你在表里看到两个外键字段未必能一眼看出这是一对多还是多对多但E-R图里菱形两边的基数标识写得很清楚。画E-R图的过程本质上是逼你把业务关系想透而直接建表容易跳过这一步。第二概念模型可以脱离具体技术栈。同一个E-R模型既可以转换成关系型数据库的表也能为后续做文档数据库、图数据库的设计提供参考。你不需要在乎Oracle和MySQL的方言差异只需要把业务抽象做对。这里插一句我在实际项目中画E-R图时经常会用一堆便利贴贴在白板上每个实体写一张然后用胶带表示联系。这种方式的好处是随时可以撕掉重贴。一开始我直接用绘图工具画结果改了六版光调整连线就花了不少时间。我的建议是先用纸和便利贴把关系理清楚再用工具画正式的E-R图。2. E-R模型的核心三要素实体、属性、联系2.1 实体与实体集先分清“东西”和“东西的集合”E-R模型的基础是实体Entity指的是客观存在、可以相互区分的事物。比如一个具体的学生张三、一门具体的课程“数据库导论”都是实体。而具有相同属性特征的一类实体的集合叫实体集Entity Set比如全体学生、全部课程。画E-R图的时候实体用矩形表示实体集名称写在矩形里面。这里有个容易混淆的点实体集在逻辑设计阶段会变成一张表但实体集不等于表。实体集关注的是“有哪些类型的事物”而表还包含了列、主键、外键这些实现细节。判断一个事物是不是实体有个土办法看它有没有独立存在的意义有没有自己的属性。比如“选课”不是实体因为选课本身没有属性成绩算谁的呢成绩属于选课这个联系而不属于学生或课程单个实体。但“订单”就是实体因为它有订单号、下单时间、金额这些属于自己的属性哪怕顾客删号了订单也依然存在。2.2 属性键、多值属性、复合属性、派生属性属性Attribute是实体集的特征描述。比如学生有学号、姓名、性别、出生日期。属性在E-R图中用椭圆表示通过无向边连到所属实体上。属性里有几个概念必须搞清楚**键Key**是能唯一标识实体的属性组合。学号可以作为学生的键但姓名通常不行因为可能重名。由一个或多个属性组成的键叫超键Super Key如果这个键的任何真子集都不能唯一标识实体那它就是候选键Candidate Key。从候选键里挑一个做主键Primary Key主键下方画下划线。这里要注意主键的选择很考验业务理解。一个常见的错误是拿“身份证号”当学生的唯一标识可实际业务里学生可能没有身份证未成年人或者报名时没填身份证导致大量NULL值。这时候用自增学号反而更稳妥。**多值属性Multivalued Attribute**指一个实体可以有多个值。比如学生可以有好几个联系电话这时你可以画双线椭圆表示多值属性。但在逻辑设计阶段多值属性通常要拆成一张单独的表。这一点后面转换部分再细说。**复合属性Composite Attribute**指属性可以再拆分。比如地址可以拆成省、市、街道姓名可以拆成姓和名。在大多数系统里我建议不要过度拆分。拆到粒度过细除了增加表的列数和代码复杂度实际业务收益很有限。除非你有明确的按“省”做统计的需求否则地址存成一个大字符串完全够用。**派生属性Derived Attribute**指可以由其他数据推导出来的属性比如“年龄”可以由出生日期推导。派生属性在E-R图中用虚线椭圆表示。实战里我的建议很简单不要存储派生属性需要的时候用表达式或查询现算。你存了年龄第二年没更新就是错误数据就算做了定时任务更新也存在一个时间差的窗口期。同理订单的“总金额”如果可以由订单明细累加也不建议单独存列除非你有非常充分的性能需求。2.3 联系度、基数比、参与约束联系Relationship是E-R模型里最考验建模能力的一环。联系描述的是多个实体之间的关联用菱形表示比如“选课”就是学生和课程之间的联系。联系的程度指参与联系的实体集个数。两个实体集之间的叫二元联系三个的叫三元联系。大多数业务场景下二元联系够用。三元联系确实存在比如“供应商-项目-零件”这个经典例子描述的是哪个供应商给哪个项目供应哪种零件。三元联系转换为表的时候比较麻烦容易出现漏掉连接表的情况我建议一开始尽量把三元联系拆成几个二元联系处理实在拆不开再保留三元。基数比描述的是联系两边实体数量的对应关系。一对一1:1表示一个实体最多对应另一个实体集中的一个实体一对多1:N表示一个实体可以对应多个另一边的实体多对多M:N则表示两边都可以对应多个。判断基数比有一个很实用的方法拿真实场景问自己两个问题——“一个A最多对应几个B”“一个B最多对应几个A”答案组合起来就是基数比。比如学生和课程“一个学生能选多门课一门课能被多个学生选”这显然就是M:N。而“班级和学生”的关系“一个学生只属于一个班一个班有多个学生”这是1:N。参与约束回答的是联系的双方是否必须全部参与。比如“学生选课”是否每个学生都必须选课如果学校规定大一新生必须选课那学生就是全参与如果允许某些学生退掉所有课那就是部分参与。这个约束在逻辑设计阶段会直接影响外键是否允许NULL以及是否需要额外的强制约束。很多人画E-R图不标参与约束等落库之后才发现一些字段该设非空却设成了允许空值损失很大。3. 实操拿一个选课系统把E-R图画完整3.1 案例背景某高校选课系统的需求描述理论说多了容易飘我们直接落一个案例。假设某高校要做一套选课系统业务方给的需求是下面这段大白话系统要管理学生信息包括学号、姓名、性别、出生日期、联系电话。学生按班级管理一个学生只能属于一个班级班级有班级编号、班级名称每个班级有一个班主任班主任由教师担任一个教师最多当一个班的班主任。系统还要管理教师信息包括工号、姓名、职称。课程有课程号、课程名、学分、课程类别。学生可以选多门课每门课可以被多个学生选选课要记录选课时间和成绩。教师可以讲授多门课程每门课也可以由多个教师讲授。课程之间有先修关系比如学“数据库”之前必须先修“数据结构”。这段需求看起来很简短但信息量不小。接下来就按步骤拆解。3.2 第一步识别实体与属性我习惯的做法是把需求里的名词全部圈出来再逐个判断它们是否配得上“实体”的身份。圈出来的名词包括学生、学号、姓名、性别、出生日期、联系电话、班级、班级编号、班级名称、班主任、教师、工号、职称、课程、课程号、课程名、学分、课程类别、选课、成绩、选课时间、先修关系。初步筛选后有明显属性的候选实体是学生、班级、教师、课程。这四个都有属于自己的属性组合应该画成实体。“学号、姓名、性别、出生日期、联系电话”这些都是学生的属性“班级编号、班级名称”是班级的属性“工号、姓名、职称”是教师的属性“课程号、课程名、学分、课程类别”是课程的属性。班主任在这里不是实体它是教师和班级之间的一种联系角色。“选课”和“授教”不配当实体它们是联系。但“成绩”和“选课时间”这两个属性很关键它们不能挂在学生或课程上而必须挂在“选课”这个联系上——原因很简单同一个学生选不同课程的成绩不同同一门课不同学生成绩也不同成绩是“某个学生选了某门课”这个行为带来的结果。这是E-R建模里最经典的一个考点也最能区分建模功底。3.3 第二步确定联系类型与基数比识别出四个实体后我们来逐个梳理它们之间的联系。学生和班级之间是“属于”联系。一个学生属于一个班级一个班级有多个学生所以是N:1站在班级角度或者说1:N站在学生角度。这里要注意按教材的规范联系上靠近“多”的那一端要标上N或箭头。教师和班级之间是“担任班主任”联系。需求里写了“一个教师最多当一个班的班主任”那么反过来“一个班级有几个班主任”答案是1个。这是典型的1:1联系。但这里还有个隐藏问题是不是每个班级都必须有班主任是不是每个教师都必须当班主任需求里没写死。如果学校要求每个班级必须有班主任那班级就是全参与如果教师可以不当班主任教师就是部分参与。这个不确定的点要和业务方确认不能自己拍脑袋。为了演示我们假设每个班级必须有班主任但不是每个教师都当班主任。学生和课程之间是“选课”联系。前面已经分析过这是M:N。而且这个联系带有成绩、选课时间两个属性。教师和课程之间是“讲授”联系。一个教师可以讲授多门课程一门课程也可以由多个教师讲授这是M:N。这里有个需要辨析的点一个教师讲授一门课和一个学生选一门课虽然在表结构上都是多对多产生连接表但业务语义完全不同所以必须拆成两个独立的联系不能混淆。课程和课程之间的“先修”联系很有意思它是一元联系也叫自联系因为实体集和实体集自身发生关联。一个课程可以有多个先修课程一个课程也可以被多个后续课程作为先修课所以是M:N。在实际落库的时候这种一元M:N联系通常需要一张单独的先修关系表。3.4 第三步处理弱实体、多值属性这些特殊场景上面的案例比较标准还没涉及弱实体和多值属性。但这两个概念是第七章的重要考点我顺带扩展说明。弱实体Weak Entity指没有足够属性组成完整主键的实体它必须依赖另一个实体才能存在。经典的例子是“家属”和“员工”家属有自己的姓名、年龄但如果没有所属员工家属这个实体就没法独立标识。因为不同员工可能有同名的家属所以家属主键由“员工编号家属序号”联合组成。在E-R图中弱实体用双边矩形它的部分键Partial Key用虚线下划线依赖联系用双边菱形。落库时弱实体对应的表要包含owner实体的主键作为外键和自身部分键一起组成联合主键。多值属性在实战里也很常见。回到选课系统如果需求改一下“学生联系电话”变成“学生可以有多个联系电话”那这个属性就该画成双线椭圆。转换时单独建一张student_phone表存储学号电话。有些新手图省事在student表里设计phone1、phone2、phone3三个字段这种设计非常僵硬——业务方再加一个号码怎么办改表多数情况下拆表是更干净的方式。4. E-R模型向关系模型转换从图到表的最后一公里4.1 转换规则一览E-R图画得再好最终还是要转成表结构落地。教材里给出了几条核心转换规则我把它们整理成一张速查表E-R模型元素转换规则关键要点普通实体集直接转成一张表实体属性变列键变主键二元1:1联系外键放任意一端优先放在全参与的一端或合并成一张表二元1:N联系外键放在N端在“多”的一方添加“一”方的主键作为外键二元M:N联系独立生成一张连接表两端主键合起来当连接表的主键多值属性单独生成一张表含原实体主键作为外键弱实体集生成一张表包含owner外键外键部分键联合组成主键三元联系一般生成独立表三个参与方的主键作为外键通常联合作为主键4.2 多对多联系独立成表关键中的关键M:N联系必须独立成表这是E-R转换里最重要的一条规则。很多人第一次设计学生选课表时会尝试在student表里加一个course_ids字段存一串用逗号分隔的课程号。这种设计在关系型数据库里等于埋雷查询某个学生的所有课程时必须做字符串拆分不仅SQL写得痛苦还会导致索引失效、统计困难。正确做法是建一张选课表主键由学生主键和课程主键联合组成CREATE TABLE student ( stu_no VARCHAR(20) PRIMARY KEY, name VARCHAR(50), gender CHAR(1), birth_date DATE, class_no VARCHAR(20) ); CREATE TABLE course ( course_no VARCHAR(20) PRIMARY KEY, course_name VARCHAR(100), credit INT, category VARCHAR(50) ); CREATE TABLE takes ( stu_no VARCHAR(20), course_no VARCHAR(20), score DECIMAL(4,1), take_time DATETIME, PRIMARY KEY (stu_no, course_no), FOREIGN KEY (stu_no) REFERENCES student(stu_no), FOREIGN KEY (course_no) REFERENCES course(course_no) );这里有一个我在工作中经常和人争论的点takes表到底该用联合主键还是额外加一个自增ID作为主键我的看法是看你业务上是否允许“同一个学生同一门课出现多条记录”。如果学校不允许学生重复选同一门课联合主键既满足唯一性约束又能天然防止重复插入是更合理的选择。但如果你做的系统是补考报名、重修记录同一个学生同一门课可能出现多条历史记录那联合主键就不合适了需要加一个无业务含义的自增主键。4.3 外键放哪边由基数比决定1:N联系的外键放在哪端是最容易背错的一条规则。我当年也犯过迷糊把班级编号放在了student表里结果一个学生对应一个班级放student表没问题。但如果反过来把学生编号放在class表里问题就大了一个班级有50个学生class表里怎么放放一个字段存不下放多个字段又不知道到底有几个。所以规则就是在“多”的一方比如多个学生放“一”的一方班级的主键作为外键。这样每个学生都能记录自己属于哪个班而班级表不需要关心学生列表——需要查询某个班级的所有学生时用student表按class_no查即可。1:1联系的外键位置则更灵活。比如教师和班级的“班主任”联系外键可以放在class表里class表加teacher_no也可以放在teacher表里teacher表加class_no。我的习惯是放在全参与的那一方。既然每个班级必须有班主任那class表里加teacher_no并设置NOT NULL就能保证数据完整性。如果反过来放在teacher表里就得允许大量NULL值因为有很多教师不当班主任虽然也能约束但约束力弱一些查询时也要多一次join。课程先修关系的转换值得单独说。课程与课程是M:N的自联系需要建一张先修关系表CREATE TABLE prereq ( course_no VARCHAR(20), prereq_no VARCHAR(20), PRIMARY KEY (course_no, prereq_no), FOREIGN KEY (course_no) REFERENCES course(course_no), FOREIGN KEY (prereq_no) REFERENCES course(course_no) );这里有一个约束细节是否可以允许某门课的先修课程等于自身业务上不应当出现但数据库不会自动阻止。我通常会在应用层做校验或者在插入前先SELECT确认。不要指望数据库能替你智能判断这种业务规则。5. 高频问题排查与实战避坑5.1 常见的E-R建模错误一览下面这些坑是我在实际开发中见过或者踩过的整理成表格方便复盘。问题现象根本原因解决方案成绩字段直接放在student表里没识别出选课是M:N联系把成绩当成学生固有属性建立takes表成绩作为联系属性一门课只能放一个教师教师授课的M:N联系被误判成1:N建teach表一个课程对应多条授教记录学生电话号码建了三个列没有识别多值属性拆student_phone表年龄字段存了几年后数据不准存储了派生属性存birth_date年龄用查询计算班级和教师是一对多班主任信息丢失没确认“一个教师最多带一个班”确认基数比画成1:1联系课程先修关系塞在course表一个字段里忽略了一元M:N联系建prereq表5.2 一次真实的返工复盘我之前做一个教务相关的小系统第一版就把班主任的基数比画错了。当时需求文档里写“班级有班主任”我想当然地画成了教师和班级的1:N联系转换时把teacher_no放到了class表里。等到做权限功能时业务方说“一个老师只能当一个班的班主任”我立刻意识到这是1:1联系。虽然表结构不用改外键还是放在class表但业务校验逻辑必须重写还返工改了一批初始化数据。这次踩坑让我养成了一个习惯每画出一条联系就向业务方问一遍“一个X最多对应几个Y”和“一个Y最多对应几个X”然后把答案记录在建模文档里。比起自己猜确认基数比看起来多花两分钟但能省掉后面改表的大把时间。另外我见过不少人为了画图好看给实体加了一堆不必要的属性。比如学生实体里加了email、QQ、微信号、家庭住址、紧急联系人……这些字段如果是系统明确需要管理的就画上去用不上的统统删掉。E-R图是业务模型不是数据仓库清单。画得越简洁后续转换表结构的时候越轻松。5.3 参与约束容易忽略但影响非空约束参与约束很多时候会被忽略但它直接决定了外键列能不能是NULL。回到选课系统如果业务上有“新生入学第一学期必须至少选一门课”那学生和课程在选课联系中都是全参与。落库时takes表里的stu_no、course_no都应该设为NOT NULL这是由约束推导出来的。如果业务方说“允许学生退掉所有课”那学生的参与就是部分的反映到表设计上你就得考虑如果不强制选课是否还有必要保留一些默认值或兜底逻辑。我在画E-R图的时候会用实线表示全参与虚线表示部分参与。这个习惯在团队协作时特别有好处因为看图的人一眼就能明白哪些数据是必须的。不过要提醒一下参与约束和基数比是两回事。比如教师和班级的班主任联系基数比是1:1不假但教师端是部分参与——很多教师并不是班主任。不要把这两个概念混淆。最后说点个人体会。E-R模型这章表面上学的是画图实际上学的是抽象业务的能力。我做了这么多年开发越来越觉得画E-R图的时候功夫在数据库之外——你要能听懂业务人员在说什么要能在模糊的需求中提取出实体和关系还要能预判哪些规则没被说透会导致未来返工。建议你学完这一章之后随手拿一个身边的真实场景练手比如宿舍管理系统、图书借阅系统、外卖点单系统把E-R图画出来再转成表结构这个过程比单纯刷题印象深刻得多。画每一条联系时都试着用一句话描述清楚业务规则这个习惯会一直受益。