数据库设计范式

关忆北.

发布于 2023-12-01 10:41:29

1990

发布于 2023-12-01 10:41:29

文章被收录于专栏：关忆北.关忆北.

摘要：

数据库设计是构建可靠和高效系统的关键步骤之一。设计范式是一种规范，它帮助开发人员减少数据冗余、提高数据一致性和完整性。本文将探讨数据库设计范式的重要性，并通过基于MySQL的表设计示例来佐证其应用。

引言：

数据库是现代应用程序不可或缺的一部分，而良好的数据库设计能够为系统的可靠性和性能提供坚实基础。在数据库设计过程中，遵循设计范式是一种重要的指导原则，它有助于我们规范和优化数据结构。

第一范式（1NF）

第一范式正例

第一范式要求每个表中的字段都是原子性的，不允许多个值混合在一起。 考虑一个用户表的例子：

CREATE TABLE user (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100),
  registration_time TIMESTAMP,
  ...
);

以上将用户的用户名、邮箱、注册时间分别放在了不同的字段中，遵循了数据库设计的第一范式。

第一范式反例

假设我们有一个存储订单信息的表，其中包含了产品名称和产品属性：

CREATE TABLE order (
  order_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  product_attributes VARCHAR(200),
  quantity INT,
  total_price DECIMAL(10, 2),
  ...
);

在这个例子中，product_attributes 列可能包含多个产品属性，比如颜色、尺寸等，这就违反了第一范式的要求。为了符合第一范式，应该将产品属性拆分成单独的列，或者将其存储在另外的表中，为方便查询可以使order表和order_attributes表id一致或两表使用order_id关联字段关联。

修正

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  product_name VARCHAR(100),
  quantity INT,
  total_price DECIMAL(10, 2),
  ...
);

CREATE TABLE product_attributes (
  order_id INT PRIMARY KEY,
  attribute_id INT,
  attribute_name VARCHAR(50),
  attribute_value VARCHAR(50)
);

第二范式（2NF）

第二范式要求非主键属性完全依赖于主键。

换句话说，非主键属性的取值必须与主键的所有列的取值相关，而不能只与主键的某一部分相关。

第二范式正例

CREATE TABLE author (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(50),
  ...
);

CREATE TABLE article (
  article_id INT PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  publish_time TIMESTAMP,
  author_id INT,
  ...
);

在这个例子中，作者ID（author_id）直接依赖于文章ID（article_id），与文章ID一起构成了完整的主键。这样可以确保非主键属性（例如作者名称）与整个主键相关，满足第二范式的要求。

第二范式反例

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(50),
  department_id INT,
  department_name VARCHAR(50),
  salary DECIMAL(10, 2),
  ...
);

在这个例子中，department_name 字段依赖于 department_id 字段，而不是完全依赖于主键 employee_id。这就违反了第二范式，因为非主键属性 department_name 只依赖于部分主键，而不是整个主键。

这种设计，同样违反了设计模式的原则中的单一职责原则，一个表的字段应该尽量保持单一职责，即每个字段应该只存储一个特定的数据信息。

修正

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(50),
  department_id INT,
  salary DECIMAL(10, 2),
  ...
);

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50),
  ...
);

第三范式（3NF）

第三范式是确保每个非主键属性都不传递依赖于其他非主键属性的规范。

这意味着我们需要将每个非主键属性都拆分成独立的表，以避免数据冗余。例如，在一个用户表中，用户的地址应该作为独立的表，而不是在用户表中重复出现。

例如，在一个用户表中，用户的地址应该作为独立的表，而不建议用户表中出现，因为当用户表中字段较多时，在用户表存储用户的详细地址信息，如：nation、province、city、district、postcode、longitude、latitude，会导致数据表功能不清晰，违反单一职责设计。

第三范式正例

CREATE TABLE user (
  user_id INT PRIMARY KEY,
  username VARCHAR(50),
  ...
);

CREATE TABLE addresses (
  address_id INT PRIMARY KEY,
  user_id INT,
  address_line VARCHAR(100),
  city VARCHAR(50),
  ...
);

第三范式反例

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_name VARCHAR(50),
  customer_email VARCHAR(50),
  product_name VARCHAR(50),
  product_price DECIMAL(10, 2),
  ...
);