数据库设计规范，我的数据库我做主

---

一、前言

从数据库理论的抽象角度看，数据库设计是指对于一个给定的应用环境，构造出某种数据库管理系统支持的优化的数据库模式，并据此建立数据库及其应用系统，使之能够有效地存储和管理数据，满足各种用户的应用需求（包括信息管理要求和数据处理要求）

在数据库表设计中，虽然没有固定的规定，但可以根据广泛接受的数据库设计原则和最佳实践来总结出一些关键规定。

二、数据库三大范式

1、第一范式（1NF）

所谓第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

说明：在任何一个关系数据库中，第一范式（1NF）是对关系模式的设计基本要求，一般设计中都必须满足第一范式（1NF）。不过有些关系模型中突破了1NF的限制，这种称为非1NF的关系模型。换句话说，是否必须满足1NF的最低要求，主要依赖于所使用的关系模型。

2、第二范式（2NF）

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分。选取一个能区分每个实体的属性或属性组，作为实体的唯一标识。例如在员工表中的身份证号码即可实现每个一员工的区分，该身份证号码即为候选键，任何一个候选键都可以被选作主键。在找不到候选键时，可额外增加属性以实现区分，如果在员工关系中，没有对其身份证号进行存储，而姓名可能会在数据库运行的某个时间重复，无法区分出实体时，设计辟如ID等不重复的编号以实现区分，被添加的编号或ID选作主键。（该主键的添加是在ER设计时添加，不是建库时随意添加）

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是在第一范式的基础上属性完全依赖于主键。

3、第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

第三范式（3NF）是第二范式（2NF）的一个子集，即满足第三范式（3NF）必须满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个关系中不包含已在其它关系已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性，也就是在满足2NF的基础上，任何非主属性不得传递依赖于主属性。

三、数据库设计常用规范

1、使用有意义的表名和列名

表名和列名应该清晰地描述它们所代表的数据或概念，使其他开发者能够轻松理解表的用途。

正面例子：
表名：customer
列名：first_name, last_name, email_address

反面例子：
表名：table1
列名：col1, col2, col3

2、 使用单数形式命名表

使用单数形式可以使表名更加简洁，并且与面向对象编程中的类命名惯例保持一致。

正面例子：order, product, customer
反面例子：orders, products, customers

3、 避免在表名中使用空格或特殊字符

使用空格或特殊字符可能会导致在SQL查询中需要额外的引号，增加出错的可能性。

正面例子：order_details, product_category
反面例子：order details, product-category

4、使用主键（Primary Key）

每个表都应该有一个唯一标识每条记录的主键，通常是一个自增的整数字段。
正面例子：

CREATE TABLE customer (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

反面例子：

CREATE TABLE customer (
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

5、正确使用数据类型

为每个列选择最合适的数据类型，既能确保数据的完整性，又能优化存储空间和查询性能。

正面例子：

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    created_at TIMESTAMP
);

反面例子：

CREATE TABLE product (
    product_id VARCHAR(50),
    product_name TEXT,
    price VARCHAR(20),
    created_at VARCHAR(50)
);

6、使用外键（Foreign Key）维护引用完整性

外键用于在表之间建立关系，确保引用的数据始终有效，并防止孤立的记录。

正面例子：

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

反面例子：

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    customer_id INT
);

7、规范化以减少数据冗余

通过将数据分解到多个相关表中，可以减少冗余并提高数据一致性。

正面例子：
将客户地址信息单独存储在一个表中

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE address (
    address_id INT PRIMARY KEY,
    customer_id INT,
    street VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50),
    FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

反面例子：
将所有信息存储在一个表中

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    street VARCHAR(100),
    city VARCHAR(50),
    country VARCHAR(50)
);

8、使用索引优化查询性能

在经常用于搜索、排序或连接的列上创建索引可以显著提高查询性能。

正面例子：

CREATE INDEX idx_last_name ON customer(last_name);

反面例子：
不为经常查询的列创建索引，导致全表扫描。

9、避免使用保留字作为表名或列名

使用SQL保留字可能导致语法错误或需要特殊处理。

正面例子：user_account, item_order
反面例子：user, order

10、使用一致的命名约定

在整个数据库中保持一致的命名风格可以提高可读性和可维护性。

正面例子：
全部使用小写和下划线：first_name, last_name, email_address
反面例子：
混合使用不同风格：firstName, LastName, Email_Address

