MySQL - 多表设计

在实际项目开发中，数据表之间往往存在复杂的关系。常见的关系有一对多、一对一和多对多。为了保持数据的规范性和合理性，多表设计是数据库设计的重要部分。

1. 一对多关系

在一对多关系中，一个表的某条记录对应另一个表的多条记录。常见的场景是“部门”和“员工”之间的关系，一个部门可以有多个员工。

1.1 实现方式

在“多”的一方（员工表）添加一个外键字段，指向“1”的一方（部门表）的主键。

CREATE TABLE tb_dept (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL UNIQUE COMMENT '部门名称',
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL
) COMMENT '部门表';

CREATE TABLE tb_emp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL,
    dept_id INT COMMENT '部门ID',   -- 员工的归属部门 -- 逻辑外键
    create_time DATETIME NOT NULL,
    update_time DATETIME NOT NULL
) COMMENT '员工表';

1.2 外键约束

目前上述的两张表(员工表、部门表)，在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。

想解决上述的问题呢，我们就可以通过数据库中的 外键约束 来解决。

外键约束：让两张表的数据建立连接，保证数据的一致性和完整性。

对应的关键字：foreign key

1.2.1 物理外键

概念：使用foreign key定义外键关联另外一张表。

缺点：

        影响增、删、改的效率（需要检查外键关系）。

        仅用于单节点数据库，不适用与分布式、集群场景。

        容易引发数据库的死锁问题，消耗性能。

1.2.2 逻辑外键

保留对应字段，但是不要加foreign key。

概念：在业务层逻辑中，解决外键关联。

        通过逻辑外键，就可以很方便的解决上述问题。

在现在的企业开发中，很少会使用物理外键，都是使用逻辑外键。 甚至在一些数据库开发规范 中，会明确指出禁止使用物理外键 foreign key。

2. 一对一关系

一对一关系常用于将一张表拆分成两张，将大表中的一些基础字段放在一张表当中，将其他的字段放在另外一张表当中，以提高数据查询效率。例如“用户”和“身份证信息”之间的关系。

如果在业务系统当中，对用户的基本信息查询频率特别的高，但是对于用户的身份信息查询频率很 低，此时出于提高查询效率的考虑，我就可以将这张大表拆分成两张小表，第一张表存放的是用户 的基本信息，而第二张表存放的就是用户的身份信息。他们两者之间一对一的关系，一个用户只能 对应一个身份证，而一个身份证也只能关联一个用户。

实现方式：在一张表中添加外键，关联另外一方的主键，并且设置该外键为 UNIQUE，保证一对一关系。

CREATE TABLE tb_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL COMMENT '姓名',
    phone CHAR(11) COMMENT '手机号'
) COMMENT '用户表';

CREATE TABLE tb_user_card (
    id INT PRIMARY KEY AUTO_INCREMENT,
    idcard CHAR(18) NOT NULL COMMENT '身份证号',
    user_id INT UNIQUE COMMENT '用户ID',  -- 一对一外键
    CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES tb_user(id)
) COMMENT '身份证信息表';

3. 多对多关系

多对多关系通过中间表实现。例如，“学生”和“课程”之间的关系，一个学生可以选多门课程，一门课程也可以被多个学生选修。

实现方式：创建一个中间表，中间表包含两个外键，分别指向两张表的主键。

CREATE TABLE tb_student (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL COMMENT '学生姓名'
) COMMENT '学生表';

CREATE TABLE tb_course (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL COMMENT '课程名称'
) COMMENT '课程表';

CREATE TABLE tb_student_course (
    student_id INT NOT NULL COMMENT '学生ID',
    course_id INT NOT NULL COMMENT '课程ID',
    CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES tb_student(id),
    CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES tb_course(id)
) COMMENT '学生课程中间表';