数据库三大范式详解

 一、范式核心思想

设计目标：通过结构化拆分，消除数据冗余和异常，同时保证数据完整性 

平衡原则：范式化越高，冗余越少，但查询可能需更多JOIN（实际设计需权衡）

二、三大范式逐层解析

1. 第一范式（1NF）：原子性

规则：每个字段必须是**不可再分**的最小数据单元 

违反案例： 

错误设计（多值存储在一个字段）

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    products VARCHAR(200)  -- 存储"手机,耳机,充电宝"
);

问题：无法单独查询/统计某个产品的订单 

1NF改造： 

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(50),  -- 每个产品单独记录
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

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

规则：所有非主键字段必须**完全依赖**整个主键（针对联合主键） 

违反案例： 

 错误设计（课程名称仅依赖课程ID，与学号无关）

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    course_name VARCHAR(50),  -- 部分依赖
    PRIMARY KEY (student_id, course_id)
);

问题：同一课程被重复存储，修改时可能不一致 

2NF改造： 

 拆分为两个表

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50)
);

CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

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

规则：非主键字段之间**不能有依赖关系** 

违反案例： 

 错误设计（学院地址依赖学院ID，而非直接依赖学生ID）

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    dept_id INT,
    dept_address VARCHAR(100)  
);

问题：同一学院地址重复存储，修改需更新多行 

3NF改造： 

CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_address VARCHAR(100)
);

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

三、真实业务场景分析

案例：电商订单系统设计 

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    PRODUCT }|--|{ ORDER_ITEM : refers
    PRODUCT {
        int product_id PK
        varchar name
        decimal price
    }
    ORDER_ITEM {
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price  -- 反范式：价格快照
    }

范式应用说明： 

1. 1NF：`order_item`中每个商品独立成行 

2. 2NF：`unit_price`冗余存储（违反但合理，需保留历史价格） 

3. 3NF：`product`表拆分，避免`product_name`重复 

四、反范式化实践

何时需要打破范式？ 

高频查询：订单总金额预计算存储 

统计分析：商品销量计数器冗余 

历史追溯：订单快照保留下单时价格 

示例： 

允许冗余的订单总金额

ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2);

通过触发器自动维护

CREATE TRIGGER update_order_total 
AFTER INSERT ON order_items
FOR EACH ROW 
UPDATE orders SET total_amount = (
    SELECT SUM(quantity * unit_price) 
    FROM order_items 
    WHERE order_id = NEW.order_id
) WHERE order_id = NEW.order_id;

五、快速自查表