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定义外键关联另外一张表的缺点有：

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

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

一对一

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

多对多

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

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