MySQL 系统学习 第三阶段：数据库设计（Database Design）

一、什么是数据库设计数据库设计根据业务需求合理规划数据库中的表、字段、关系和约束使数据能够正确、高效、安全地存储。简单理解SQL 是怎么查数据。数据库设计是数据应该怎么存。例如需求一个用户可以购买多个商品。初学者可能设计user表 id name product1 product2 product3问题如果用户买 100 个商品增加字段product4 product5 ...明显不合理。正确设计用户表user id name订单表order id user_id商品表product id name price订单详情order_item order_id product_id count通过关系连接。二、数据库设计核心流程企业设计数据库一般流程需求分析 ↓ 概念设计 ↓ 逻辑设计 ↓ 物理设计 ↓ 创建数据库表 ↓ 优化维护三、需求分析先不要急着建表。先分析业务。例如做一个商城系统。需要知道有哪些对象用户属性用户ID 用户名 手机号 密码 注册时间商品属性商品ID 商品名称 价格 库存 分类订单属性订单ID 订单编号 购买用户 订单金额 创建时间这些对象就是后面的实体Entity四、实体设计Entity实体可以独立存在并且需要保存的数据对象。例如电商系统用户 User 商品 Product 订单 Order 支付 Payment每个实体最终通常对应一张表。例如User ↓ user表 Product ↓ product表五、字段设计Attribute一个实体包含属性。例如用户User id username password phone create_time对应CREATE TABLE user( id BIGINT PRIMARY KEY, username VARCHAR(50), password VARCHAR(255), phone VARCHAR(20), create_time DATETIME );六、表关系设计重点数据库最重要的是表与表之间如何关联。主要有三种关系。1. 一对一1 : 1一个用户对应一个身份证。关系User 1 -------- 1 IDCard设计user:id nameid_card:id user_id number应用场景用户信息和详细资料用户和实名认证信息2. 一对多1 : N⭐⭐⭐⭐⭐企业最常见。例如一个用户有多个订单。User 1 | | N Order设计userid nameorderid user_id price关键外键放在多的一方。所以订单表user_id而不是用户表order_id1 order_id23. 多对多N : N⭐⭐⭐⭐⭐例如学生选课程。Student N | | N Course不能直接保存。需要中间表student_course结构student_id course_id例如学生张三课程数学 英语中间表张三 数学 张三 英语七、数据库三大范式 ⭐⭐⭐⭐⭐范式解决如何减少数据冗余。第一范式1NF要求字段必须不可再分。错误address 中国 上海 浦东问题无法单独查询城市。正确country city area第二范式2NF要求非主键字段必须完全依赖主键。主要针对联合主键。例如订单商品表order_id product_id product_name问题product_name 只依赖 product_id。应该拆商品表product id name订单详情order_id product_id第三范式3NF要求非主键字段不能依赖其他非主键字段。例如错误user id name department_id department_name问题department_name 依赖 department_id。应该user:id name department_iddepartment:id name八、主键设计主键唯一标识一条数据。例如id BIGINT PRIMARY KEY要求唯一不为空稳定常见设计自增IDid BIGINT AUTO_INCREMENT优点简单。UUID例如550e8400-e29b优点分布式唯一。企业常用BIGINT 雪花算法九、外键设计外键保证表之间关系正确。例如订单user_id必须存在user.id作用防止订单属于不存在的用户但是企业项目中很多互联网公司不一定真正创建外键。原因高并发性能考虑分库分表困难更多使用应用层保证数据一致性。十、字段设计规范企业常见字段主键id创建时间create_time更新时间update_time删除标记软删除deleted例如0 正常 1 删除为什么不用DELETE因为数据可能需要恢复。十一、企业常见表设计案例用户表user id username password phone status create_time update_time角色权限RBACuser role permission user_role role_permission关系用户 | 角色 | 权限电商系统user product category order order_item payment关系用户 | 订单 | 订单详情 | 商品十二、数据库设计核心思想总结1.不要根据页面设计表。错误一个页面一张表应该根据业务实体设计。2.不要为了减少表数量强行合并。3.不要一开始考虑性能。先保证数据正确结构合理优化交给索引缓存分库分表第三阶段总结你需要掌握内容重要程度实体设计⭐⭐⭐⭐⭐字段设计⭐⭐⭐⭐⭐一对多关系⭐⭐⭐⭐⭐多对多关系⭐⭐⭐⭐⭐三大范式⭐⭐⭐⭐⭐主键设计⭐⭐⭐⭐外键设计⭐⭐⭐⭐软删除⭐⭐⭐⭐RBAC设计⭐⭐⭐⭐⭐ 第三阶段核心一句话数据库设计的本质是把现实业务中的对象和关系转换成合理的数据结构。