11、为每个表添加创建和更新时间戳

这些时间戳字段有助于跟踪记录的创建和最后修改时间，对于审计和数据管理非常有用。
正面例子：

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

反面例子：
完全省略时间戳字段，或者手动更新时间字段，容易出错或忘记更新。

12、使用枚举类型或查找表来限制可能的值

对于有限集合的值，使用枚举类型或查找表可以确保数据的一致性和完整性。
正面例子：
使用枚举类型：

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered')
);

或使用查找表：

CREATE TABLE order_status (
    status_id INT PRIMARY KEY,
    status_name VARCHAR(20)
);

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    status_id INT,
    FOREIGN KEY (status_id) REFERENCES order_status(status_id)
);

反面例子：
使用普通的VARCHAR字段，允许任意值：

CREATE TABLE order (
    order_id INT PRIMARY KEY,
    status VARCHAR(20)
);

13、避免过度规范化

虽然规范化可以减少数据冗余，但过度规范化可能导致性能问题和复杂的查询。在某些情况下，适度的非规范化是可以接受的。

正面例子：
在订单表中保存订单总额，而不是每次都从订单明细中计算。

反面例子：
将每个属性都拆分到单独的表中，导致简单查询需要多次连接。

14、使用适当的字符集和排序规则

选择正确的字符集和排序规则可以确保正确处理多语言数据和排序。

正面例子：

CREATE TABLE customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

反面例子：
使用默认的字符集和排序规则，可能导致某些语言的字符无法正确存储或排序。

15、为大文本或二进制数据使用专门的数据类型

对于大型文本或二进制数据，使用专门的数据类型可以提高性能和管理效率。

正面例子：

CREATE TABLE document (
    document_id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    file_data MEDIUMBLOB
);

反面例子：
对所有数据都使用VARCHAR或BLOB，不考虑数据的实际大小和用途。

16、使用适当的约束（Constraints）

约束可以在数据库级别强制执行业务规则，确保数据的完整性和一致性。

正面例子：

CREATE TABLE product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0)
);

反面例子：
没有使用约束，允许插入无效数据：

CREATE TABLE product (
    product_id INT,
    product_name VARCHAR(100),
    price DECIMAL(10, 2),
    stock_quantity INT
);

17、考虑使用存储过程和触发器

存储过程和触发器可以封装复杂的业务逻辑，提高性能和一致性。

正面例子：
创建一个更新库存的存储过程：

DELIMITER //
CREATE PROCEDURE update_stock(IN product_id INT, IN quantity INT)
BEGIN
    UPDATE product
    SET stock_quantity = stock_quantity - quantity
    WHERE product_id = product_id;
END //
DELIMITER ;

反面例子：
在应用程序中实现所有业务逻辑，增加了出错和不一致的风险。

18、为大型表考虑分区

对于非常大的表，使用分区可以提高查询性能和管理效率。

正面例子：
按日期范围分区的订单表：

CREATE TABLE order (
    order_id INT,
    order_date DATE,
    customer_id INT,
    total DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p0 VALUES LESS THAN (2020),
    PARTITION p1 VALUES LESS THAN (2021),
    PARTITION p2 VALUES LESS THAN (2022),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

反面例子：
不分区的大型表可能导致查询性能下降和管理困难。

19、使用适当的命名前缀或后缀

使用前缀或后缀可以更清晰地表示表或列的用途或类型。

正面例子：

视图名称使用 “v_” 前缀：v_active_customers
索引名称使用 “_idx” 后缀：customer_email_idx
外键名称使用 “_fk” 后缀：order_customer_fk

反面例子：
所有对象使用相同的命名方式，难以区分其类型或用途。

20、记录和维护数据库设计文档

虽然这不是直接的设计规则，但保持最新的数据库设计文档对于长期维护和团队协作至关重要。

正面例子：
使用数据库设计工具创建和维护ER图
为每个表和重要字段编写详细的注释
记录设计决策和任何特殊考虑

反面例子：
没有文档，依赖于开发人员的记忆或代码注释来理解数据库结构。

四、总结

这些规定涵盖了从命名约定到性能优化的各个方面，遵循这些规定可以帮助您创建一个结构良好、高效和易于维护的数据库。记住，虽然这些是一般性的最佳实践，但在特定情况下可能需要根据实际需求进行调整。