跳到主要内容

MySQL 数据库范式

第一范式 1NF​

数据库表的每个字段都必须是原子性的，不可再分。在数据库设计中，一个字段只能包含一个值，不能存储多个相关信息。

在员工信息表中，应该将联系方式拆分为手机号码和固定电话两个独立字段，而不是将两者合并存储在同一字段中。

-- 不符合1NF的表设计
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    contact_info VARCHAR(255) -- 包含手机和固定电话
);

-- 符合1NF的表设计
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    mobile_phone VARCHAR(20),
    landline_phone VARCHAR(20)
);

最佳实践

  • 确保每个字段只存储一个数据项，避免使用逗号分隔或其他方式在单个字段中存储多个值。
  • 使用适当的数据类型定义字段，确保数据的完整性和准确性。

第二范式 2NF​

建立在第一范式基础上，要求表中的非主键字段必须完全依赖于主键。换句话说，消除部分依赖。

在订单明细表中，订单 ID 和商品 ID 构成复合主键，商品数量完全依赖于这个复合主键。但商品价格只依赖于商品 ID，这就不满足第二范式，需要将商品价格移到商品表中。

复合主键由多个字段组合构成，用于唯一标识数据库表中的一条记录。在使用复合主键时，必须确保所有非主键字段都完全依赖于整个复合主键，而不是部分依赖。

-- 不符合2NF的表设计
CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    product_price DECIMAL(10,2),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 符合2NF的表设计
CREATE TABLE order_details (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

最佳实践

  • 分离表中的数据，确保每个表只包含与主键完全相关的字段。
  • 避免在一个表中存储与多个主键部分相关的数据，采用适当的表拆分。

第三范式 3NF​

建立在第二范式基础上，要求所有非主键字段之间不存在传递依赖关系。也就是说，非主键字段不能依赖于其他非主键字段。

在员工表中，员工 ID 作为主键，部门 ID 和部门名称都作为非主键字段。由于部门名称依赖于部门 ID，这种传递依赖违反了第三范式。应该将部门相关信息独立成部门表。

-- 不符合3NF的表设计
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    dept_id INT,
    dept_name VARCHAR(100),
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 符合3NF的表设计
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    dept_name VARCHAR(100)
);

最佳实践

  • 将表中不直接依赖于主键的字段拆分到其他表中，消除传递依赖。
  • 通过外键关联不同表，维护数据的一致性和完整性。

第四范式 4NF​

建立在第三范式基础上，要求表中不存在多值依赖。也就是说，一个表中的多值属性不应依赖于其他非主键属性。

数据库表中的每个非主键字段都不能依赖于其他非主键字段的任何子集。这要求表中的每个非主键字段都必须直接依赖于所有候选键。

-- 不符合4NF的表设计
CREATE TABLE student_courses (
    student_id INT PRIMARY KEY,
    course_id VARCHAR(10),
    hobby VARCHAR(50)
);

-- 假设一个学生可以选修多门课程和拥有多个爱好，这导致了多值依赖。

