10.1 为何需要规约
- 数据库比较复杂,
10.1.1糟糕的数据库设计
- 数据冗余,浪费空间
- 程序的性能差
- 数据库插入更新都比较麻烦,异常(不使用物理外键)
10.1.2 良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便开发系统
10.1.3 软件开发,数据库设计
- 分析需求:分析业务和需要处理的数据需求
- 概要设计:设计关系图E-R图
- 若字段命名有多个单词不用驼峰命名,用_隔开
- 关注:关系型数据库建关注中间表,nosql数据库redis{1111:{1,2,3}}
10.1.4 例子:设计数据库步骤(blog)
- 收集信息,分析需求
-
- 用户表-登录注册注销,创建人
show CREATE TABLE `user`
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`name` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名',
`password` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码',
`tellphone` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话',
`email` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
`sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '性别',
`age` int(3) DEFAULT NULL COMMENT '年龄',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户状态',
`version` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '版本号',
`create_user_id` int(60) NOT NULL COMMENT '创建用户id',
`open_id` varchar(500) COLLATE utf8mb4_general_ci NOT NULL COMMENT '微信id',
`avatar` varchar(500) COLLATE utf8mb4_general_ci NOT NULL COMMENT '头像地址链接',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
- 分类表-文章分类,创建人
show CREATE TABLE `category`
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '分类id',
`name` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户状态',
`version` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '版本号',
`create_user_id` int(60) NOT NULL COMMENT '创建用户id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
- 文章表-文章信息
show CREATE TABLE `blog`
CREATE TABLE `blog` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '博客id',
`title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '标题',
`author_id` int(11) NOT NULL COMMENT '作者id',
`category_id` int(11) NOT NULL COMMENT '分类id',
`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '内容',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`love` int(11) DEFAULT NULL COMMENT '点赞',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
- 评论表
show CREATE TABLE `comment`
CREATE TABLE `comment` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论id',
`blog_id` int(11) NOT NULL COMMENT '博客id',
`user_id` int(11) NOT NULL COMMENT '评论人',
`content` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '评论内容',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '评论时间',
`user_id_parent` int(11) DEFAULT NULL COMMENT '恢复人的id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
- 链接表
show CREATE TABLE `links`
CREATE TABLE `links` (
`id` int(11) NOT NULL COMMENT '链接标题',
`links` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '链接标题',
`href` varchar(2000) COLLATE utf8mb4_general_ci NOT NULL COMMENT '链接',
`sort` int(10) NOT NULL COMMENT '排序',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
-
自定义表config(系统配置信息,关键字或主字段)字段形式:key:value
-
说说表(发表 id,content,create_time)
-
标识实体(需求落地到每个字段)
-
标识实体间关系
-
- 写博客:user>blog
- 建分类:user>category
- 评论:user>user>blog
- 关注:user>user_follow
CREATE TABLE `uesr_follow` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL COMMENT '被关注人id',
`follow_id` int(11) NOT NULL COMMENT '关注人id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
10.2函数依赖
- 某个属性集决定另一个属性集时,称另一属性集依赖于该属性集
- 定义:设 R 为任一给定关系,如果对于 R 中属性 X 的每一个值,R 中的属性 Y 只有唯一值与之对应,则称 X 函数决定 Y 或称 Y 函数依赖于 X ,记作 X—>Y。其中,X 称为决定因素。
10.2.1 完全函数依赖
- 书上定义的意思基本是:如果存在 X 属性集(注意是集合,说明是联合主键)决定 唯一的 Y ,且 X 中的任一子集都不能决定 唯一的 Y,则 Y 完全依赖于 X。
10.2.2 完全函数依赖
- 于完全函数依赖区别是:就是 X 的属性集中任一子集可以决定唯一的 Y
10.2.2 传递函数依赖
- 定义:设 R 为任一给定关系, X Y Z 为其不同的属性子集,若 X —> Y, Y 不决定 X 且 Y —>Z,则有 X —>Z,称为 Z 传递函数依赖于 X
10.3 三大范式
- 为何需要规约?
- 信息重复
- DML更新、插入、删除异常
- 无法保证数据的一致性
- 设计关系型数据库时,遵从不同的规范要求,设计出合理的关系型数据库,这些不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据库冗余越小
- 目前关系型数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF,又称完美范式)。一般来说,数据库只需要满足第三范式就行了
10.3.1 第一范式(1NF)
- 最基本的范式:保证每列的原子性根据系统的实际需求来定
- 数据库表中的所有字段值都是不可分解的原子值,就说明该数据库满足了第一范式
10.3.2 第二范式(2NF)
- 保证第一范式
- 保证一张表只描述一件事情
- 消除了非主属性对主属性的部分函数依赖
10.3.3 第三范式(3NF)
- 保证第一、二范式
- 保证每列都和主键直接相关,不能间接相关
- 消除了非主属性对主属性的传递地函数依赖
10.3.4 规范和性能问题
- 阿里规范关联表查询不超过3张
- 从商业化的需求和目标(用户成本体验)性能>规范性
- 在规范性能问题时,适当考虑规范性
- 给表加一些冗余字段,有多表查询变为单表查询
- 读远大于写,增加的耗时相对于读取可以忽略不计,故意加一些字段列,从大数据量减为小数据量查询
- 加索引也会有开销问题