数据库设计规范

ER 图与实体关系建模

ER 图三要素

元素	表示	说明
实体	矩形	现实中的对象（用户、订单、商品）
属性	椭圆	实体的特征（姓名、价格、创建时间）
关系	菱形	实体间的关联（下单、属于、包含）

关系类型

关系	说明	示例
1:1	一对一	用户 ↔ 身份证
1:N	一对多	用户 ↔ 订单（一个用户有多个订单）
M:N	多对多	学生 ↔ 课程（需中间表）

建模步骤

确定实体：找出业务中的核心对象
确定属性：每个实体有哪些字段
确定关系：实体间的关联方式
绘制 ER 图：可视化呈现
转换为表结构：ER 图 → DDL 语句

数据库范式

范式是数据库设计的规范级别，级数越高，数据冗余越低。

第一范式（1NF）

要求：每一列都是不可分割的原子值。

sql

-- ❌ 违反 1NF：字段包含多个值
users (id, name, phones)  -- phones = "138000,139000"

-- ✅ 符合 1NF
users (id, name, phone1, phone2)  -- 或拆为另一张表
user_phones (id, user_id, phone)

第二范式（2NF）

前提：满足 1NF 要求：非主键列完全依赖于主键（消除部分依赖）。

sql

-- ❌ 违反 2NF：部分依赖（订单金额只依赖于商品ID，不依赖用户ID）
order_details (order_id, user_id, product_id, product_name, price, quantity)

-- ✅ 符合 2NF：拆分为两张表
orders (order_id, user_id)
order_items (order_id, product_id, product_name, price, quantity)

第三范式（3NF）

前提：满足 2NF 要求：非主键列直接依赖于主键（消除传递依赖）。

sql

-- ❌ 违反 3NF：传递依赖
-- 用户姓名 -> 部门 ID -> 部门名称（部门名称间接依赖用户）
users (id, name, dept_id, dept_name)

-- ✅ 符合 3NF
users (id, name, dept_id)
departments (dept_id, dept_name)

BCNF（Boyce-Codd 范式）

前提：满足 3NF 要求：每个决定因素都是候选键。

实际项目中，通常满足 3NF 即可，BCNF 过于严格。

范式总结

范式	核心要求	解决的问题
1NF	列不可分割	原子性问题
2NF	完全依赖主键	部分依赖导致的冗余
3NF	直接依赖主键	传递依赖导致的冗余
BCNF	决定因素都是候选键	3NF 未处理的特殊情况

反范式化设计

反范式化 = 刻意引入冗余，以空间换时间。

适用场景

读多写少：查询频繁，写操作少
复杂查询：需要频繁 JOIN 多张表
统计分析：直接查冗余字段避免聚合

示例

sql

-- 范式化：查询订单详情需要 JOIN 3 张表
SELECT o.id, u.name, p.title
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;

-- 反范式化：预存冗余字段，避免 JOIN
orders (id, user_id, user_name, product_id, product_title, price)

反范式化风险

风险	说明
数据不一致	冗余字段需要同步更新
更新开销大	修改一个字段可能需更新多处
存储空间增加	冗余数据占用更多空间

在范式化和反范式化之间寻找平衡点。

命名约定

通用规则

统一使用小写字母 + 下划线
见名知意，避免缩写
不使用保留字（如 order、group、select）

表名

规则	示例
单数或复数（统一即可）	`user` / `users`
关联表用下划线连接	`user_role`、`order_product`
模块前缀（可选）	`log_access`、`sys_config`

字段名

sql

-- 主键
id              -- 单表主键
order_id        -- 外键（表名_id）

-- 通用字段
created_at      -- 创建时间
updated_at      -- 更新时间
deleted_at      -- 软删除时间（可为 NULL）
version         -- 乐观锁版本号

-- 状态字段
status          -- 状态（0=禁用, 1=启用）
is_deleted      -- 是否删除（0=否, 1=是）

索引名

sql

idx_表名_字段名              -- 普通索引
uk_表名_字段名               -- 唯一索引
idx_表名_字段1_字段2         -- 联合索引
fk_表名_字段名               -- 外键

