表设计 | Minsix’s Blog

多维度基础信息表

以省市区这种树结构的级联信息为例，我们可以设计 3 张表，然后分别关联，但是我们也可以设计一张表，会更加简洁，3 张表，设计如下：

// 省表
CREATE TABLE `province` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;

// 市表
CREATE TABLE `city` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  `provincecode` varchar(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=343 DEFAULT CHARSET=utf8;
// 县，区表
CREATE TABLE `area` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(6) NOT NULL,
  `name` varchar(20) NOT NULL,
  `citycode` varchar(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3145 DEFAULT CHARSET=utf8;

换成一张表设计，如下：

CREATE TABLE `region` (
  `region_id` varchar(10) NOT NULL COMMENT '地区主键编号',
  `region_name` varchar(50) NOT NULL COMMENT '地区名称',
  `region_short_name` varchar(10) DEFAULT NULL COMMENT '地区缩写',
  `region_code` varchar(20) DEFAULT NULL COMMENT '行政地区编号',
  `region_parent_id` varchar(10) DEFAULT NULL COMMENT '地区父id',
  `region_level` int(2) DEFAULT NULL COMMENT '地区级别 1-省、自治区、直辖市 2-地级市、地区、自治州、盟 3-市辖区、县级市、县',
  PRIMARY KEY (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='地区表';

通过 region_level 这个字段来增加了数据的一个维度信息，就表达一条数据的信息，然后通过 region_parent_id 来表达关联关系，这样一张表就能表达出来了，这样做查询的时候，一张表就把所有信息查出来了，方便不少.

这种设计用在基础数据设计上是非常方便的，这种表结构变化比较少，新增，更改不频繁的表结构上面，比如，分类信息，部门信息。

不能滥用，比如学生，班级，就不适合，比如洛可场景，楼层，展位，操作频繁，而且也不符合三范式。

1:n 设计变通，构造关联表

假设有两个表：部门表（Departments）和员工表（Employees）。每个部门可以拥有多名员工，但每名员工只能属于一个部门（1:n）。

消除 1:n 关系：

创建关联表”部门员工关联表”，该表包含两个字段，分别是部门 ID 和员工 ID。
部门ID字段作为外键引用部门表的主键，员工ID字段作为外键引用员工表的主键。
每行记录表示一个具体的部门与员工的关联。适用场景：关联字段在项目中使用宽泛，并且可以在该关联表中添加额外字段，易于维护业务需求。

n:n 设计变通，构造中间表

将原来 n:n 的关系，现在变为两个 1:n 的关系。将原来两个表的的属性合理地分配到第 3 张表。

step1:

在“图书馆信息系统”中，“图书”对应一张表，“读者”也对应一张表。这两张表之间的关系，是一个典型的多对多关系，一本图书在不同时间可以被多个读者借阅，一个读者又可以借多本图书。

在二者之间增加第三个实体，该实体取名为“借还书”，它的属性为：借还时间、借还标志（0 表示借书，1 表示还书）
它还应该有两个外键（“图书”的主键，“读者”的主键），使它能与“图书”和“读者”连接。
每行记录表示一条借书/还书记录。

step2:

假设上面的中间表就三个字段：

CREATE TABLE `book_reader` (
  `id` varchar(10) NOT NULL COMMENT '主键编号',
  `book_id` varchar(50) NOT NULL COMMENT 'book 表 主键',
  `reader_id` varchar(10) DEFAULT NULL COMMENT '读者表主键'
  PRIMARY KEY (`region_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图书，读者中间表';

这张表特别符合第三范式的，但是你做查询的时候每次都得关联 3 张表查询，其实可以适当冗于部分信息，比如书名，书的 isbn 编号，这样的好处是，查中间表就能获取部分信息，但是需要注意的是合理分配，不要把变动频繁的字段往中间表放，更新数据的时候，记得更新中间表