【数据库】（3）数据库第三范式（3NF）深度剖析

前进之路  论坛元老 | 2025-4-26 11:48:09 | 来自手机 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 1587|帖子 1587|积分 4761

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有账号？立即注册

x
数据库第三范式（3NF）终极指南

一、第三范式焦点原理

1.1 基本定义

在满意第二范式（2NF）的底子上，消除非主属性对候选键的转达依赖
1.2 关键概念剖析



  • 转达依赖：若A→B且B→C，则称C转达依赖于A
  • 直接依赖：非主属性必须直接依赖候选键
  • 非主属性：不属于任何候选键的属性
1.3 违反3NF的典型场景

  1. 学生成绩表（不符合3NF）
  2. +----------+----------+--------+------------+-----------------+
  3. | 学号(PK) | 课程号(PK)| 成绩  | 学院名称   | 学院院长        |
  4. +----------+----------+--------+------------+-----------------+
  5. | 2023001  | C001     | 85     | 计算机学院 | 张伟民          |
  6. | 2023001  | C002     | 92     | 计算机学院 | 张伟民          |
  7. +----------+----------+--------+------------+-----------------+
复制代码
问题分析：


  • 学院院长 → 学院名称 → 学号（转达依赖）
  • 同一学院院长信息重复存储
二、企业级实战案例

2.1 电商订单系统优化

原始表结构：
  1. 订单表（不符合3NF）
  2. +----------+------------+-----------+------------+----------+---------------+
  3. | 订单号(PK)| 用户ID    | 用户等级  | 商品ID(PK) | 商品价格 | 商品供应商    |
  4. +----------+------------+-----------+------------+----------+---------------+
  5. | O1001    | U8801      | VIP3      | P001       | 5999     | 苹果公司      |
  6. | O1001    | U8801      | VIP3      | P005       | 199      | 小米科技      |
  7. +----------+------------+-----------+------------+----------+---------------+
复制代码
规范化步骤：

  • 分离用户等级体系
  1. CREATE TABLE users (
  2.   user_id VARCHAR(10) PRIMARY KEY,
  3.   level VARCHAR(10) NOT NULL
  4. );
  6. CREATE TABLE user_levels (
  7.   level VARCHAR(10) PRIMARY KEY,
  8.   discount_rate DECIMAL(5,2)
  9. );
复制代码

  • 拆分供应商信息
  1. CREATE TABLE suppliers (
  2.   supplier_id VARCHAR(10) GENERATED ALWAYS AS (
  3.     CASE
  4.       WHEN supplier_name = '苹果公司' THEN 'S001'
  5.       WHEN supplier_name = '小米科技' THEN 'S002'
  6.     END
  7.   ) STORED PRIMARY KEY,
  8.   supplier_name VARCHAR(50) UNIQUE NOT NULL
  9. );
  11. CREATE TABLE products (
  12.   product_id VARCHAR(10) PRIMARY KEY,
  13.   price DECIMAL(10,2),
  14.   supplier_id VARCHAR(10),
  15.   FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id)
  16. );
复制代码
2.2 人力资源管理系统改造

原始员工表：
  1. 员工表（不符合3NF）
  2. +----------+----------+------------+-----------+---------------+
  3. | 员工ID   | 部门编号 | 部门名称   | 部门预算  | 部门所在地    |
  4. +----------+----------+------------+-----------+---------------+
  5. | E001     | D01      | 研发部     | 5000000   | 上海浦东      |
  6. | E002     | D02      | 市场部     | 3000000   | 北京朝阳      |
  7. +----------+----------+------------+-----------+---------------+
复制代码
规范化方案：
  1. -- 部门维度表
  2. CREATE TABLE departments (
  3.   dept_id VARCHAR(10) PRIMARY KEY,
  4.   dept_name VARCHAR(30) UNIQUE NOT NULL,
  5.   budget DECIMAL(12,2),
  6.   location VARCHAR(50)
  7. );
  9. -- 精简员工表
  10. CREATE TABLE employees (
  11.   emp_id VARCHAR(10) PRIMARY KEY,
  12.   dept_id VARCHAR(10),
  13.   FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
  14. );