字段类型选择原则

数字类型

类型	范围	适用场景
`TINYINT`	0~255 / -128~127	状态码、布尔值
`SMALLINT`	0~65535	枚举值、小范围计数
`INT`	0~42亿	最常用，主键、常规计数
`BIGINT`	极大	雪花 ID、大数量计数
`DECIMAL(M,D)`	精确小数	金额、价格

金额用 DECIMAL，不要用 FLOAT/DOUBLE。

字符串类型

类型	说明	建议
`CHAR(N)`	定长字符串	N < 256，固定长度（如手机号、MD5）
`VARCHAR(N)`	变长字符串	最常用，N 按实际最大长度设置
`TEXT`	大文本	尽量少用，会生成隐藏临时表
`JSON`	JSON 数据（5.7+）	适合结构灵活的数据

时间类型

类型	范围	建议
`DATETIME`	1000-01-01 ~ 9999-12-31	推荐，不受时区影响
`TIMESTAMP`	1970-01-01 ~ 2038-01-19	受时区影响，2038 年问题
`DATE`	仅日期	只需日期时使用
`YEAR`	1901-2155	仅需年份时使用

设计案例

简单电商数据库设计

sql

-- 用户表
CREATE TABLE `user` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username`   VARCHAR(50)     NOT NULL COMMENT '用户名',
    `phone`      VARCHAR(20)              COMMENT '手机号',
    `status`     TINYINT         NOT NULL DEFAULT 1 COMMENT '1=正常 0=禁用',
    `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_user_username` (`username`),
    KEY `idx_user_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 商品表
CREATE TABLE `product` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title`       VARCHAR(200)    NOT NULL COMMENT '商品标题',
    `price`       DECIMAL(10,2)   NOT NULL COMMENT '价格',
    `stock`       INT UNSIGNED    NOT NULL DEFAULT 0 COMMENT '库存',
    `status`      TINYINT         NOT NULL DEFAULT 1 COMMENT '1=上架 0=下架',
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_product_status_created` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

-- 订单表
CREATE TABLE `order` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_no`    VARCHAR(32)     NOT NULL COMMENT '订单号',
    `user_id`     BIGINT UNSIGNED NOT NULL COMMENT '用户ID',
    `total_amount` DECIMAL(12,2)  NOT NULL COMMENT '总金额',
    `status`      TINYINT         NOT NULL DEFAULT 0 COMMENT '0=待支付 1=已支付 2=已发货 3=已完成 4=已取消',
    `paid_at`     DATETIME                 COMMENT '支付时间',
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_no` (`order_no`),
    KEY `idx_order_user_id` (`user_id`),
    KEY `idx_order_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

-- 订单明细表
CREATE TABLE `order_item` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id`    BIGINT UNSIGNED NOT NULL COMMENT '订单ID',
    `product_id`  BIGINT UNSIGNED NOT NULL COMMENT '商品ID',
    `product_title` VARCHAR(200)  NOT NULL COMMENT '商品名称（冗余）',
    `price`       DECIMAL(10,2)   NOT NULL COMMENT '单价（冗余）',
    `quantity`    INT UNSIGNED    NOT NULL COMMENT '数量',
    `subtotal`    DECIMAL(12,2)   NOT NULL COMMENT '小计',
    PRIMARY KEY (`id`),
    KEY `idx_order_item_order_id` (`order_id`),
    KEY `idx_order_item_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';

数据库设计规范 ​

ER 图与实体关系建模 ​

ER 图三要素 ​

关系类型 ​

建模步骤 ​

数据库范式 ​

第一范式（1NF） ​

第二范式（2NF） ​

第三范式（3NF） ​

BCNF（Boyce-Codd 范式） ​

范式总结 ​

反范式化设计 ​

适用场景 ​

示例 ​

反范式化风险 ​

命名约定 ​

通用规则 ​

表名 ​

字段名 ​

索引名 ​

字段类型选择原则 ​

数字类型 ​

字符串类型 ​

时间类型 ​

设计案例 ​

简单电商数据库设计 ​