表与表之间的关系和数据库的设计

表与表之间的关系

表关系的概念

现实生活中,实体与实体之间肯定是有关系的,比如:老公和老婆,部门和员工,老师和学生等。那么我们
在设计表的时候,就应该体现出表与表之间的这种关系!

表与表之间的三种关系
一对多:最常用的关系部门和员工
多对多:学生选课表和学生表, 一门课程可以有多个学生选择,一个学生选择多门课程
一对一:相对使用比较少。员工表简历表, 公民表护照表。

一对多

一对多(1:n) 例如:班级和学生,部门和员工,客户和订单,分类和商品
一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

多对多

多对多(m:n) 例如:老师和学生,学生和课程,用户和角色
多对多关系建表原则: 需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。

一对一

一对一(1:1) 在实际的开发中应用不多.因为一对一可以创建成一张表。

两种建表原则:

一对一的建表原则 说明
外键唯一 主表的主键和从表的外键(唯一),形成主外键关系,外键唯一 UNIQUE
外键是主键 主表的主键和从表的主键,形成主外键关系

一对多关系案例

需求:一个旅游线路分类中有多个旅游线路

界面

表与表的关系

具体操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游线路分类名称非空,唯一,字符串 100
create table tab_category (
cid int primary key auto_increment,
cname varchar(100) not null unique
)
-- 添加旅游线路分类数据:
insert into tab_category (cname) values ('周边游'), ('出境游'), ('国内游'), ('港澳游');
select * from tab_category;
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键,自动增长
rname 旅游线路名称非空,唯一,字符串 100
price 价格
rdate 上架时间,日期类型
cid 外键,所属分类
*/
create table tab_route(
rid int primary key auto_increment,
rname varchar(100) not null unique,
price double,
rdate date,
cid int,
foreign key (cid) references tab_category(cid)
)
-- 添加旅游线路数据
INSERT INTO tab_route VALUES
(NULL, '【厦门+鼓浪屿+南普陀寺+曾厝垵 高铁 3 天 惠贵团】尝味友鸭面线 住 1 晚鼓浪屿', 1499,
'2018-01-27', 1),
(NULL, '【浪漫桂林 阳朔西街高铁 3 天纯玩 高级团】城徽象鼻山 兴坪漓江 西山公园', 699, '2018-02-
22', 3),
(NULL, '【爆款¥1699 秒杀】泰国 曼谷 芭堤雅 金沙岛 杜拉拉水上市场 双飞六天【含送签费 泰风情 广州
往返 特价团】', 1699, '2018-01-27', 2),
(NULL, '【经典•狮航 ¥2399 秒杀】巴厘岛双飞五天 抵玩【广州往返 特价团】', 2399, '2017-12-23',
2),
(NULL, '香港迪士尼乐园自由行 2 天【永东跨境巴士广东至迪士尼去程交通+迪士尼一日门票+香港如心海景酒店
暨会议中心标准房 1 晚住宿】', 799, '2018-04-10', 4);
select * from tab_route;

多对多关系案例

需求:一个用户收藏多个线路,一个线路被多个用户收藏

具体操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
/*
创建用户表 tab_user
uid 用户主键,自增长
username 用户名长度 100,唯一,非空
password 密码长度 30,非空
name 真实姓名长度 100
birthday 生日
sex 性别,定长字符串 1
telephone 手机号,字符串 11
email 邮箱,字符串长度 100
*/
create table tab_user (
uid int primary key auto_increment,
username varchar(100) unique not null,
password varchar(30) not null,
name varchar(100),
birthday date,
sex char(1) default '男',
telephone varchar(11),
email varchar(100)
)
-- 添加用户数据
INSERT INTO tab_user VALUES
(NULL, 'cz110', 123456, '老王', '1977-07-07', '男', '13888888888', '66666@qq.com'),
(NULL, 'cz119', 654321, '小王', '1999-09-09', '男', '13999999999', '99999@qq.com');
select * from tab_user;
/*
创建收藏表 tab_favorite
rid 旅游线路 id,外键
date 收藏时间
uid 用户 id,外键
rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
*/
create table tab_favorite (
rid int,
date datetime,
uid int,
-- 创建复合主键
primary key(rid,uid),
foreign key (rid) references tab_route(rid),
foreign key(uid) references tab_user(uid)
)
-- 增加收藏表数据
INSERT INTO tab_favorite VALUES
(1, '2018-01-01', 1), -- 老王选择厦门
(2, '2018-02-11', 1), -- 老王选择桂林
(3, '2018-03-21', 1), -- 老王选择泰国
(2, '2018-04-21', 2), -- 小王选择桂林
(3, '2018-05-08', 2), -- 小王选择泰国
(5, '2018-06-02', 2); -- 小王选择迪士尼
select * from tab_favorite;

表与表之间的关系小结

表与表的关系 关系的维护
一对多 主外键的关系
多对多 中间表,两个一对多
一对一 1) 特殊一对多,从表中的外键设置为唯一 2) 从表中的主键又是外键

数据库设计

数据规范化

什么是范式

好的数据库设计对数据的存储性能和后期的程序开发,都会产生重要的影响。建立科学的,规范的数据库就需要满足一些规则来优化数据的设计和存储,这些规则就称为范式。

三大范式:

目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。

满足最低要求的范式是第一范式(1NF)。在第一范式的基础上进一步满足更多规范要求的称为第二范式(2NF),其余范式以次类推。一般说来,数据库只需满足第三范式(3NF)就行了。

1NF

概念

数据库表的每一列都是不可分割的原子数据项,不能是集合、数组等非原子数据项。即表中的某个列有多个值时,必须拆分为不同的列。简而言之,第一范式每一列不可再拆分,称为原子性。

班级表

学号 姓名 班级
1 张三 一年三班
2 李四 一年二级
3 王五 二年三班

2NF

概念

在满足第一范式的前提下,表中的每一个字段都完全依赖于主键。

所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之,第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候,才会发生不符合第二范式的情况。比如有一个主键有两个列,不能存在这样的属性,它只依赖于其中一个列,这就是不符合第二范式。

第二范式的特点
1)一张表只描述一件事情。
2)表中的每一列都完全依赖于主键。

示例:

3NF

概念

在满足第二范式的前提下,表中的每一列都直接依赖于主键,而不是通过其它的列来间接依赖于主键。

简而言之,第三范式就是所有列不依赖于其它非主键列,也就是在满足 2NF 的基础上,任何非主列不得传递依赖于主键。所谓传递依赖,指的是如果存在”A → B → C”的决定关系,则 C 传递依赖于 A。因此,满足第三范式的数据库表应该不存在如下依赖关系:主键列 → 非主键列 x → 非主键列 y

示例:学生信息表

三大范式小结

范式 特点
1NF 原子性:表中每列不可再拆分。
2NF 不产生局部依赖,一张表只描述一件事情
3NF 不产生传递依赖,表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键。

本博客所有文章除特别声明外,均采用 CC BY-SA 3.0协议 。转载请注明出处!