在开发项目的过程中,数据库表设计是难免的一步,这篇文章主要讲述数据库表设计的实现方法。

实体关系

数据库实体关系中,主要可以概括为三类关系,一对一、一对多、多对多。这只是概念上的关系,在真正的关系数据库中,只有外键,并没有这三种关系,那么就来以学生选课为例,说一说在关系数据库管理系统中,怎么实现这三种关系。

一对一

例如学生的身份证号(名字会重复但身份证号不会)与学号为一对一关系,这个关系是三种关系中实现起来最为简单的一种,可以把这两个实体合并成一个实体。但是在设计中,仍然会遇到两个完全不同的实体,之间存在一对一关系。如每个班级必须有一个班主任,这是一个一对一关系,在数据库中可以在其中一个表中添加指向另一个表的外键,在外键上添加唯一约束,同时添加条件约束,例如每个班级必须要有一个班主任,将这个外键设置为非空。总结起来说,如果对于实体A、实体B为一对一关系,假如实体A必须拥有实体B,就在实体A表中建立指向B的外键。

一对多

比如说班级和学生是一对多关系。一个班级对应多个学生,一个学生只会对于一个班级。一对多的关系之所以说简单,是因为数据库的外键其实就是表示一对多关系。对于一对多关系,只需要在“多”的这个表中建立“一”的外键关联即可,而“一”这边的表不需要做任何修改。比如前面说到的班级学生关系。班级表不变,学生表增加班级Id作为外键。

多对多

比如说学生和课程的关系就是多对多关系。一个学生会学习多门课程,一门课程会有多个学生来选修。在数据库中,必须使用中间表来表示多对多的关系。中间表可以分成两种,一种是纯粹表示关系的中间表,一种是表示中间实体的中间表。 纯粹表示关系的中间表很简单,只需要两列:A_idB_idA_id以外键关联到A表的主键,B_id以外键关联到B表的主键,然后这两个列组成联合主键。这个中间表纯粹是表示多对多关系而存在,在业务上不会有对应的实体与之对应。中间实体是在纯粹的中间关系表的基础上,加上了更多的属性,从而形成了一个新的实体。比如前面提到的学生和课程的关系,如果我们需要记录学生选课的时间、学生选择这门课程后的考试成绩,那么就要建立一个“选课”实体。

外键与索引

如果一个表中的若干个字段是来自另外若干个表的主键,则这若干个字段就是外键。外键并没有强制一定来自其他另外的表,也可能来自本表的主键。含有外键的表通常叫做外建表,外键字段来自参考的那个表则叫做主键表。

外键是一种约束,与索引的概念不一样,只是大多数情况下,建立外键时,都会在外键列上建立对应的索引。外键的存在会在每一次数据插入、修改时进行约束检查,如果不满足外键约束,则禁止数据的插入或修改,这必然带来一个问题,就是在数据量特别大的情况下,每一次约束检查必然导致性能的下降。索引也有类似的问题,索引如果建多了,那么在插入删除修改数据时也要去维护对应的索引,所以索引的存在也会导致数据操作变慢。

主键的设计

在数据库设计时,主要就是对实体和关系的设计,实体表现出来就是表,关系表现出来就是外键。而对于一个表,由两部分组成:主键和属性。主键的简单定义就是表中为每一行数据的唯一标识。其实更准确的说法,每一行数据的唯一标识是候选键(Candidate Key),一个表中可以有很多个候选键,主键是候选键中的一个,主要用于更方便的检索和管理数据。一个表中可以有多个候选键,但是只有一个主键。由于主键常常用于检索数据,也用于表之间的关联,所以主键的设计的好坏将会严重影响数据操作的性能。

主键的数据类型

最常见的主键数据类型是数字类型、固定长度的字符类型和GUID类型。通常情况下,RDBMS会在主键上建立聚集索引,由于使用B-Tree的数据结构来存储索引数据,所以一般对主键有以下两个要求:

  • 越短越好——越短在一个Page中存储的节点越多,检索速度就越快。
  • 顺序增长——如果每一条插入的数据的主键都比前面的主键大,那么B-Tree上的节点也是顺序增长的,不会造成频繁的B-Tree分割。

越短越好是为了查询的速度快,顺序增长是为了插入速度快。

数字类型:根据数据量决定是用Int16还是Int32或者Int64,能用Int32的就不需要使用Int64。

字符类型:基本不满足前面提到的2点要求,字符类型一般不会很短,而且也很可能不是顺序增长的,所以不是特别推荐的主键类型。当然如果确实业务需求使用字符类型,那么也尽量使用char(XX)而不要使用varchar(XX),因为在RDBMS中,对于定长字符串和变成字符串的数据结构和处理是不一样的,varchar的性能更差。

GUID类型:这个类型并不是所有数据库都有对应的数据类型,SQL Server有uniqueidentifier,MySQL没有。GUID类型在SQL Server中是16个字节,不算短,比4个字节的Int32长多了。在插入新数据时,GUID一般都是使用NewId()这样的生成随机GUID的方式生成的,所以也不是顺序增长的,在插入速度上不会很快。

通过上面的比较,知道使用数字类型是更好的方式,那么我们为什么还会有人使用GUID和字符串来当主键呢?那是因为:

  • 相对于数字类型,字符类型更易读易记,在检索关联的数据时,更方便直接。
  • GUID的优势是全球唯一,也就是说同样的系统,如果部署了多套环境,那么里面的数据的主键仍然是唯一的,这样有助于数据的集成。典型的例子就是一个系统在全国每个省份都部署一套,每个省份的数据各种录入,互不干扰,然后再把每个省的数据集成起来为总部做分析。

最佳实践:

  • 主键通常都是整数,不建议使用字符串当主键(如果主键是用于集群式服务,可以采用字符串类型)
  • 主键的值通常都不允许修改,除非本记录被删除。
  • 主键的值通常不重用,意味着记录被删除后,该主键值不再使用。
  • 主键不要直接定义成【id】,而要加上前缀,定义成【表名id】或者【表名_id】
  • 要用代理主键,不要使用业务主键(自增id)
    • 任何一张表,强烈建议不要使用有业务含义的字段充当主键
    • 我们通常都是在表中单独添加一个整型的编号充当主键字段

联合主键

联合主键就是以多个字段来唯一标识每一行数据。前面已经说到主键应该越短越好,而且是建议是一个没有意义的自增列,那么是不是就不会再需要联合主键呢?答案是否定的,仍然可能会使用到联合主键。联合主键主要使用在多对多的关系时,中间表就需要使用联合主键。在简单的多对多关系中,不需要为中间的关联建立实体,所以中间表可能就只需要两列,分别是两个实体表的主键。