MySql 多表设计

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本分为：一对多，多对多，一对一。

一对多

CREATE TABLE tb_dept (id int UNSIGNED primary key auto_increment COMMENT 'id',name VARCHAR(10) not null UNIQUE comment '部门名称',create_time datetime not null COMMENT '创建时间',update_time datetime not null COMMENT '修改时间') COMMENT '部门表';

CREATE TABLE `tb_emp` (`id` int NOT NULL AUTO_INCREMENT COMMENT '主键id',`username` varchar(20) NOT NULL COMMENT '用户名',`password` varchar(23) DEFAULT NULL COMMENT '密码',`name` varchar(10) DEFAULT NULL COMMENT '姓名',`gender` tinyint DEFAULT NULL COMMENT '性别：1男，2女',`image` varchar(300) DEFAULT NULL COMMENT '图像url',`job` tinyint DEFAULT NULL COMMENT '职位：1班主任，2讲师，3学工主管，4教研主管',`entrydate` datetime DEFAULT NULL COMMENT '入职日期',`dept_id` int COMMENT '归属部门id',`creat_time` datetime NOT NULL COMMENT '创建时间',`update_time` datetime NOT NULL COMMENT '修改时间',PRIMARY KEY (`id`),UNIQUE KEY `username` (`username`)
) COMMENT='员工表' ;

多表问题：上述两张表在数据库层面并未建立关联，所以是无法保证数据的一致性和完整性的。

外键约束：

-- 创建表时指定
create table 表名 (字段名 数据类型,...[constraint] [外键名称] foreign key (外键字段名) references 主表（字段名）
);-- 建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表（字段名）;

使用foreign key定义外键关联另外一张表的缺点有：

• 影响增删改查的效率（需要检查外键关系）。
• 仅用于单节点数据库，不适用于分布式、集群场景。
• 容易引发数据库的死锁问题，消耗性能。

逻辑外键：在业务层逻辑中，解决外键关联。通过逻辑外键，可以很方便的解决上述问题。

一对一

一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中以提高效率。典型案例：用户与身份证信息的关系。

多对多

一个学生可以选修多门课程，一个课程也可以供多个学校选择。

实现：建立第三张表，中间表至少包含两个外键，分别关联两方主键。