-- 符合4NF的表设计
CREATE TABLE student_courses (
    student_id INT,
    course_id VARCHAR(10),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

CREATE TABLE student_hobbies (
    student_id INT,
    hobby VARCHAR(50),
    PRIMARY KEY (student_id, hobby),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

最佳实践

  • 分离具有多值依赖的字段到不同的表中，避免数据冗余和更新异常。
  • 使用复合主键或唯一约束来确保数据的唯一性和完整性。

第五范式 5NF​

建立在第四范式基础上，要求消除表中的连接依赖。即数据库表中的每个事实必须完全独立，不能通过连接其他表来推导。

数据库表中的每个事实都必须完全独立，不能通过连接其他表来推导。这种设计将数据分解为更小的关系表，每个表包含独立的事实，减少了数据冗余，提高了数据的灵活性和可维护性。

-- 不符合5NF的表设计
CREATE TABLE project_assignments (
    project_id INT,
    employee_id INT,
    equipment_id INT,
    PRIMARY KEY (project_id, employee_id, equipment_id)
);

-- 假设一个项目可以分配多个员工和多种设备，且设备分配与员工无关，导致连接依赖。

-- 符合5NF的表设计
CREATE TABLE project_employees (
    project_id INT,
    employee_id INT,
    PRIMARY KEY (project_id, employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

CREATE TABLE project_equipment (
    project_id INT,
    equipment_id INT,
    PRIMARY KEY (project_id, equipment_id),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (equipment_id) REFERENCES equipment(id)
);

最佳实践

  • 将复杂的多值依赖和连接依赖拆分到不同的表中，确保每个表只存储独立的事实。
  • 使用外键约束维护不同表之间的关联，确保数据的一致性和完整性。

综合示例​

以下是一个综合示例，展示如何应用 1NF 到 5NF 进行数据库设计，确保数据的规范化和完整性。

-- 创建数据库
CREATE DATABASE IF NOT EXISTS company_db;
USE company_db;

-- 创建部门表
CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

-- 创建员工表，符合3NF
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 创建项目表
CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_name VARCHAR(100) NOT NULL
);

-- 创建设备表
CREATE TABLE equipment (
    id INT AUTO_INCREMENT PRIMARY KEY,
    equipment_name VARCHAR(100) NOT NULL
);

-- 创建学生课程表，符合4NF
CREATE TABLE student_courses (
    student_id INT,
    course_id VARCHAR(10),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- 创建学生爱好表，符合4NF
CREATE TABLE student_hobbies (
    student_id INT,
    hobby VARCHAR(50),
    PRIMARY KEY (student_id, hobby),
    FOREIGN KEY (student_id) REFERENCES students(id)
);

-- 创建项目员工关联表，符合5NF
CREATE TABLE project_employees (
    project_id INT,
    employee_id INT,
    PRIMARY KEY (project_id, employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (employee_id) REFERENCES employees(id)
);

-- 创建项目设备关联表，符合5NF
CREATE TABLE project_equipment (
    project_id INT,
    equipment_id INT,
    PRIMARY KEY (project_id, equipment_id),
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (equipment_id) REFERENCES equipment(id)
);

-- 插入部门数据
INSERT INTO departments (dept_name) VALUES
('研发部'),
('市场部'),
('人事部');

-- 插入员工数据
INSERT INTO employees (name, dept_id) VALUES
('张三', 1),
('李四', 2),
('王五', 1),
('赵六', 3);

-- 插入项目数据
INSERT INTO projects (project_name) VALUES
('项目A'),
('项目B');

-- 插入设备数据
INSERT INTO equipment (equipment_name) VALUES
('服务器'),
('笔记本电脑');

-- 关联项目与员工
INSERT INTO project_employees (project_id, employee_id) VALUES
(1, 1),
(1, 3),
(2, 2);

-- 关联项目与设备
INSERT INTO project_equipment (project_id, equipment_id) VALUES
(1, 1),
(1, 2),
(2, 2);

创建数据库和表结构​

departments 表存储部门信息。

employees 表存储员工信息，并通过 dept_id 外键关联到 departments 表。

projects 表存储项目信息。

equipment 表存储设备信息。

project_employees 和 project_equipment 表分别用于关联项目与员工、项目与设备，符合 5NF，消除了连接依赖。

插入数据​

各表中插入初始数据，确保数据的完整性和关联性。

规范化设计​

每个表都符合相应的范式，从 1NF 到 5NF，确保数据结构的合理性，减少冗余，提高查询效率。

最佳实践​

在设计数据库时，按照范式逐步规范化，确保数据的完整性和一致性。

定期审查和优化数据库结构，必要时进行反规范化以提升查询性能。

使用外键约束维护表之间的关系，确保数据的参照完整性。

编辑此页
最后由 sumingcheng 于 更新