MySQL：多表设计

一、前言

在 MySQL 中，进行多表设计是构建高效、可靠数据库的关键步骤。以下是一些多表设计的要点和示例：

• 明确业务需求： 首先要了解业务流程和数据之间的关系。比如，在一个电商系统中，可能有用户表、订单表、商品表等。

• 确定表的结构：为每个表定义合适的列，包括主键、外键等。

• 选择合适的表关系：

  • 一对多：一个表的一行可以与另一个表的多行相关联。一个部门可以有多个员工，多个员工只能归属一个部门。
  • 一对一：一个表的一行可以与另一个表的多行相关联，与一对多不同的是，关联的字段是唯一的。用户与身份证的关系。
  • 多对多：两个表中的多行可以相互关联。学生和课程就是多对多的关系，一个学生可以选多门课程，一门课程也可以供多门学生选择。这种情况通常需要一个中间表来建立关系。

• 规范化设计：遵循数据库规范化原则，减少数据冗余和数据不一致性。通常至少达到第三范式。

• 考虑性能优化：适当建立索引，提高查询效率。但过多的索引可能会影响数据插入和更新的性能。

• 数据一致性和完整性：通过约束（如主键约束、外键约束、非空约束等）确保数据的一致性和完整性。

总之，多表设计需要综合考虑业务需求、数据关系、性能和数据质量等多个方面，以构建一个合理、高效的数据库结构。您是正在进行一个新的数据库项目的多表设计吗？

---

二、一对多

在 MySQL 中，一对多关系是常见的表关系类型，而实现这种关系时，可以使用逻辑外键和物理外键。

• 逻辑外键

不在数据库表结构中定义实际的外键约束，而是通过在应用程序的代码逻辑中进行控制和验证，通过在数据库表 "多" 的一方，添加字段，来关联 "一" 的一方的主键

-- 部门表（一）
create table tb_dept (
    id int primary key auto_increment comment 'ID',
    name varchar(50) comment '部门名'
) comment '部门表';

-- 员工表（多）
create table tb_emp (
    id int primary key auto_increment comment 'ID',
    name varchar(50) comment '姓名',
    age tinyint unsigned comment '年龄',
    dept_id int comment '部门ID'
) comment '员工表';

• 物理外键

目前上述的两张表，在数据库层面并未建立关联，所以无法保证数据的一致性和完整性。如果想要保证数据的一致性和完整性，需要在数据库表结构中实际定义的外键约束，通过在 "多" 的一方的表中创建一个字段，该字段引用 "一" 的一方表的主键。

-- 创建表时指定
create table 表名(
	字段名 数据类型,
	...
	[constraint] [外键名] foreign key (外键字段名) references 主表(字段名)
);

-- 创建完表后，添加外键
alter table 表名 add constraint 外键名 foreign key (外键字段名) references 主表(字段名);

-- 部门表（一）
create table if not exists tb_dept (
    id int primary key auto_increment comment '部门ID',
    name varchar(50) comment '部门名'
) comment '部门表';
insert into tb_dept (name) values ('人事部');
insert into tb_dept (name) values ('财政部');

-- 员工表（多）
create table tb_emp (
    id int primary key auto_increment comment 'ID',
    name varchar(50) comment '姓名',
    age tinyint unsigned comment '年龄',
    dept_id int comment '部门ID',
    constraint fk_emp_dept foreign key (dept_id) references tb_dept(id)
) comment '员工表';
insert into tb_emp(name, age, dept_id) values ('张三', 18, 1);
insert into tb_emp(name, age, dept_id) values ('李四', 18, 2);
insert into tb_emp(name, age, dept_id) values ('王五', 18, 1);

-- 添加外键
-- alter table tb_emp add constraint 外键名 foreign key (dept_id) references tb_dept(id);

---

三、一对一

一对一的关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提高效率。通过在任意一方加入外键来关联另外一张表的主键，并且设置外键为唯一（UNIQUE）。

-- 用户表
create table tb_user (
    id int primary key auto_increment comment '用户ID',
    name varchar(50) comment '姓名',
    age tinyint unsigned comment '年龄'
) comment '用户表';
insert into tb_user (name, age) values ('张三', 18);
insert into tb_user (name, age) values ('李四', 20);

-- 用户信息表
create table tb_user_card (
    id int primary key auto_increment comment 'ID',
    birthday date not null comment '生日',
    id_card varchar(18) not null comment '身份证号',
    user_id int not null unique comment '用户ID',
    constraint fk_user_id foreign key (user_id) references tb_user(id)
) comment '用户信息表';
insert into tb_user_card (birthday, id_card, user_id) values ('2000-06-10', '100001021000000100', 1);
insert into tb_user_card (birthday, id_card, user_id) values ('2003-08-03', '100001021000000101', 2);

---

四、多对多

通过创建一个中间表，中间表中至少包含两个外键，分别用来关联两方的主键。

-- 学生表
create table tb_student (
    id int auto_increment primary key  comment '学生ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';
insert into tb_student (name, no) values ('张三', 20030803);
insert into tb_student (name, no) values ('李四', 20010901);

-- 课程表
create table tb_course(
    id int auto_increment primary key  comment '课程ID',
    name varchar(10) comment '课程名'
) comment '课程表';
insert into tb_course (name) values ('语文');
insert into tb_course (name) values ('数学');
insert into tb_course (name) values ('英文');

-- 中间表
create table tb_student_course (
    id int auto_increment primary key comment 'ID',
    student_id int not null comment '学生ID',
    course_id int not null comment '课程ID',
    constraint fk_student_id foreign key (student_id) references tb_student(id),
    constraint fk_course_id foreign key (course_id) references tb_course(id)
) comment '学生课程中间表';
insert into tb_student_course (student_id, course_id) values (1,1);
insert into tb_student_course (student_id, course_id) values (1,2);
insert into tb_student_course (student_id, course_id) values (1,3);
insert into tb_student_course (student_id, course_id) values (2,1);
insert into tb_student_course (student_id, course_id) values (2,3);

---