数据库设计之E-R图和关系表

一 概念

E-R图：也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。

实体：用矩形框表示，矩形框内写明实体名称

实体属性：用椭圆图框或圆角矩形表示

关系：用菱形框表示实体之间的关系，在菱形框内写明关系名，分别与有关实体连接起来，同时在实心线段旁标上关系的类型（1:1，1:N或M:N）

关系表：将E-R图转换成关系表，并定义列类型，建立主键、外键等各种约束。

二 E-R图

下边我们以大家最为熟悉的一个应用场景——学校系统（学生、课程、老师、学生证）为例，设计一个E-R图，如下：

说明：

1. 图中定义了4个实体和3个关系，实体分别是学生、学生证、老师和课程。
2. 实体的属性如图中各个椭圆所示。
3. 3组关系：学生与学生证的一对一入学关系（每个学生入学时，办理唯一的学生证）；课程与老师之间的一对多任教关系（此处为了便于说明1对多关系，假设学校只允许一个老师教一门课程，但一门课程可以有多个老师任教）；课程与学生之间的多对多关系（每个课程可以有多个学生学习，每个学生可以选择多门课程）。

 三 关系表

完成了E-R图设计后，下边就可以设计关系表了，如下步骤：

1. 数据库选型，常用的数据库MySQL，SQL Server，Oracle等。各种数据库支持的字段类型略有不同，所以在物理表设计之前要完成数据库选型。下边我们以开源数据库MySQL为例，进行建表。
2. 将每个实体转换成一个数据表，将实体的属性转换为该表中的列，为每个列定义相应的数据类型。
3. 对于1:1关系的两个表，给两个表设置相同的主键列。
4. 对于1:N关系的两个表，在N表中添加一个外键列，该列与1标的主键相关联。
5. 对于M:N关系，生成一个单独的表表示该关系，该关系的列由两个表的主键组成。
6. 重新审核所有的表，在需要的地方添加约束，对常用的条件字段设置索引。

通过以上步骤，可以将E-R图中的实体关系模型转换成具体的关系表。该图一共生成了5个表：4个实体表和1个关系表。

课程表

表名:t_course		表的作用：定义实体课程
列名	类型	索引、约束	作用及备注
id	INT	PK	唯一标识
title	TEXT	NOT NULL	课程名
period	INT	Index	学时
description	TEXT		课程描述

老师表

表名:t_teacher		表的作用：定义实体老师
列名	类型	索引、约束	作用及备注
id	INT	PK	唯一标识
name	TEXT	NOT NULL	姓名
gender	BOOL	Index	性别 True：男 False：女
address	TEXT		住址
course_id	INT	FK:t_course.id	由于1:N关系添加的外键

学生表

表名:t_student		表的作用：定义实体学生
列名	类型	索引、约束	作用及备注
student_id	INT	PK	唯一标识
name	TEXT	NOT NULL	姓名
age	INT	Index	年龄
parent	TEXT		家长

学生证表

表名:t_card		表的作用：定义实体学生证
列名	类型	索引、约束	作用及备注
student_id	INT	PK	学号，唯一标识
start_from	datetime		注册日期、入学日期
end_to	datetime		学生证有效期截止日期

学生课程关系表

表名:t_enroll		表的作用：定义关系学生:课程
列名	类型	索引、约束	作用及备注
student_id	INT	PK,FK:t_student.student_id	学生标识
course_id	INT	PK,FK:t_course.id	课程标识

说明：

Index：索引，是对数据库表中一列或多列的值进行排序的一种结构。对常用的查询条件字段添加索引可以显著提高SQL语句的性能。

Constraint：约束，是对列数据取值的某种限定。常见的约束有主键、外键、非空、唯一等。

PK：主键（Primary Key），唯一标识一条记录，不允许为空。在大多数数据库中主键列也是一个索引列。

FK：外键（Foreign Key），是另一个表的主键，表示关联关系，可以是空字段。

NOT NULL：非空约束，表示不允许列值为空。

四 SQL语句

关系表设计好之后，创建表格的SQL语句，也就水到渠成了，下边是以上5个关系表对应的建表mysql语句。注意mysql引擎使用的是InnoDB，字符集是utf-8。

课程表

//课程表
CREATE TABLE `t_course` (
  `id` int(11) NOT NULL,
  `title` text NOT NULL,
  `period` int(11) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  KEY `idx_period` (`period`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

老师表

//老师表
CREATE TABLE `t_teacher` (
  `id` int(11) NOT NULL,
  `name` text NOT NULL,
  `gender` int(11) DEFAULT NULL,
  `address` text,
  `course_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `gender` (`gender`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `course_id` FOREIGN KEY (`course_id`) REFERENCES `t_course` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生表

//学生表
CREATE TABLE `t_student` (
  `student_id` int(11) NOT NULL,
  `name` text NOT NULL,
  `age` int(11) DEFAULT NULL,
  `parent` text,
  PRIMARY KEY (`student_id`),
  KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生证表

//学生证表
CREATE TABLE `t_card` (
  `student_id` int(11) NOT NULL,
  `start_from` datetime DEFAULT NULL,
  `end_to` datetime DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

学生课程关系表

//学生:课程关系表
CREATE TABLE `t_enroll` (
  `student_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  PRIMARY KEY (`student_id`,`course_id`),
  CONSTRAINT `student_id` FOREIGN KEY (`student_id`) REFERENCES `t_student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

五 参考文献

《Python高效开发实战》（刘长龙）