数据库概念结构设计完全指南：从E-R图到建表实战

前言为什么需要概念结构设计在数据库系统的开发过程中设计者通常需要面对一个问题如何将现实世界中的业务需求准确、高效地转化为计算机能够存储和处理的数据结构如果直接跳到物理设计也就是写CREATE TABLE语句往往会导致表结构混乱、数据冗余严重、更新异常频发等问题。而概念结构设计正是解决这一问题的关键环节。概念结构设计位于需求分析之后、逻辑结构设计之前它的核心任务是描述用户需求中的数据实体、属性以及实体之间的业务关系形成一个独立于任何具体数据库管理系统DBMS的抽象模型。最常用、最直观的概念建模工具就是实体-联系图也就是我们常说的E-R图。本文将系统讲解E-R图的基本要素、绘制方法、三种映射基数1:1、1:N、M:N及其SQL实现并配合三个贴近实际业务的完整案例帮助你从零掌握数据库概念设计。全文不包含任何图片所有图形均用文字和符号描述并配以详实的代码示例确保你能够直接上手实践。一、E-R图的核心概念实体、属性、联系在E-R图中数据世界被抽象为三样东西实体Entity、属性Attribute和联系Relationship。下面逐一展开。1.1实体Entity—— 矩形实体是现实世界中可区别于其他对象的“事物”或“对象”。它可以是具体的人、物也可以是抽象的概念或事件。例如学生、课程、订单、系、仓库、图书等。实体型指的是具有相同属性特征的实体集合在E-R图中用一个矩形框表示框内写上实体名。例如“学生”就是一个实体型而“张三”、“李四”则属于该实体型下的具体实体实例。 文字化E-R图表示法[学生]表示实体。1.2 属性Attribute—— 椭圆形属性用于描述实体的特征或性质。每个实体可以有多个属性属性在E-R图中用椭圆形表示并用无向边连接到所属的实体。例如学生实体有“学号”、“姓名”、“性别”、“出生日期”等属性。 文字化E-R图表示法(学号)、(姓名)表示属性连接线用箭头或文字描述。1.3 属性的分类进阶补充简单属性不可再分如“性别”。复合属性可细分为更小的部分如“地址”可分为“省、市、街道”。E-R图中可以画出子属性。单值属性对一个实体只有一个值如“出生日期”。多值属性一个实体可能有多个值如“电话号码”。在E-R图中常用双椭圆形表示。派生属性可以从其他属性计算得出如“年龄”由“出生日期”派生常用虚线椭圆表示。主键属性唯一标识实体的属性通常在属性名下加下划线。为了简化本节案例只使用简单、单值、主键属性。多值属性和派生属性在后续实战中会涉及转换规则。1.4 联系Relationship—— 菱形联系反映的是两个或多个实体之间的业务关联。在E-R图中联系用菱形表示菱形框内写上联系名并用无向边将菱形连接到参与联系的各个实体。联系可以带有属性例如“选修”联系可以包含“成绩”属性。联系也可以有映射基数指明一个实体实例能与另一个实体实例关联的数量范围。映射基数是后文的核心内容。 文字化E-R图表示法[学生]--(选修)--[课程]表示学生选修课程的联系。二、映射基数1:1、1:N、M:N的完整解析映射基数也称基数比是E-R图中最重要的约束信息它决定了后续数据库表结构的设计方式。主要有三种基本类型一对一1:1、一对多1:N和多对多M:N。注意“N”和“M”均表示“多”的意思。2.1 一对一联系1:1定义对于实体集A中的每一个实体在实体集B中至多有一个实体与之联系反之亦然。也就是说两个实体集的实例之间的关联是相互唯一对应的。举例一个系有且只有一个系主任一个系主任只能管理一个系。一个人只有一张身份证一张身份证只属于一个人。一个公司只有一个法人代表一个法人代表只能在一个公司任职假设无兼职。E-R图形描述文字版[院长] ————(管理)———— [学院] 1 1在联系“管理”的旁边分别标注“1”和“1”。关系模式转换规则可以在任意一个实体对应的表中增加另一张表的主键作为外键并为该外键添加UNIQUE约束以确保一对一。2.2 一对多联系1:N定义对于实体集A中的一个实体实体集B中有N个零个、一个或多个实体与之联系但对于实体集B中的一个实体至多只有一个实体集A中的实体与之联系。即“一”方对应“多”方。举例一个仓库可以存放多种商品但每种商品只能存放在一个仓库中。一个部门有多名员工但一名员工只属于一个部门。一位作者可以写多本书但一本书只属于一位作者忽略合著情况。E-R图形描述文字版[仓库] ————(存放)———— [商品] 1 N注意有些教材把一对多画成1:N但在连线箭头旁分别标注1和N。关系模式转换规则在“多”方的表中添加“一”方的主键作为外键不需要额外约束。2.3 多对多联系M:N定义对于实体集A中的一个实体实体集B中有N个实体与之联系对于实体集B中的一个实体实体集A中也有M个实体与之联系。即双方都是“多”。举例一个教师可以讲授多门课程一门课程也可以由多位教师讲授。一个学生可以选修多门课程一门课程也可以被多名学生选修。一本书可以属于多个分类一个分类下可以包含多本书。E-R图形描述文字版[教师] ————(讲授)———— [课程] m n有时写为M和N。关系模式转换规则必须创建一个独立的关联表也称为中间表、连接表该表至少包含两个外键分别指向两个实体的主键并且这两个外键共同构成联合主键或单独创建一个代理主键。三、案例一一对一联系系与主任3.1 业务描述某学院有若干个系每个系有且只有一个系主任每个系主任只能管理一个系。系主任也是学校的教师但这里我们只关注系主任这一角色。系主任实体包含以下属性编号主键、姓名、年龄、学历。系实体包含系编号主键、系名。另外在“管理”这个联系中需要记录系主任的任职时间这是一个联系属性。3.2 E-R图要素文字版实体[主任]、[系]属性主任(编号)、(姓名)、(年龄)、(学历)系(系编号)、(系名)联系(管理)方向为主任与系之间标注为1:1。联系属性(任职时间)属于联系管理。 文字化E-R结构图描述[主任] 1 ———— (管理[任职时间]) ———— 1 [系] | | (编号)(姓名)(年龄)(学历) (系编号)(系名)3.3 转换为关系表一对一联系有两种常见的转换方案方案A将任意一方的主键放入另一方作为外键并设置唯一性。方案B将两个实体合并成一张表适用于经常同时查询且字段不多时。这里采用方案A在系表中增加主任编号作为外键因为通常一个系的信息中会包含其主任信息。同时为外键添加UNIQUE约束确保一个系只能关联一个主任而一个主任也只能管理一个系但反过来数据库约束只能保证从表到主表的方向唯一还需要业务逻辑确保主任不兼任。实际上更严谨的做法是在主任表中也加外键但会造成双向冗余。通常选择在“子”表中加外键。这里我们选择在“系”表中添加dean_id。3.4 SQL建表代码-- 创建主任表 CREATE TABLE dean ( dean_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主任编号, name VARCHAR(50) NOT NULL COMMENT 姓名, age INT COMMENT 年龄, education VARCHAR(100) COMMENT 学历 ); -- 创建系表外键引用主任表并设置UNIQUE保证一对一 CREATE TABLE department ( dept_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 系编号, dept_name VARCHAR(100) NOT NULL UNIQUE COMMENT 系名, dean_id INT UNIQUE COMMENT 主任编号外键, FOREIGN KEY (dean_id) REFERENCES dean(dean_id) ); -- 如果需要记录任职时间联系属性建议单独建一张联系表 -- 但1:1也可以直接放在系表或主任表中。 -- 这里我们在系表中增加一个字段 hire_date。 ALTER TABLE department ADD COLUMN hire_date DATE COMMENT 主任任职时间;或者采用独立联系表的方案更规范尤其当联系有多个属性时-- 独立的联系表 CREATE TABLE manage ( dean_id INT UNIQUE, -- 1:1 双方都唯一 dept_id INT UNIQUE, hire_date DATE, PRIMARY KEY (dean_id, dept_id), FOREIGN KEY (dean_id) REFERENCES dean(dean_id), FOREIGN KEY (dept_id) REFERENCES department(dept_id) );在实际开发中若1:1联系很少使用独立表通常将联系属性并入某一实体。四、案例二一对多联系仓库与商品4.1 业务描述在某仓库管理系统中有多个仓库每个仓库可以存放多种商品但一类商品只能存放在一个仓库中即商品不能跨仓库存放。仓库实体包含仓库号主键、地点、面积。商品实体包含商品号主键、商品名、价格。在“存放”联系中需要记录该仓库中该种商品的数量即库存量。4.2 E-R图要素文字版实体[仓库]、[商品]属性仓库(仓库号)、(地点)、(面积)商品(商品号)、(商品名)、(价格)联系(存放)方向为仓库1 — n商品。联系属性(数量) 文字化描述[仓库] 1 ———— (存放[数量]) ———— n [商品] | | (仓库号)(地点)(面积) (商品号)(商品名)(价格)4.3 转换规则一对多联系将“一”方仓库的主键放入“多”方商品表中作为外键同时将联系属性数量也放入“多”方表中。因为每个商品只属于一个仓库所以商品表中增加warehouse_id列和quantity列即可。4.4 SQL建表代码-- 仓库表一 CREATE TABLE warehouse ( wh_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 仓库号, location VARCHAR(200) NOT NULL COMMENT 地点, area DECIMAL(10,2) COMMENT 面积平方米 ); -- 商品表多 CREATE TABLE product ( product_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 商品号, product_name VARCHAR(100) NOT NULL COMMENT 商品名, price DECIMAL(10,2) COMMENT 价格, wh_id INT NOT NULL COMMENT 所属仓库编号, quantity INT DEFAULT 0 COMMENT 存放数量联系属性, FOREIGN KEY (wh_id) REFERENCES warehouse(wh_id) );如果在实际业务中一个商品可以存放在多个仓库多对多那就需要中间表了。但本例明确为一对多所以上述设计完全正确。4.5 数据示例-- 插入一个仓库 INSERT INTO warehouse (location, area) VALUES (北京市朝阳区仓库A, 5000.00); -- 插入商品数量为100 INSERT INTO product (product_name, price, wh_id, quantity) VALUES (智能手机, 2999.00, 1, 100);查询某个仓库的所有商品及其库存量SELECT w.wh_id, w.location, p.product_name, p.quantity FROM warehouse w JOIN product p ON w.wh_id p.wh_id WHERE w.wh_id 1;五、案例三多对多联系教师与课程5.1 业务描述在某教务管理系统中一个教师可以讲授多门课程一门课程也可以由多位教师讲授。这是一个典型的多对多关系。教师实体属性教师号主键、教师名、职称。课程实体属性课程号主键、课程名、班级指面向的班级比如“计算机1班”。在“讲授”联系中需要记录教师的授课质量例如评估得分或等级。5.2 E-R图要素文字版实体[教师]、[课程]属性教师(教师号)、(教师名)、(职称)课程(课程号)、(课程名)、(班级)联系(讲授)方向为教师m — n课程。联系属性(授课质量) 文字化描述[教师] m ———— (讲授[授课质量]) ———— n [课程] | | (教师号)(教师名)(职称) (课程号)(课程名)(班级)5.3 转换规则对于多对多联系必须创建一张独立的关联表有时称为“中间表”。该表包含两个外键分别指向教师表和课程表的主键并且这两个外键组合成联合主键也可以单独设一个自增ID作为主键但联合主键更能防止重复关联。此外联系属性“授课质量”也放在这张关联表中。5.4 SQL建表代码-- 教师表 CREATE TABLE teacher ( teacher_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 教师号, name VARCHAR(50) NOT NULL COMMENT 教师名, title VARCHAR(50) COMMENT 职称 ); -- 课程表 CREATE TABLE course ( course_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 课程号, course_name VARCHAR(100) NOT NULL COMMENT 课程名, class_name VARCHAR(100) COMMENT 班级 ); -- 中间表讲授关联表 CREATE TABLE teach ( teacher_id INT, course_id INT, teaching_quality VARCHAR(20) COMMENT 授课质量例如优秀/良好/合格, PRIMARY KEY (teacher_id, course_id), -- 联合主键 FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id), FOREIGN KEY (course_id) REFERENCES course(course_id) );5.5 数据示例-- 插入教师 INSERT INTO teacher (name, title) VALUES (张教授, 教授); INSERT INTO teacher (name, title) VALUES (李老师, 讲师); -- 插入课程 INSERT INTO course (course_name, class_name) VALUES (数据库原理, 计算机1班); INSERT INTO course (course_name, class_name) VALUES (数据结构, 计算机2班); -- 张教授讲授数据库原理质量为“优秀” INSERT INTO teach (teacher_id, course_id, teaching_quality) VALUES (1, 1, 优秀); -- 李老师也讲授数据库原理质量为“良好” INSERT INTO teach (teacher_id, course_id, teaching_quality) VALUES (2, 1, 良好); -- 张教授还讲授数据结构质量“优秀” INSERT INTO teach (teacher_id, course_id, teaching_quality) VALUES (1, 2, 优秀);查询所有课程及其任课教师SELECT c.course_name, t.name, te.teaching_quality FROM course c JOIN teach te ON c.course_id te.course_id JOIN teacher t ON te.teacher_id t.teacher_id;六、从E-R图到实际数据库的完整步骤补充指南1.需求分析与业务方沟通列出所有数据对象和业务规则。2.寻找实体名词分析法通常每个核心业务对象都是一个实体。3.确定属性为每个实体列出描述性特征选出主键。4.确定联系分析两个实体之间是否存在业务关联并判断映射基数1:1、1:N、M:N。5.画出E-R图可用纸笔或绘图工具本文用文字描述。6.转换为关系模式每个实体对应一张表主键即实体主键。1:1 联系任意一方添加对方主键作为外键 UNIQUE。1:N 联系在N方表中添加1方主键作为外键。M:N 联系新建中间表包含两个外键及联系属性。7.规范化检查是否满足第三范式3NF消除传递依赖。8.物理设计编写CREATE TABLE语句选择合适的数据类型添加索引、约束等。9.填充测试数据验证设计是否满足需求。七、总结与最佳实践通过本文的详细讲解你应该已经掌握了E-R图的核心概念以及如何将其转化为实际的数据库表。回顾重点1.实体用矩形属性用椭圆联系用菱形这是E-R图的“三原色”。2.映射基数决定了外键的放置方式1:1任选一方加UNIQUE1:N在N方加外键M:N必用中间表。3.联系属性可以放在外键所在的表中1:1或1:N或中间表中M:N。4.始终在需求分析之后先画E-R图不要跳过概念设计直接写SQL。常见的失误与避坑指南1.误将多对多当成一对多比如学生选课如果只看一个学生能选多门课就只建了一个外键会遗漏课程也能被多个学生选的事实。解决方法一旦发现双方都是“多”立刻用中间表。2.忽略联系属性如仓库的存放数量、教师的授课质量这些属性既不属于A也不属于B必须放到联系上。3.忘记主键和外键的约束在SQL中要显式声明PRIMARY KEY和FOREIGN KEY。4.不进行规范化导致数据冗余和更新异常。例如将课程名称重复存储在书评表中应该通过book_id外键引用。最后记住一句话好的数据库设计是从一张清晰的E-R图开始的。希望你在今后的开发项目中能够熟练运用本文所讲的方法设计出结构优美、扩展性强的数据库模型。