数据库设计方法：从需求到表结构

数据库设计方法从需求到表结构一句话总结数据库设计遵循需求分析 → 概念结构设计E-R 图 → 逻辑结构设计关系模式 → 物理结构设计索引/存储四步流程每一步都有明确的目标和交付物是理论与实践的完美结合。一、数据库设计的六个阶段数据库设计不是一拍脑袋画几张表而是有严格工程方法的。标准的数据库设计分为六个阶段1. 需求分析 ↓ 2. 概念结构设计E-R 图 ↓ 3. 逻辑结构设计关系模式 ↓ 4. 物理结构设计索引、分区、存储 ↓ 5. 数据库实施建表、导入数据、开发 ↓ 6. 数据库运行与维护优化、备份、扩容本文重点讲前四个阶段这是设计理论的核心。二、需求分析搞清楚要存什么2.1 需求分析的任务数据需求系统需要管理哪些数据处理需求对这些数据做什么操作增删改查性能需求响应时间、并发量、数据量安全需求谁可以看什么谁可以改什么2.2 需求分析的方法访谈和业务人员面对面聊问卷调查收集各部门的数据使用情况查阅文档现有系统的报表、Excel、文档跟岗观察看实际业务怎么流转原型演示用草图让用户确认理解是否正确2.3 需求分析的成果数据字典数据字典是需求分析的核心产出详细记录每个数据项的定义数据项含义类型长度取值范围来源备注学号学生唯一编号CHAR10固定格式教务处主键姓名学生姓名VARCHAR20非空入学登记成绩课程成绩INT30-100教师录入可为空未出成绩三、概念结构设计画 E-R 图这是数据库设计中最关键、最体现设计能力的阶段。目标是画出一套准确描述现实世界的 E-R 图。3.1 设计步骤确定局部应用按业务部门或功能模块划分如学生管理、课程管理、成绩管理设计局部 E-R 图为每个局部应用画 E-R 图合并全局 E-R 图把所有局部 E-R 图合并消除冲突和冗余优化 E-R 图检查命名一致性、消除冗余、调整结构3.2 局部 E-R 图设计示例学生管理系统局部一学生信息管理学生(学号, 姓名, 性别, 出生日期) ——属于—— 班级(班号, 班级名)局部二课程管理课程(课程号, 课程名, 学分) ——由—— 教师(教师号, 姓名, 职称) 教授局部三选课管理学生(学号) ——选修—— 课程(课程号), 属性成绩3.3 合并全局 E-R 图将三个局部图合并统一实体和属性┌─────────┐ │ 学生 │ │ 学号 │ │ 姓名 │ │ 性别 │ │ 出生日期│ └───┬─────┘ │ 属于 (1:n) ▼ ┌─────────┐ │ 班级 │ │ 班号 │ │ 班级名 │ │ 入学年份│ └─────────┘ │ │ 包含 ▼ ┌─────────┐ ┌─────────┐ │ 学生 │───选修───│ 课程 │ │ │ (m:n) │ 课程号 │ │ │ 成绩 │ 课程名 │ │ │ │ 学分 │ └─────────┘ └────┬────┘ │ 教授 (1:n) ▼ ┌─────────┐ │ 教师 │ │ 教师号 │ │ 姓名 │ │ 职称 │ └─────────┘3.4 合并时的冲突处理冲突类型示例解决方法命名冲突局部一叫学号局部二叫学生编号统一命名规范结构冲突局部一学生有系属性局部三学生没有取并集允许 NULL联系冲突局部一学生-班级是 1:n局部二画成 n:1统一为 1:n四、逻辑结构设计E-R 图 → 关系模式E-R 图是概念模型不能直接建表。需要把它转换成 DBMS 能理解的关系模式。4.1 转换规则规则 1实体 → 表每个实体型转换为一个关系模式表实体的属性就是表的属性实体的码就是表的主键。学生(学号, 姓名, 性别, 出生日期) ← 主键学号 班级(班号, 班级名, 入学年份) ← 主键班号 课程(课程号, 课程名, 学分) ← 主键课程号 教师(教师号, 姓名, 职称) ← 主键教师号规则 21:1 联系 → 外键或合并方案 A将联系和任意一端合并。如学生和班长一个学生可能是班长一个班长对应一个学生学生(学号, 姓名, ..., 是否班长, 任职日期)方案 B在任意一端加外键学生(学号, 姓名, ..., 班长学号) ← 班长学号外键引用学生表规则 31:n 联系 → 外键加到 n 端班级(班号, 班级名, ...) ← 1 端 学生(学号, 姓名, ..., 班号) ← n 端加外键班号规则 4m:n 联系 → 独立建表选课(学号, 课程号, 成绩) ← 主键(学号, 课程号) ↓ 外键学号 引用 学生 ↓ 外键课程号 引用 课程m:n 联系必须拆成独立表这是最常见的考点。规则 5多值属性 → 独立建表如果一个属性有多个值如一个学生有多个手机号不能放在学生表中必须独立建表学生手机号(学号, 手机号) ← 主键(学号, 手机号)4.2 完整转换示例将前面的学生管理系统 E-R 图转换为关系模式-- 实体表学生(学号,姓名,性别,出生日期,班号)主键学号 外键班号 引用 班级(班号)班级(班号,班级名,入学年份)主键班号 课程(课程号,课程名,学分,教师号)主键课程号 外键教师号 引用 教师(教师号)教师(教师号,姓名,职称)主键教师号-- 联系表m:n 联系选课(学号,课程号,成绩)主键(学号,课程号)外键学号 引用 学生(学号)外键课程号 引用 课程(课程号)4.3 优化规范化检查转换后检查每个关系模式是否满足 3NF/BCNF学生表学号 → 班号 → 班级名不班级名不在学生表中。如果学生表有班级名则有传递依赖需要拆分。课程表课程号 → 教师号 → 教师名如果课程表有教师名需要拆分。实际转换时实体和联系的属性只放直接相关的不要顺手冗余其他实体的属性。五、物理结构设计让数据库跑得更快物理设计关注数据在磁盘上的存储方式和访问效率。5.1 索引设计-- 主键自动创建索引聚簇索引-- 为外键创建索引加速 JOINCREATEINDEXidx_学生_班号ON学生(班号);CREATEINDEXidx_课程_教师号ON课程(教师号);-- 为常用查询条件创建索引CREATEINDEXidx_学生_姓名ON学生(姓名);-- 组合索引CREATEINDEXidx_选课_学号课程号ON选课(学号,课程号);索引不是越多越好每个索引都会增加写入开销需要根据查询模式权衡。5.2 存储引擎选择MySQL引擎特点适用场景InnoDB支持事务、外键、行级锁绝大多数场景默认推荐MyISAM查询快、不支持事务只读报表、日志已不推荐Memory数据存内存极快临时数据、缓存重启丢失5.3 分区与分表当单表数据量超过千万时考虑水平分区按范围或哈希把数据分散到多个物理文件垂直拆分把宽表的列拆到多个表读写分离主库写从库读六、动手练习练习 1设计图书馆管理系统需求管理读者读者号、姓名、类型、可借数量管理图书图书号、书名、作者、出版社、价格、库存管理借阅借阅号、读者、图书、借出日期、应还日期、实际归还日期管理管理员工号、姓名、职位任务画 E-R 图转换为关系模式指出主键和外键设计必要的索引练习 2电商订单系统需求用户管理用户ID、用户名、密码、邮箱、地址、注册时间商品管理商品ID、商品名、分类、价格、库存、描述订单管理订单ID、用户、下单时间、状态、总金额订单明细订单ID、商品ID、数量、单价支付记录支付ID、订单ID、支付方式、金额、支付时间任务找出所有 m:n 联系画出 E-R 图转换为 3NF 关系模式分析哪些字段需要索引练习 3判断设计正误以下关系模式是否有问题如何改进员工(员工号, 姓名, 部门号, 部门名, 项目号, 项目名, 参与时间, 职责)七、常见误区与避坑指南误区正确理解“E-R 图直接就是数据库表”E-R 图是概念模型需要按规则转换为关系模式“1:n 联系也单独建表”1:n 只需在 n 端加外键不需要独立表“m:n 联系可以在一张表里存两个外键”m:n 必须独立建表不能只存两个外键在某一端“物理设计就是选存储引擎”还包括索引、分区、文件组织、内存配置等“设计一次完成不需要迭代”数据库设计需要反复验证、测试、优化八、下篇预告下一篇我们将学习事务与并发控制——当多个用户同时操作数据库时如何保证数据的一致性和正确性这是数据库从单机走向生产环境必须掌握的核心知识。