复制代码
三、依赖关系分析方法

3.1 函数依赖图谱

     3.2 转达依赖判断公式

  1. 存在函数依赖：
  2. A → B → C
  3. 则：
  4. A → C 是传递依赖
  5. 当且仅当：
  6. B ↛ A（B不能决定A）
复制代码
3.3 典型转达依赖模式

依赖链问题类型办理方案学号 → 学院 → 院长信息重复创建学院维度表订单 → 客户 → 区域区域统计困难建立客户-区域映射表产物 → 类别 → 税率税率更新复杂单独税率设置表 四、性能优化平衡计谋

4.1 答应违反3NF的场景

场景示例优化方案及时盘算要求高订单总金额表现冗余存储金额字段数据堆栈维度表星型模型中的维度表适当生存冗余描述字段高频读取低频更新商品分类信息反规范化存储 4.2 索引优化方案

  1. -- 覆盖索引优化
  2. CREATE INDEX idx_emp_dept
  3. ON employees(dept_id) INCLUDE (emp_id);
  5. -- 函数索引优化
  6. CREATE INDEX idx_supplier_name
  7. ON suppliers(UPPER(supplier_name));
复制代码
五、实战练习题

题目1：改造图书借阅系统
  1. 借阅记录表（不符合3NF）
  2. +----------+----------+------------+------------------+---------------+
  3. | 借阅ID   | 图书ID   | 图书类别   | 读者ID          | 读者单位      |
  4. +----------+----------+------------+------------------+---------------+
  5. | L2023001 | B001     | 计算机     | R002            | 清华大学      |
  6. | L2023002 | B005     | 文学       | R005            | 北京大学      |
  7. +----------+----------+------------+------------------+---------------+
复制代码
题目2：优化物流管理系统
  1. 运单表（不符合3NF）
  2. +----------+------------+--------------+----------------+---------------+
  3. | 运单号   | 快递公司   | 公司联系电话 | 收件人ID       | 收件人所属省  |
  4. +----------+------------+--------------+----------------+---------------+
  5. | YD23001  | SF         | 95338        | J23001         | 广东省        |
  6. | YD23002  | YTO        | 95554        | J23005         | 浙江省        |
  7. +----------+------------+--------------+----------------+---------------+
复制代码
参考答案：
  1. -- 题目1解决方案
  2. CREATE TABLE books (
  3.   book_id VARCHAR(10) PRIMARY KEY,
  4.   category VARCHAR(20)
  5. );
  7. CREATE TABLE readers (
  8.   reader_id VARCHAR(10) PRIMARY KEY,
  9.   institution VARCHAR(50)
  10. );
  12. CREATE TABLE borrow_records (
  13.   borrow_id VARCHAR(10) PRIMARY KEY,
  14.   book_id VARCHAR(10),
  15.   reader_id VARCHAR(10)
  16. );
  18. -- 题目2解决方案
  19. CREATE TABLE couriers (
  20.   company_code VARCHAR(10) PRIMARY KEY,
  21.   company_name VARCHAR(50),
  22.   contact_tel VARCHAR(20)
  23. );
  25. CREATE TABLE recipients (
  26.   recipient_id VARCHAR(10) PRIMARY KEY,
  27.   province VARCHAR(20)
  28. );
  30. CREATE TABLE waybills (
  31.   waybill_no VARCHAR(20) PRIMARY KEY,
  32.   company_code VARCHAR(10),
  33.   recipient_id VARCHAR(10)
  34. );
复制代码
六、扩展学习资源

6.1 保举工具



  • Normalization Checker：在线范式验证工具（https://www.database-normalizer.com）
  • pgAdmin：PostgreSQL的范式分析插件
6.2 经典文献



  • 《Database System Concepts》第7章：关系数据库计划
  • Microsoft SQL Server 最佳实践白皮书《Normalization Guidelines》
