跟着项目学sql(二) 三大范式

序号	表名	说明
1	permission	权限表
2	user	用户表
3	menu	栏目表
4	news	新闻表
5	clicks	浏览信息表

ER图如下，并没有创建外键约束，因此本文中所有的外键都是指【逻辑外键】：

我们在数据库表设计时，经常说好的设计最起码要遵循第三范式，那现在用三大范式来检验一下我们的设计

-------------------------------------------------------------------------------------------------------------

数据库三大范式

          设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有8种范式(1NF,2NF,3NF,BCNF,4NF,5NF,DKNF,6NF)，每一级范式都是基于前一范式的基础上，例如符合2NF的关系模式，必定符合1NF。一般说来，只需满足第三范式(3NF）就行了。

第一范式：所有属性都是不可分割的原子值

事实上，在目前的DBMS中是不可能拆分属性的。相对于excel来说，mysql的表并没有行合并和列合并的功能，因此从字面意思来看，我们的表很难违反第一范式！

第一范式包括下列指导原则：

1)关系中每个记录的每个属性只能包含一个值;                 ps:无法做到像Excel那样每个记录包含多个值。

2)关系中的每个记录必须包含相同数量的值；                  ps:无法做到像Excel那样每个记录包含不同数量的值

3)关系中的每个记录一定不能相同。                                 ps:要有主键约束或者唯一约束

我们每个表都用非业务主键id列，内容是GUID做主键，符合1NF。

 

第二范式：在第一范式的基础上，要求非主属性都要和码有完全依赖关系 

这句话显然是针对复合主键和联合主键来说的。

1)复合主键：以clicks(id,ip,useragent,news_id,create_time)表为例，我们这里用了非业务主键id列，内容是GUID，避开了2NF。

为了研究2NF，我们删掉id列，再来看这个问题：

表的业务主键是(ip,news_id,create_time)，即某ip在某时间浏览了某条新闻，而浏览器(useragent)是完全依赖于业务主键的，这符合2NF。如果我们在表中增加了一个news_title字段，那news_title是只依赖于news_id的，从而违反了2NF。

mysql中不应该使用具有实际意义的column 作为主键，这是因为主键列的值不建议进行修改。第一是因为主键列经常用作其他表的外键，如果某一表的主键被修改了，那牵一发而动全身。第二是考虑到经常更新的列不适合创建索引，这当然也包括mysql中的主键索引了。

这里我们的每个表都用GUID做主键，从而避免了使用复合主键，也避开了这种情况。

2)联合主键：

为了应对表间多对多的关系，需要创建中间表，在NewsDB中，权限表(permission)和用户表(user)就是多对多的关系。

创建中间表

permission_user(permission_id,user_id,create_time)

(permission_id,user_id)就是联合主键，如果这时候在要表中添加username列，那就违反了2NF，

真实的项目中，我们在user表中用了一个permission字段存储用户权限id，把用户的多个权限id用逗号隔开，从而免去了中间表，避免了违反第二范式的可能。这种做法能够成立，主要原因是权限的数量比较固定，而且后台用户数量很小。事实上我们每次对权限表的删除操作，要去更新所有的用户的permission字段，用以去除脏数据，这在用户量多的情况下，是很失败的做法。

比如，我们的新闻表(news)中同样有一个menus字段，用来存储新闻所属的栏目id，多个栏目id用逗号隔开。这里也没有采用中间表，就是很严重的设计问题了！我们应该使用中间表news_menus(id,news_id,menu_id)

除了上面讲的脏数据处理的问题，更主要的还是前台的查询性能问题，相比之下后台的所有功能都应该给它让路。要显示某栏目下的新闻，直接去中间表中查找对应的menu_id,即可筛选出所有的news_id，这变相的起到了索引表的效果。

反观是原来的方式，menus字段里多个栏目id用逗号隔开，我们就只能在where条件中用mysql中的locate()函数了(类似indexOf的功能)，这显然是不能接受的。

这里添加中间表news_menus(id,news_id,menu_id)

 

第三范式：任何非主属性不依赖于其它非主属性。 

           这显然是针对外键来说的，这里以News表为例：

