MySQL 表设计

0、这篇博客中一部分内容是Deolin的个人观点和习惯。

 

1、把SET remove_time = '1970-01-01 00:00:00' 称为“移除”（remove），

把DELETE FROM table 称为“擦除”（expunge），

至于“删除”（delete）、“取消”（cancel），则更像个业务层而不是持久层的概念。

 

2、数据表可以分为信息表，关联表，字典表。

 

3、信息表反映的是数据信息，“数据为王”主要指的就是这类表里的数据。

信息表应该有以下字段

id int 这条数据的主键，默认长度，无符号，自增，前位补零。由于remove_time的存在，所以业务上的字段做主键便不再合适，因为新插入的数据很有可能与被移除的数据发生主键重复，所以需要一个毫无业务意义的主键

insert_time datetime 这条数据第一次执行insert文的时间，不能为空。

update_time datetime 这条数据每次执行update文的时间，可以为空（如果从为被update过，那就应该是空的）。

remove_time datetime 这条数据执行remove文的时间，可以为空，这个字段有值则代表这条数据被删了，

由于信息表里的数据往往比较重要，所以应该禁止擦除，使用移除。而“有remove_time字段”和“使用移除”两件事情应该同时出现，或同时不出现

 

信息表可以有以下字段，但不是“应该有”

insert_id int 插入这条数据的业务上的插入者id，代表的是某一个信息表的主键（user表之类的）。

insert_table varchar(64) 如果业务上不只一类插入者，那么需要指定是那个信息表。长度定为64，因为表名最长64个字符。

update_id int 与insert_id同理。

update_table varchar(64) 与insert_table同理。

remove_id int 与insert_id同理。

remove_table varchar(64) 与insert_table同理。

 

如果是整个项目在业务上没有登录行为，或是管理员在db上插入/修改的，那么些字段只能为空了，这也是这些字段不算“应该有”的原因了。

 

一个信息表的示例可能是这样的

CREATE TABLE `person` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `father_age` int(3) DEFAULT NULL, #这个字段可能非常奇怪，但Deolin只是希望能在示例里有至少一个int字段，并且最好不是age（age跟birthday一起出现的话显得更加奇怪）
  `tuition` decimal(8,2) DEFAULT NULL,
  `payment_time` datetime DEFAULT NULL,
  `vip_flag` tinyint(1) DEFAULT NULL,
  `insert_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `remove_time` datetime DEFAULT NULL
  PRIMARY KEY (`id`)
)

 

4、关联表反映的是两张信息表之间多对多的关系，例如学生表与课程表之间的关系

除了主键，关联表应该有像信息表那样的基础字段。

至于主键，它的设计有两种方式

第一种

放弃主键id，而是使用被关联表各自的id作为双主键（如student_id, lesson_id），

这种方式优点是直观，一眼就能看出是关联表，而且关联关系绝对不会重复。

有个缺点，remove_time和移除操作不能有了，因为会发生主键重复，所以只能擦除。

 

第二种

使用主键id，优缺点与第一种方式正好相反。

主要适用于两种场合，一是整个项目有比较强的数据挖掘和分析目地的，即便是关联关系也要保留下来，

二是关联关系产生时，会出现一些业务字段（如学生选的课，会有成绩，那么成绩字段不应该放在student或lesson中，而是应该放在关联表里）

 

5、字典表反映的是为项目本身提供的各种数据，是对项目扩展性的补充，

（如JSP中option标签的每一个value属性，如某个表'kind'之类字段的值解释，如一些配置属性），数据量不会很大，

基本上，数据是开发人员录入，项目不会对它有增、改、移除、擦除操作，只有查询操作，一个不是特别大的项目只需要一张字典表。

一般只有以下字段

key varchar(255) 主键，字典的索引

value varchar(255)  字典的值

group varchar(255) 用来表示key属于那一类，可以为空

sort int 用来表示key在自己所在group的顺序，可以为空

disable tinyint(1) 用来表示这条数据是否被失效，不能为空，默认0

 

6、信息表之间一对一的关系，设计成两张表的id字段一致

例如`person`表示例中，每个人都有“学生”和“子女”两个不同模块的属性，可以将它们分别抽取出来

CREATE TABLE `person` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `sex` tinyint(1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `vip_flag` tinyint(1) DEFAULT NULL,
  `insert_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `remove_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)  #与其他两张表的id形成逻辑外键
)
CREATE TABLE `student` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `tuition` decimal(8,2) DEFAULT NULL,
  `payment_time` datetime DEFAULT NULL,
  `insert_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `remove_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)  #与其他两张表的id形成逻辑外键
)
CREATE TABLE `child` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `father_age` int(3) DEFAULT NULL,
  `father_job` int(3) DEFAULT NULL,  
  `mother_age` int(3) DEFAULT NULL,    
  `mother_job` int(3) DEFAULT NULL,  
  `insert_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `remove_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)  #与其他两张表的id形成逻辑外键
)

 

7、信息表之间一对多的关系，在“多”的表中追加一个 `for_表名` 字段（如果其他字段中有单词for，有时候也会设计成`表名_id`）

例如，一家学校当前有多个学生，一个学生当前只能属于一个学校

CREATE TABLE `school` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `address` varchar(200) DEFAULT NULL,
  `insert_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `remove_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)
CREATE TABLE `student` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `for_school` int(11) NOT NULL DEFAULT '0',  #与school.id形成逻辑外键
  `name` varchar(20) DEFAULT NULL,
  `insert_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `remove_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

 

8、关联表的命名，一般是两张“多”的表的表名用2链接

例如一个学生选择多门课程，一门课程供多名学生选择

CREATE TABLE `selectable_lesson` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,  
  #省略
  PRIMARY KEY (`id`)
)

CREATE TABLE `normal_student` (
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,  
  #省略
  PRIMARY KEY (`id`)
)

CREATE TABLE `normal_student2selectable_lesson` (  #意为normal student to selectable lesson
  `id` int(11) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `for_normal_student` int(11) NOT NULL DEFAULT '0',  #与normal_student.id形成逻辑外键
  `for_selectable_lesson` int(11) NOT NULL DEFAULT '0',  #与selectable_lesson.id形成逻辑外键
  `score` int(3) DEFAULT NULL,
  `insert_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `remove_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
)

 

9、信息表的中字段名中不出现表名，

如不采用user.username，而是采用user.name

 

11、信息表中涉及到“属于什么类型”、“是哪种分类”概念的字段，用`kind`表示，如user.kind

涉及到以上概念信息表，用诸如`food_type`来表示。

 

10、类型

字符串长度不变的用char(n)，

字符串能大致确定范围的用varchar(n)，

字符串范围超过65532的用text，

年龄之类的字段通常用int(n)，

上限不定的个数类字段通常用int默认，即最长11，

更长时用bigint，

日期用date，

日期+时间用datetime，

时间用time，

区分类字段（如“性别”，“直辖市”等）用tiny(1)（并在Java代码中定义好规约），

是/否类字段用tiny(1)，

价格类字段用decimal(8,2)，-99,999,999.99 ~ 99,999,999.99，

电话/手机用varchar(20)，

`id`用int，`insert_time`和`update_time`和`remove_time`用datetime