MySQL之数据库表的设计

一、表与表关系分类

1.一对多

一对多，例如：一个班级对应多名学生（客户和订单、分类和商品)

建表原则: 主外键的关系。 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

2.多对多

多对多， 例如：一名老师对应多名学生,一名学生又对应多名老师  (学生和课程、用户和角色)

建表原则: 借助中间表，拆分为两个一对多。 创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

3.一对一

一对一，例如： 一个人只能对应一个身份证ID。在实际开发中使用不多，因为一对一可以设计成一张表。

建表原则: 
1.外键唯一   特殊的一对多，主表的主键和从表的外键（唯一），形成主外键关系，外键唯一 UNIQUE

2.外键又是主键 主表的主键和从表的主键，形成主外键关系

二、表与表关系设计

1.创建四张表

1.商品分类表 category
2.商品表 goods
3.用户表 user
4.收藏中间表 favorite

2.表间关系分析:

分类表与商品表是一对多关系(一个分类对应多个商品）

商品表与用户表是多对多关系(一个商品可以被多个用户收藏，一个用户可以收藏多个商品)

商品表与收藏中间表是一对多(一个商品可以被收藏多次)

用户表与收藏中间表是一对多(一个用户可以收藏多次不同商品)

3.设计例子

		-- 创建分类表 category
		-- cid 分类主键，自动增长
		-- cname 分类名称非空，唯一
		CREATE TABLE category (
			cid INT PRIMARY KEY AUTO_INCREMENT,
			cname VARCHAR(100) NOT NULL UNIQUE
		);
		
		-- 创建商品表 goods
		-- gid 商品主键，自动增长
		-- gname 商品名称非空，唯一
		-- price 商品价格
		-- cid 外键，所属分类
		CREATE TABLE goods(
			gid INT PRIMARY KEY AUTO_INCREMENT,
			gname VARCHAR(100) NOT NULL UNIQUE,
			price DOUBLE,
			cid INT,
			FOREIGN KEY (cid) REFERENCES category(cid)
		);
		
		
		-- 创建用户表 user
		-- uid 用户主键，自增长
		-- username 唯一，非空
		-- password 非空
		-- name 姓名
		-- sex 性别
		-- phone 手机号
		
		CREATE TABLE user (
			uid INT PRIMARY KEY AUTO_INCREMENT,
			username VARCHAR(100) UNIQUE NOT NULL,
			PASSWORD VARCHAR(30) NOT NULL,
			NAME VARCHAR(20),
			sex CHAR(1) DEFAULT '未定义',
			phone VARCHAR(11),
		);
		
		
		-- 创建收藏中间表 favorite
		-- gid 商品id，外键
		-- uid 用户id，外键
		-- gid 和 uid 设置为联合主键，同一个用户不能收藏同一个商品两次
		
		CREATE TABLE favorite (
			gid INT, -- 商品id
			uid INT, -- 用户id
			PRIMARY KEY(gid,uid), -- 联合主键
			FOREIGN KEY (gid) REFERENCES goods(gid),
			FOREIGN KEY(uid) REFERENCES user(uid)
		);
		

三、数据库范式

1.什么是范式?

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。
目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）

2.函数依赖

1. 函数依赖：A-->B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
			
2. 完全函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
			
3. 部分函数依赖：A-->B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
				
4. 传递函数依赖：A-->B, B -- >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
				
5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码

3.三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般来说，数据库只需满足第三范式(3NF）就行了

1.第一范式（1NF）

所谓第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。在符合第一范式（1NF）表中的每个域值只能是实体的一个属性或一个属性的一部分。简而言之，第一范式就是无重复的域。

1. 数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。

2. 简言之，第一范式每一列不可再拆分，称为原子性。

2.第二范式（2NF )

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

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

2.简言之，第二范式就是所有列完全依赖于主键列。

3.第三范式（3NF）

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

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

2.简言之，第三范式就是所有列不依赖于其它非主键列，任何非主列不得传递依赖于主键。

3.三大范式概括

1NF 原子性：表中每列不可再拆分。

2NF 不产生局部依赖，所有列完全依赖于主键列。

3NF 不产生传递依赖，表中每一列都直接依赖于主键。而不是通过其它列间接依赖于主键