数据库逻辑结构设计阶段详解

 一、阶段定位

逻辑结构设计是数据库设计的关键阶段，位于概念设计（E-R图）之后、物理设计之前，主要任务是将概念模型转换为具体的**关系模型**（即表结构），并确保其符合规范化要求。

二、核心工作步骤

1. E-R图转关系模式 

   实体转换：每个实体转为一张表 

     [学生] → STUDENT(student_id, name, gender)

关系转换： 

      1:1关系：合并表或外键互嵌 

      1:N关系：在多方表加外键 

       [班级] 1:N [学生] → STUDENT(class_id)  # 外键

      M:N关系：生成关联表 

       [学生] M:N [课程] → ENROLLMENT(student_id, course_id, grade)

2. 规范化处理（Normalization） 

  第一范式（1NF）：消除重复字段 

     STUDENT(course1, course2)  # 违反1NF

     + ENROLLMENT(student_id, course_id)  # 符合1NF

  第二范式（2NF）：消除部分依赖 

      错误示例：订单明细依赖订单ID和产品ID（产品名称仅依赖产品ID）

     ORDER_DETAIL(order_id, product_id, product_name) → 拆分为：

     ORDER_DETAIL(order_id, product_id) + PRODUCT(product_id, name)

  第三范式（3NF）：消除传递依赖 

      错误示例：学生表包含学院名称（通过学院ID传递）

     STUDENT(student_id, dept_id, dept_name) → 拆分为：

     STUDENT(student_id, dept_id) + DEPARTMENT(dept_id, name)

3. 反范式化权衡 

   典型场景： 

      高频查询：允许冗余（如订单总金额） 

     ORDERS(order_id, total_amount)  # 冗余但避免实时计算

      统计分析：预聚合字段（如商品销量计数器） 

4. 完整性约束设计 

  主键：自增ID/UUID/复合主键 

   外键：ON DELETE CASCADE/SET NULL规则 

  业务约束：CHECK约束（如`age >= 18`）

三、关键交付物

四、常见问题与解决方案

1. 多值属性处理 

    方案：拆分为关联表 

    错误：学生有多个电话号码存储在一列

   STUDENT(phone: "138,159") →

   STUDENT_PHONE(student_id, phone_num)

2. 继承关系转换 

    方案： 

     单表继承：所有子类字段放主表（含类型标识） 

     类表继承：父表+子表（外键关联） 

3. 历史数据存储 

    方案：添加时间维度字段 

   PRODUCT_PRICE(product_id, price, valid_from, valid_to)

五、工具推荐

1. 设计工具： 

    MySQL Workbench（逆向工程） 

    PowerDesigner（逻辑模型生成DDL） 

2. 验证工具： 

    使用`EXPLAIN`分析查询路径 

    数据生成器测试约束有效性