6.3 项目实战库



  • GitHub堆栈：https://github.com/normalization-labs/3nf-examples
  • Kaggle数据集：Amazon Sales Data（适合进行规范化练习）
6.4 视频课程



  • Udemy《Database Design Fundamentals》第5章
  • YouTube频道「TechTFQ」规范化专题（含实战演示）
七、常见错误排查指南

错误征象1：循环依赖

症状：
  1. 部门表：dept_id → manager_id
  2. 员工表：emp_id → dept_id
  3. 经理表：manager_id → emp_id
复制代码
办理方案：


  • 冲破循环：在部分表中直接存储经理姓名
  • 添加束缚：确保经理属于对应部分
错误征象2：过分规范化

症状：


  • 简单的用户表被拆分成10个关联表
  • 必要5次JOIN才能获取完备信息
    办理方案：
  • 归并低频变更字段
  • 使用JSON字段存储扩展属性
错误征象3：忽略业务束缚

症状：


  • 答应同一商品在差别订单中有差别代价
  • 但业务要求代价必须同一
    办理方案：
  1. ALTER TABLE order_details
  2. ADD CONSTRAINT price_consistency
  3. CHECK (price = (SELECT price FROM products WHERE product_id = order_details.product_id));
复制代码
八、范式演进与工程实践

8.1 现代数据库计划趋势

计划模式实用场景3NF应用微服务架构服务独立数据库在服务内保持3NF数据湖架构原始数据存储不逼迫规范化及时数仓流式数据处置惩罚适当反规范化 8.2 云原生数据库实践



  • AWS Aurora：使用Read Replica处置惩罚规范化表的查询压力
  • Google Spanner：在分布式数据库中实现参照完备性
  • Azure CosmosDB：使用反规范化计划优化文档存储
九、企业口试重点

9.1 高频考题


  • 解释什么是转达依赖？
  • 如何证明一个表计划符合3NF？
  • 什么时间应该故意违反第三范式？
9.2 参考答案示例

问题：订单表包含客户所在城市是否违反3NF？
回答：


  • 如果存在 订单ID → 客户ID → 客户城市 的依赖链
  • 且客户ID ↛ 订单ID（即客户可能有多笔订单）
  • 则属于转达依赖，违反3NF
  • 应拆分为客户信息表和订单表
十、下一步学习方向

     学习建议：

  • 使用DBngin搭建多版本数据库环境
  • 在GitHub上寻找开源项目的数据库计划分析
  • 尝试在MongoDB中实现关系型规范化
  • 学习使用dbt（data build tool）进行数据建模
十一、综合应用寻衅

实战项目：计划跨境电商系统数据库
  1. 需求清单：
  2. 1. 支持多币种价格存储
  3. 2. 记录商品的多级分类
  4. 3. 跟踪供应商的所在国家/地区
  5. 4. 管理用户的跨境配送地址
复制代码
计划要点：

  • 钱币汇率单独建表
  • 商品分类使用闭包表计划
  • 国家信息符合3NF标准
  • 地点信息采用半结构化存储
  1. -- 示范代码片段
  2. CREATE TABLE countries (
  3.   country_code CHAR(2) PRIMARY KEY,
  4.   country_name VARCHAR(50) NOT NULL,
  5.   currency_code CHAR(3) NOT NULL
  6. );
  8. CREATE TABLE exchange_rates (
  9.   base_currency CHAR(3),
  10.   target_currency CHAR(3),
  11.   rate DECIMAL(12,6),
  12.   PRIMARY KEY (base_currency, target_currency)
  13. );
  15. CREATE TABLE product_categories (
  16.   category_id INT PRIMARY KEY,
  17.   parent_id INT REFERENCES product_categories(category_id),
  18.   category_name VARCHAR(50) NOT NULL
  19. );
复制代码
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
继续阅读请点击广告
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

前进之路

论坛元老
这个人很懒什么都没写!

楼主热帖

标签云

国产数据库 集成商 AI 运维 CIO 存储 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表