在实际项目开发中,数据表之间往往存在复杂的关系。常见的关系有一对多、一对一和多对多。为了保持数据的规范性和合理性,多表设计是数据库设计的重要部分。
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 '学生课程中间表';