数据库设计

2024-01-25 12:32:40

数据库中存放很多表，设计数据库就是设计表。表是对现实生活中一些事务的抽象。

要设计数据库就要明白表的关系，也要知道数据库设计的一些准则，也称范式。

数据库的设计直接影响了项目开发的难易程度，也直接影响了项目开发完后运行的性能。

多表之间的关系：

1、一对多（或多对一），如部门和员工，一个部门有多个员工，一个员工只能对应一个部门。

2、多对多，学生和课程，一个学生可以选择很多课程，一个课程也可以被很多学生选择。

3、一对一，如人和身份证，一个人只有一个身份证，一个身份证只能对应一个人。开发中，一对一的关系很少存在，了解即可。

一、多表关系_一对多关系实现

如：部门和员工

创建表时如何实现一对多的关系呢？在多的一方建立外键去指向一的一方的主键。

二、多表关系_多对多关系实现

如：学生和课程

创建表时如何实现多对多的关系呢？需要借助第三张中间表，中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键。

注意：每一对不应该重复，故需要设置联合主键。

联合主键，如下所示：

ALTER TABLE sys_user_role  ADD  CONSTRAINT PK_user_role PRIMARY KEY( user_id, role_id);

三、多表关系_一对一关系实现

如：人和身份证

创建表时如何实现一对一的关系呢？在任意一方添加唯一外键去指向一的一方的主键。注意：需要让外键唯一，唯一约束。

如果是一对一关系，一般都会合成一张表。

案例：

旅游线路分类、旅游线路、用户

一个旅游线路分类对应多个旅游线路，一个线路对应一个分类。分类表和线路表是一对多的关系。故在多的一方添加外键指向一的一方的主键。

一个用户对应多个旅游线路，一个线路可以被多个用户所收藏。故线路表和用户表是多对多的关系。

-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键，自动增长
-- cname 旅游线路分类名称非空，唯一，字符串 100
CREATE TABLE tab_category (
    cid INT PRIMARY KEY AUTO_INCREMENT,
    cname VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 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)
);
/*创建用户表 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)
);
/*
创建收藏表 tab_favorite
rid 旅游线路 id，外键
date 收藏时间
uid 用户 id，外键
rid 和 uid 不能重复，设置复合主键，同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
    rid INT, -- 线路id
    DATE DATETIME,
    uid INT, -- 用户id
    -- 创建复合主键
    PRIMARY KEY(rid,uid), -- 联合主键
    FOREIGN KEY (rid) REFERENCES tab_route(rid),
    FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

在sqlyog中的架构设计器中展示如下：

四、数据库设计的范式

1、概念

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求。

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

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。一般情况下，遵守了前三种范式，数据库的设计就没有什么问题了。

2、分类

第一范式（1NF）：每一列都是不可分割的原子数据项。

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于码（在1NF基础上消除非主属性对主码的部分函数依赖）。

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

由于系这一列可以分割为两列，故不满足每一列都是不可分割的原子数据项的要求。修改如下就变成原子列了。

如上，遵循了第一范式。第一范式是数据库设计表的基本要求，所有的表都遵循第一范式。

存在的问题：

（1）、存在非常严重的数据冗余。

（2）、数据添加存在问题，如果现在新增了一个系，如计算机系，数据不合法。如下所示：

（3）、数据删除存在问题，张无忌同学毕业了，删除数据时，会将系得数据一起删除。

上述三个问题导致数据库的设计不合适。

3、几个概念

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）、码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码。

学号没有被其他所有属性依赖，因为不能通过学号属性确定唯一的其他属性，因为通过学号不能确定唯一的分数，所以码为学号和课程名称。

第二范式要消除部分依赖，由于分数是完全依赖于码（学号和课程名称），而姓名、系名、系主任只依赖于学号，故是部分依赖。

现在需要做表的拆分

分数（非主属性）完全依赖与码（学号和课程名称），这样，非主属性完全依赖于主属性。

学号为主属性，姓名、系名、系主任完全依赖学号。

例如：该表中码为：（学号，课程名称）

主属性：码属性组中的所有属性。

非主属性：除过码属性组的属性。

现在虽然解决了非常严重的数据冗余，但是数据添加和数据删除都存在问题。张无忌毕业了会将系的数据一起删除。

第三范式可以解决上述问题。第三范式消除传递依赖。

学生表中，现在姓名和系名完全依赖与学号，不存在传递依赖。

系表中，系主任完全依赖于系名。

现在的三种表遵循第三范式。解决了添加数据的问题，即在系表中添加系名（计算机系）和系主任没有问题。也解决了删除数据的问题，张无忌毕业了，经济学仍然被保留着。

在实际工作中，应用这些范式来验证你设计的表是否合理。

码农公寓