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 NULLAUTO_INCREMENT,
`name`varchar(20) NOT NULL,
`sex`tinyint(1) DEFAULT NULL,
`birthday` dateDEFAULT 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 NULLAUTO_INCREMENT,
`name`varchar(20) NOT NULL,
`sex`tinyint(1) DEFAULT NULL,
`birthday` dateDEFAULT 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 NULLAUTO_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 NULLAUTO_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 NULLAUTO_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 NULLAUTO_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 NULLAUTO_INCREMENT,
#省略PRIMARY KEY(`id`)
)CREATE TABLE`normal_student` (
`id`int(11) unsigned zerofill NOT NULLAUTO_INCREMENT,
#省略PRIMARY KEY(`id`)
)CREATE TABLE `normal_student2selectable_lesson` ( #意为normal student toselectable lesson
`id`int(11) unsigned zerofill NOT NULLAUTO_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