news(id,title,keywords,...,user_id,username,...state),username依赖于user_id，从而在表面上违反了第三范式。这里多存一个username主要是为了在用户被删除的情况下，还能取到用户名。这关系到后面的报表统计问题和应对可能发生的薪酬纠纷。

如果用户被删除，同时news表里只有一个user_id，那username就无从查起了。也就是说，现在即使把user表中的记录删除，news表中的username仍不受影响，这已经说明news表中的username字段并不依赖于user_id，因此这里并没有违反3NF。

我们还是以上面刚刚添加的新闻菜单表中间表news_menus(id,news_id,menu_id)为例，如果这时候在要表中添加news_title列，则违反了3NF

 

第二范式和第三范式的区别：

2NF针对复合主键和联合主键，表中属性存在部分依赖

3NF针对外键，在满足2NF的基础上，表中属性依赖于外键，造成传递依赖。

记住：

news_menus(news_id,menu_id,news_title)       违反2NF，部分依赖

news_menus(id,news_id,menu_id,news_title)    违反3NF，传递依赖

一般的在mysql都会用非业务主键，避开了部分依赖，反而造成了传递依赖出现的概率比较大！

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

最后修订的数据库： 

序号	表名	说明
1	permission	权限表
2	user	用户表
3	menu	栏目表
4	news	新闻表
5	news_menus	新闻_栏目表
6	clicks	浏览信息表

 

1、表名：permission(权限表)

序号	名称	数据类型	长度	小数位	允许空值	主键	默认值	说明
1	id	varchar(45)	45	0	N	Y		主键
2	name	varchar(45)	45	0	Y			权限名称
3	orderby	int	10	0	Y		0	排序
4	create_time	datetime	23	0	Y		CURRENT_TIMESTAMP	创建时间

 

2、表名：user(用户表)

序号	名称	数据类型	长度	小数位	允许空值	主键	默认值	说明
1	id	varchar(45)	45	0	N	Y		主键
2	username	varchar(45)	45	0	N			用户名
3	password	varchar(45)	45	0	N			密码
4	permission	varchar(200)	200	0	Y			权限id (多个用逗号隔开)
5	create_time	datetime	23	0	Y		CURRENT_TIMESTAMP	创建时间

 

3、表名：menu(栏目表)

序号	名称	数据类型	长度	小数位	允许空值	主键	默认值	说明
1	id	varchar(45)	45	0	N	Y
2	name	varchar(45)	45	0	N			栏目名称
3	url	varchar(200)	200	0	N			栏目url
4	orderby	int	10	0	Y		0	排序字段
5	create_time	datetime	23	0	Y		CURRENT_TIMESTAMP	创建时间

 

4、表名：news(新闻表)

序号	名称	数据类型	长度	小数位	允许空值	主键	默认值	说明
1	id	varchar(45)	45	0	N	Y		主键
2	title	varchar(200)	200	0	N			标题
3	keywords	varchar(200)	200	0	Y			关键词
4	menus	varchar(200)	200	0	N			所属栏目(逗号隔开)
5	author	varchar(45)	45	0	N			新闻来源
6	thumb	varchar(200)	200	0	Y			缩略图
7	content	text	65535	0	N			新闻内容
8	user_id	varchar(45)	45	0	Y			创建人id
9	username	varchar(45)	45	0	Y			创建人
10	create_time	datetime	23	0	Y		CURRENT_TIMESTAMP	创建时间
11	edit_time	datetime	23	0	Y			最后编辑时间
12	state	tinyint	1	0	N		0	新闻状态 (0未发布1已发布)

 

5、表名：news_menus(新闻_栏目表)

序号	名称	数据类型	长度	小数位	允许空值	主键	默认值	说明
1	id	varchar(45)	45	0	N	Y
2	news_id	varchar(45)	45	0	N
3	menu_id	varchar(45)	45	0	N

 

 

6、表名：clicks(浏览信息表)

序号	名称	数据类型	长度	小数位	允许空值	主键	默认值	说明
1	id	varchar(45)	45	0	N	Y
2	ip	varchar(45)	45	0	N			浏览人ip地址
3	useragent	varchar(500)	500	0	Y			浏览器useragent
4	news_id	varchar(45)	45	0	N			新闻id
5	create_time	datetime	23	0	Y		CURRENT_TIMESTAMP	创建时间