别再死记硬背了！用这5个真实业务场景（选课/图书/医院），手把手教你画E-R图和设计数据库表
实战数据库设计：5个真实业务场景下的E-R图与表结构解析
在数据库设计的教学过程中，最常听到的抱怨就是"学了一堆理论，遇到真实项目还是无从下手"。确实，单纯记忆E-R图的菱形、矩形符号，或是背诵范式的定义，并不能真正培养出解决实际问题的能力。本文将打破传统教学模式，选取教育、医疗、图书管理等五个典型业务场景，带您从零开始完成完整的数据库设计流程。
1. 选课管理系统：从需求到实现的完整设计
大学选课系统是理解多对多关系的经典案例。让我们从一个真实的需求出发：某高校需要开发新系统，要求记录学生选课情况、成绩，并能统计各班级选课人数。系统需支持学生查询已修学分，教师录入成绩，管理员生成报表等功能。
核心实体识别：
- 学生：学号（主键）、姓名、性别、年龄
- 班级：班号（主键）、班名、所属专业
- 课程：课程号（主键）、课程名、学分、开课学期
关键业务规则：
- 一个班级包含多个学生，每个学生只属于一个班级（1:n）
- 学生可选修多门课程，每门课程可被多个学生选修（m:n）
- 成绩只存在于学生与课程建立联系后（联系属性）
-- 最终表结构SQL实现 CREATE TABLE 班级 ( 班号 CHAR(6) PRIMARY KEY, 班名 VARCHAR(20) NOT NULL, 专业 VARCHAR(30) ); CREATE TABLE 学生 ( 学号 CHAR(10) PRIMARY KEY, 姓名 VARCHAR(10) NOT NULL, 性别 CHAR(1) CHECK(性别 IN ('男','女')), 年龄 SMALLINT, 班号 CHAR(6) REFERENCES 班级(班号) ); CREATE TABLE 课程 ( 课程号 CHAR(8) PRIMARY KEY, 课程名 VARCHAR(30) NOT NULL, 学分 SMALLINT DEFAULT 2, 开课学期 SMALLINT ); CREATE TABLE 选课 ( 学号 CHAR(10) REFERENCES 学生(学号), 课程号 CHAR(8) REFERENCES 课程(课程号), 成绩 DECIMAL(5,2), 选课时间 DATE, PRIMARY KEY (学号, 课程号) );注意：成绩应该作为选课表的属性而非学生表，因为只有选课后才会产生成绩。学分则属于课程固有属性。
2. 医院病房管理系统：复杂业务关系的处理技巧
医疗系统的数据模型往往涉及复杂的业务规则。某三甲医院需要建立住院管理系统，需管理科室、病房、医生和病人的多级关系。特殊要求包括：每个病人有唯一主管医生，医生只能属于一个科室，病房需按科室分配。
设计过程中的关键决策点：
- 科室-病房关系：采用1:n设计，病房表包含科室外键
- 医生分配：医生表中设置科室外键，病人表设置医生外键
- 病房分配：病人表需要同时记录病房号和床位号（复合外键）
E-R图转换关系模型：
| 表名 | 主键 | 外键 | 特殊约束 |
|---|---|---|---|
| 科室 | 科名 | 无 | 科电话需符合格式校验 |
| 病房 | 病房号 | 科名→科室.科名 | 床位号需与病房号联合唯一 |
| 医生 | 工作证号 | 科名→科室.科名 | 职称需在预设范围内 |
| 病人 | 病历号 | 工作证号→医生.工作证号 | 病房号+床位号→病房.病房号+床位号 |
-- 病房表的特殊设计 CREATE TABLE 病房 ( 病房号 VARCHAR(10), 床位号 VARCHAR(5), 科名 VARCHAR(20) REFERENCES 科室(科名), PRIMARY KEY (病房号, 床位号), UNIQUE (病房号, 床位号, 科名) -- 确保病房属于指定科室 );3. 图书借阅系统：时间维度与历史记录设计
图书馆管理系统需要特别关注时间维度的处理。以某市图书馆为例，系统需记录每本书的借还历史，计算逾期罚款，并限制读者借阅数量。关键业务规则包括：同一本书可能被反复借阅，需要完整历史记录；还书日期可能为空（表示未还）；不同类型读者借阅上限不同。
历史数据处理的三种方案对比：
状态标记法（简单但不完整）：
CREATE TABLE 借阅 ( 读者编号 VARCHAR(10), 图书编号 VARCHAR(20), 借期 DATE NOT NULL, 还期 DATE NULL, 状态 CHAR(1) DEFAULT '0' -- 0在借 1已还 );版本号法（适合频繁更新）：
CREATE TABLE 借阅历史 ( record_id SERIAL PRIMARY KEY, 读者编号 VARCHAR(10), 图书编号 VARCHAR(20), 操作时间 TIMESTAMP, 操作类型 CHAR(1), -- B借 R还 经办人 VARCHAR(10) );触发器辅助法（自动维护历史）：
CREATE TABLE 借阅日志 ( log_id SERIAL PRIMARY KEY, 原记录ID INT, 操作类型 VARCHAR(10), 操作时间 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 操作人 VARCHAR(20) );
提示：对于需要完整审计追踪的系统，建议采用方案3，虽然实现复杂但数据最可靠。
4. 电商订单系统：主从表设计与事务处理
电商平台的订单系统是典型的一对多关系案例。一个订单包含多个商品，每个商品条目有独立的价格、数量（考虑促销价可能变化）。设计时需要特别注意数据一致性和事务完整性。
订单核心表结构：
CREATE TABLE 订单 ( 订单号 VARCHAR(20) PRIMARY KEY, 用户ID VARCHAR(20) NOT NULL, 下单时间 TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 总金额 DECIMAL(10,2) CHECK(总金额 >= 0), 状态 VARCHAR(10) CHECK(状态 IN ('待支付','已支付','已发货','已完成','已取消')), 收货地址 TEXT ); CREATE TABLE 订单明细 ( 明细ID SERIAL PRIMARY KEY, 订单号 VARCHAR(20) REFERENCES 订单(订单号), 商品ID VARCHAR(15) NOT NULL, 购买价 DECIMAL(10,2) NOT NULL, 数量 INT CHECK(数量 > 0), 优惠金额 DECIMAL(10,2) DEFAULT 0, UNIQUE (订单号, 商品ID) -- 防止同一商品重复添加 );常见问题处理方案：
- 价格不一致问题：记录购买时的快照价格，而非实时引用商品表价格
- 库存扣减时机：支付成功后异步扣减，采用预扣库存机制
- 订单取消处理：需要事务处理订单状态和库存回滚
5. 仓储管理系统：多对多关系的复杂案例
仓库管理系统涉及零件、供应商和工程项目的复杂多对多关系。特殊挑战包括：同一零件可能来自不同供应商且价格不同；工程项目需要记录每种零件的需求总量；需要跟踪每次采购的交货情况。
核心表设计技巧：
- 三元关系处理：引入供应关系表记录供应商-零件-工程的三方关系
- 历史价格追踪：在采购表中记录每次交易价格，而非仅在零件表中维护
- 库存事务设计：采用入库单、出库单模式，便于审计追踪
CREATE TABLE 供应关系 ( 供应商ID VARCHAR(10), 零件ID VARCHAR(15), 工程ID VARCHAR(12), 报价 DECIMAL(10,2), 最小起订量 INT, 交货周期 INT, PRIMARY KEY (供应商ID, 零件ID, 工程ID) ); CREATE TABLE 采购单 ( 采购单号 VARCHAR(20) PRIMARY KEY, 供应商ID VARCHAR(10), 工程ID VARCHAR(12), 创建时间 TIMESTAMP, 预期到货 DATE, 状态 VARCHAR(10) ); CREATE TABLE 采购明细 ( 明细ID SERIAL PRIMARY KEY, 采购单号 VARCHAR(20) REFERENCES 采购单(采购单号), 零件ID VARCHAR(15), 数量 INT, 实际单价 DECIMAL(10,2), 到货状态 VARCHAR(10) );在实际项目中，数据库设计从来不是一蹴而就的过程。我曾参与一个图书管理系统的重构，最初设计忽略了读者可能续借的情况，导致后来不得不增加续借次数字段并修改相关查询逻辑。经验告诉我们：好的设计应该预留适当的扩展空间，同时保持核心数据结构的稳定。