数据库构建中的三范式设计（附SQL实例说明）

数据库构建中的三范式（附SQL实例说明）

设计数据库时遵循三范式（1NF、2NF、3NF）是关系型数据库设计中用于减少数据冗余、提高数据一致性的理论基础。

第一范式：任何一张表必须有主键，每个字段原子性不可再分；最核心的要求

第二范式：建立在第一范式基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖；

第三范式：建立在第二范式基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。

个人总结：

多对多，三张表，关系表，两外键

一对多，两张表，多的表，加外键

一对一，外键唯一

以下通过代码示例来分别展示这三个范式的设计原则：

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

问题表（非1NF）：

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    CustomerAddress VARCHAR(255),
    OrderItems TEXT -- JSON格式存储多个订单项
);

在这个例子中，OrderItems列以JSON格式存储多个订单项，违反了1NF，因为一个字段包含了多个值（订单项列表），不具备原子性。

修正后的1NF表：

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT
);

CREATE TABLE OrderItems (
    ItemID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    Price DECIMAL(10, 2)
);

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(255)
);

ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
ALTER TABLE OrderItems ADD FOREIGN KEY (OrderID) REFERENCES Orders(OrderID);

现在，数据被分解成三个表：Orders、OrderItems和Customers。每个表的每个字段都只包含一个不可再分的值，满足了第一范式的要求。

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

问题表（非2NF）：

CREATE TABLE CustomersOrders (
    CustomerID INT,
    OrderID INT,
    CustomerName VARCHAR(100),
    CustomerAddress VARCHAR(255),
    OrderDate DATE,
    PRIMARY KEY (CustomerID, OrderID)
);

此表中，CustomerName和CustomerAddress依赖于CustomerID，而不是整个主键(CustomerID, OrderID)。这意味着当一个顾客有多个订单时，这些顾客信息会被重复存储。

修正后的2NF表：

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Address VARCHAR(255)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

将CustomersOrders表拆分为Customers和Orders两个表，每个表都具有单一主键，且非主键字段直接依赖于各自的主键，消除了部分依赖，符合第二范式。

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

问题表（非3NF）：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    DepartmentManagerID INT,
    ManagerName VARCHAR(100)
);

在此表中，ManagerName依赖于DepartmentManagerID，而DepartmentManagerID又依赖于DepartmentID。这种依赖关系是通过DepartmentID间接传递的，违反了第三范式。

修正后的3NF表：

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(100),
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentManagerID INT,
    FOREIGN KEY (DepartmentManagerID) REFERENCES Employees(EmployeeID)
);

CREATE TABLE Managers (
    ManagerID INT PRIMARY KEY,
    Name VARCHAR(100)
);

ALTER TABLE Departments ADD FOREIGN KEY (DepartmentManagerID) REFERENCES Managers(ManagerID);

将Employees表拆分为Employees、Departments和Managers三个表。现在，每个非主键字段都直接依赖于各自表的主键，不存在通过其他非主键字段传递依赖的情况，符合第三范式。

通过上述代码示例，可以看到如何根据三范式的要求逐步对数据模型进行规范化设计，以减少冗余、增强数据一致性。在实际应用中，应根据业务需求和性能权衡，适当调整规范化程度，可能采用BCNF、第四范式（4NF）甚至反规范化（denormalization）等策略。

相关检索内容附上：

数据库的三范式是什么?-知乎

数据库设计之三大范式及举例说明-CSDN技术社区

数据库设计三范式_科技代码

数据库设计的三大范式(举例详解)-CSDN技术社区

数据库设计之三大范式-代码天地

数据库设计之三大范式-代码天地

关于数据库三大范式的理解-哔哩哔哩

了解更多知识请戳下：

@Author:懒羊羊