数据库设计规范(必要性说明)

数据库设计规范

MYSQL数据库规划规范
1、数据库命名规范
选用26个英文字母(区别大小写)和0-9的自然数(常常不需求)加上下划线’_’组成;
命名简练明晰(长度不能超越30个字符);
例如：user,stat,log,也能够wifi_user,wifi_stat,wifi_log给数据库加个前缀;
除非是备份数据库能够加0-9的自然数：user_db_20151210;
2、数据库表名命名规范
选用26个英文字母(区别大小写)和0-9的自然数(常常不需求)加上下划线’_’组成;
命名简练明晰,多个单词用下划线’_’分隔;
例如：user_login,user_profile,user_detail,user_role,user_role_relation,
user_role_right,user_role_right_relation
表前缀’user_’能够有用的把相同联系的表显现在一起;
3、数据库表字段名命名规范
选用26个英文字母(区别大小写)和0-9的自然数(常常不需求)加上下划线’_’组成;
命名简练明晰,多个单词用下划线’_’分隔;
例如：user_login表字段user_id,user_name,pass_word,eamil,tickit,status,mobile,add_time;
每个表中有必要有自增主键,add_time(默认体系时刻)
表与表之间的相相关字段称号要求尽可能的相同;
4、数据库表字段类型规范
用尽量少的存储空间来存数一个字段的数据;
例如：能运用int就不要运用varchar、char,能用varchar(16)就不要运用varchar(256);
IP地址最好运用int类型;
固定长度的类型最好运用char,例如：邮编;
能运用tinyint就不要运用smallint,int;
最好给每个字段一个默认值,最好不能为null;
5、数据库表索引规范
命名简练明晰,例如：user_login表user_name字段的索引应为user_name_index仅有索引;
为每个表创立一个主键索引;
为每个表创立合理的索引;
建立复合索引请慎重;
6、简略熟悉数据库范式
榜首范式(1NF)：字段值具有原子性,不能再分(一切联系型数据库体系都满意榜首范式);
例如：名字字段,其间姓和名是一个整体,假如区别姓和名那么有必要设立两个独立字段;
第二范式(2NF)：一个表有必要有主键,即每行数据都能被仅有的区别;
补白：有必要先满意榜首范式;
第三范式(3NF)：一个表中不能包容其他相关表中非关键字段的信息,即数据表不能有沉余字段;
补白：有必要先满意第二范式;
数据库的三范式：
①字段不行分。
②有主键，非主键字段依靠主键。
③非主键字段不能相互依靠。
补白：往往咱们在规划表中不能恪守第三范式,由于合理的沉余字段将会给咱们削减join的查询;
例如：相册表中会添加图片的点击数字段,在相册图片表中也会添加图片的点击数字段;
MYSQL数据库规划准则
1、中心准则
不在数据库做运算;
cpu核算务必移至事务层;
操控列数量(字段少而精,字段数主张在20以内);
平衡范式与冗余(功率优先；往往牺牲范式)
回绝3B(回绝大sql句子：bigsql、回绝大事物：bigtransaction、回绝大批量：bigbatch);
2、字段类准则
用好数值类型(用合适的字段类型节省空间);
字符转化为数字(能转化的最好转化,相同节省空间、进步查询功能);
避免运用NULL字段(NULL字段很难查询优化、NULL字段的索引需求额定空间、NULL字段的复合索引无效);
少用text类型(尽量运用varchar替代text字段);
3、索引类准则
合理运用索引(改进查询,减慢更新,索引一定不是越多越好);
字符字段有必要建前缀索引;
不在索引做列运算;
innodb主键推荐运用自增列(主键建立聚簇索引,主键不该该被修正,字符串不该该做主键)(理解Innodb的索引保存结构就知道了);
不必外键(由程序保证束缚);
4、sql类准则
sql句子尽可能简略(一条sql只能在一个cpu运算,大句子拆小句子,削减锁时刻,一条大sql能够堵死整个库);
简略的事务;
避免运用trig/func(触发器、函数不必客户端程序取而代之);
不必select*(耗费cpu,io,内存,带宽,这种程序不具有扩展性);
OR改写为IN(or的功率是n级别);
OR改写为UNION(mysql的索引兼并很弱智);
selectidfromtwherephone=’159′orname=‘john’;
=>
selectidfromtwherephone=’159′
union
selectidfromtwherename=’jonh’
避免负向%;
慎用count(*);
limit高效分页(limit越大，功率越低);
运用unionall替代union(union有去重开销);
少用衔接join;
运用groupby;
请运用同类型比较;
打散批量更新;
5、功能剖析东西
showprofile;
mysqlsla;
mysqldumpslow;
explain;
showslowlog;
showprocesslist;
数据库的规划准则
1.原始单据与实体之间的联系
可所以1对1、一对多、多对多的联系。在一般情况下，它们是1对1的联系：即一张原始单据对应且只对应一个实体。
在特别情况下，它们可能是一对多或多对一的联系，即一张原始单证对应多个实体，或多张原始单证对应一个实体。
这里的实体能够理解为根本表。明晰这种对应联系后，对咱们规划录入界面大有优点。
〖例1〗：一份员工履历资料，在人力资源信息体系中，就对应三个根本表：员工根本情况表、社会联系表、作业简历表。
这便是“一张原始单证对应多个实体”的典型比如。
2.主键与外键
一般而言，一个实体不能既无主键又无外键。在E—R图中,处于叶子部位的实体,能够界说主键，也能够不界说主键
(由于它无子孙),但有必要要有外键(由于它有父亲)。
主键与外键的规划，在大局数据库的规划中，占有重要位置。当大局数据库的规划完结以后，有个美国数据库规划专
家说：“键，到处都是键，除了键之外，什么也没有”，这便是他的数据库规划经历之谈，也反映了他对信息体系核
心(数据模型)的高度笼统思想。由于：主键是实体的高度笼统，主键与外键的配对，表明实体之间的衔接。
3.根本表的性质
根本表与中心表、暂时表不同，由于它具有如下四个特性：
(1)原子性。根本表中的字段是不行再分解的。
(2)原始性。根本表中的记载是原始数据（根底数据）的记载。
(3)演绎性。由根本表与代码表中的数据，能够派生出一切的输出数据。
(4)稳定性。根本表的结构是相对稳定的，表中的记载是要长时刻保存的。
理解根本表的性质后，在规划数据库时，就能将根本表与中心表、暂时表区别开来。
4.范式规范
根本表及其字段之间的联系,应尽量满意第三范式。可是，满意第三范式的数据库规划，往往不是最好的规划。
为了进步数据库的运转功率，常常需求下降范式规范：恰当添加冗余，达到以空间换时刻的意图。
〖例2〗：有一张寄存产品的根本表，如表1所示。“金额”这个字段的存在，标明该表的规划不满意第三范式，
由于“金额”能够由“单价”乘以“数量”得到，阐明“金额”是冗余字段。可是，添加“金额”这个冗余字段，
能够进步查询核算的速度，这便是以空间换时刻的作法。
在Rose2002中，规则列有两种类型：数据列和核算列。“金额”这样的列被称为“核算列”，而“单价”和
“数量”这样的列被称为“数据列”。
表1产品表的表结构
产品称号产品型号单价数量金额
电视机29吋2,50040100,000
5.浅显地理解三个范式
浅显地理解三个范式，关于数据库规划大有优点。在数据库规划中，为了更好地应用三个范式，就有必要浅显地理解
三个范式(浅显地理解是够用的理解，并不是最科学最精确的理解)：
榜首范式：1NF是对特点的原子性束缚，要求特点具有原子性，不行再分解；
第二范式：2NF是对记载的专一性束缚，要求记载有专一标识，即实体的专一性；
第三范式：3NF是对字段冗余性的束缚，即任何字段不能由其他字段派生出来，它要求字段没有冗余。
没有冗余的数据库规划能够做到。可是，没有冗余的数据库未必是最好的数据库，有时为了进步运转功率，就有必要降
低范式规范，恰当保存冗余数据。具体做法是：在概念数据模型规划时恪守第三范式，下降范式规范的作业放到物理
数据模型规划时考虑。下降范式便是添加字段，允许冗余。
6.要长于辨认与正确处理多对多的联系
若两个实体之间存在多对多的联系，则应消除这种联系。消除的办法是，在两者之间添加第三个实体。这样，本来一
个多对多的联系，现在变为两个一对多的联系。要将本来两个实体的特点合理地分配到三个实体中去。这里的第三个
实体，实质上是一个较杂乱的联系，它对应一张根本表。一般来讲，数据库规划东西不能辨认多对多的联系，但能处
理多对多的联系。
〖例3〗：在“图书馆信息体系”中，“图书”是一个实体，“读者”也是一个实体。这两个实体之间的联系，是一
个典型的多对多联系：一本图书在不同时刻能够被多个读者借阅，一个读者又能够借多本图书。为此，要在二者之
间添加第三个实体，该实体取名为“借还书”，它的特点为：借还时刻、借还标志(0表明借书，1表明还书)，别的，
它还应该有两个外键(“图书”的主键，“读者”的主键)，使它能与“图书”和“读者”衔接。
注视：
图书1和该实体取名为“借还书”n
读者1和该实体取名为“借还书”n
7.主键PK的取值办法
PK是供程序员运用的表间衔接东西，可所以一无物理含义的数字串,由程序主动加1来完结。也可所以有物理含义
的字段名或字段名的组合。不过前者比后者好。当PK是字段名的组合时，主张字段的个数不要太多，多了不但索引
占用空间大，并且速度也慢。
8.正确认识数据冗余
主键与外键在多表中的重复呈现,不属于数据冗余，这个概念有必要清楚，事实上有许多人还不清楚。非键字段的重
复呈现,才是数据冗余！并且是一种初级冗余，即重复性的冗余。高级冗余不是字段的重复呈现，而是字段的派生呈现。
〖例4〗：产品中的“单价、数量、金额”三个字段，“金额”便是由“单价”乘以“数量”派生出来的，它便是冗余，
并且是一种高级冗余。冗余的意图是为了进步处理速度。只要初级冗余才会添加数据的不一致性，由于同一数据，可
能从不同时刻、地址、人物上多次录入。因此，咱们发起高级冗余(派生性冗余)，反对初级冗余(重复性冗余)。
9.E–R图没有规范答案
信息体系的E–R图没有规范答案，由于它的规划与画法不是专一的，只要它覆盖了体系需求的事务范围和功能内容，
便是可行的。反之要修正E–R图。尽管它没有专一的规范答案，并不意味着能够随意规划。好的E—R图的规范是：
结构明晰、相关简练、实体个数适中、特点分配合理、没有初级冗余。
10.视图技能在数据库规划中很有用
与根本表、代码表、中心表不同，视图是一种虚表，它依靠数据源的实表而存在。视图是供程序员运用数据库的
一个窗口，是基表数据归纳的一种形式,是数据处理的一种办法，是用户数据保密的一种手法。为了进行杂乱处理、
进步运算速度和节省存储空间,视图的界说深度一般不得超越三层。若三层视图仍不够用,则应在视图上界说暂时表,
在暂时表上再界说视图。这样反复交迭界说,视图的深度就不受限制了。
关于某些与国家政治、经济、技能、军事和安全利益有关的信息体系，视图的作用更加重要。这些体系的根本表完
成物理规划之后，立即在根本表上建立榜首层视图，这层视图的个数和结构，与根本表的个数和结构是完全相同。
并且规则，一切的程序员，一律只准在视图上操作。只要数据库管理员，带着多个人员一起掌握的“安全钥匙”，
才干直接在根本表上操作。请读者想想：这是为什么？
11.中心表、报表和暂时表
中心表是寄存核算数据的表，它是为数据仓库、输出报表或查询结果而规划的，有时它没有主键与外键(数据仓
库在外)。暂时表是程序员个人规划的，寄存暂时记载，为个人所用。基表和中心表由DBA保护，暂时表由程序员
自己用程序主动保护。
12.完整性束缚表现在三个方面
域的完整性：用Check来完结束缚，在数据库规划东西中，对字段的取值范围进行界说时，有一个Check按钮，通
过它界说字段的值城。
参照完整性：用PK、FK、表级触发器来完结。
用户界说完整性：它是一些事务规则，用存储进程和触发器来完结。
13.避免数据库规划打补丁的办法是“三少准则”
(1)一个数据库中表的个数越少越好。只要表的个数少了，才干阐明体系的E–R图少而精，去掉了重复的剩余的
实体，形成了对客观国际的高度笼统，进行了体系的数据集成，避免了打补丁式的规划；
(2)一个表中组合主键的字段个数越少越好。由于主键的作用，一是建主键索引，二是做为子表的外键，所以组
合主键的字段个数少了，不只节省了运转时刻，并且节省了索引存储空间；
(3)一个表中的字段个数越少越好。只要字段的个数少了，才干阐明在体系中不存在数据重复，且很少有数据冗
余，更重要的是催促读者学会“列变行”，这样就避免了将子表中的字段拉入到主表中去，在主表中留下许
多空余的字段。所谓“列变行”，便是将主表中的一部分内容拉出去，别的单独建一个子表。这个办法很简
单，有的人便是不习惯、不采纳、不履行。
数据库规划的有用准则是：在数据冗余和处理速度之间找到合适的平衡点。“三少”是一个整体概念，归纳观念，
不能孤立某一个准则。该准则是相对的，不是绝对的。“三多”准则必定是错误的。试想：若覆盖体系相同的功
能，一百个实体(共一千个特点)的E–R图，必定比二百个实体(共二千个特点)的E–R图，要好得多。
发起“三少”准则，是叫读者学会运用数据库规划技能进行体系的数据集成。数据集成的步骤是将文件体系集成
为应用数据库，将应用数据库集成为主题数据库，将主题数据库集成为大局归纳数据库。集成的程度越高，数据
共享性就越强，信息孤岛现象就越少，整个企业信息体系的大局E—R图中实体的个数、主键的个数、特点的个数
就会越少。
发起“三少”准则的意图，是避免读者运用打补丁技能，不断地对数据库进行增修改，使企业数据库变成了随意
规划数据库表的“垃圾堆”，或数据库表的“大杂院”，最终形成数据库中的根本表、代码表、中心表、暂时表
乱七八糟，不计其数（即动态创表而添加表数量），导致企事业单位的信息体系无法保护而瘫痪。
“三多”准则任何人都能够做到，该准则是“打补丁办法”规划数据库的歪理学说。“三少”准则是少而精的
准则，它要求有较高的数据库规划技巧与艺术，不是任何人都能做到的，由于该准则是根绝用“打补丁办法”
规划数据库的理论依据。
14.进步数据库运转功率的办法
在给定的体系硬件和体系软件条件下，进步数据库体系的运转功率的办法是：
(1)在数据库物理规划时，下降范式，添加冗余,少用触发器,多用存储进程。
(2)当核算十分杂乱、并且记载条数十分巨大时(例如一千万条)，杂乱核算要先在数据库外面，以文件体系方
式用C++语言核算处理完结之后，最终才入库追加到表中去。这是电信计费体系规划的经历。
(3)发现某个表的记载太多，例如超越一千万条，则要对该表进行水平切割。水平切割的做法是，以该表主键
PK的某个值为界限，将该表的记载水平切割为两个表（即能够表保护表行数过大手动切割为两个建个两表union的视图对程序透明）。若发现某个表的字段太多，例如超越八十个，则
笔直切割该表，将本来的一个表分解为两个表。
(4)对数据库管理体系DBMS进行体系优化，即优化各种体系参数，如缓冲区个数。
(5)在运用面向数据的SQL语言进行程序规划时，尽量采纳优化算法。
总归，要进步数据库的运转功率，有必要从数据库体系级优化、数据库规划级优化、程序完结级优化，这三
个层次上同时下功夫。

数据库设计规范必要性说明

一、规划数据库的必要性
1.为什么要规划数据库
当数据库比较复杂（如数据量大，表较多，事务联络复杂）时，咱们需求先规划数据库，
因为：
良好的数据库规划：
节约数据的存储空间
能够确保数据的完整性
便利进行数据库运用体系的开
糟糕的数据库规划：
数据冗余、存储空间浪费
数据更新和插入的反常
二、软件项目开发周期中的数据库开发
1.数据库生命周期
数据库的生命周期首要分为四个阶段：
1.需求剖析阶段：（此阶段在软件项目开发周期中的需求剖析阶段进行）这个阶段首要是搜集并剖析用户的事务和数据处理需求
搜集信息（咱们需求与数据的创造者和运用者进行访谈。对访谈取得的信息进行收拾、剖析，并撰写正式的需求文档。需求文档中需包括：需求处理的数据；数据的天然联络；数据库完结的硬件环境、软件渠道等；）
标识方针
标识每个方针的特点
标识方针之间的联络
2.逻辑规划阶段：（此阶段在软件项目开发周期中的概要规划和具体规划阶段进行）
运用ER或UML建模技术，创立概念数据模型图，展现所有数据以及数据间联络。终究概念数据模型有必要被转化为范式化的表。
a.制作E-R图
b.将E-R图转换为表格
c.运用三大范式规范化表格
3.物理规划阶段：
依据特定数据库办理体系所供给的多种存储结构和存取方法等依靠于具体计算机结构的各项物理规划措施，对具体的运用使命选定最合适的物理存储结构(包括文件类型、索引结构和数据的寄存次第与位逻辑等)、存取方法和存取途径等。这一步规划的成果便是所谓“物理数据库”。
a.数据库物理规划的方针是尽或许优化功能。
b.物理规划阶段，表结构或许需求进行重构来满意功能上的需求，这被称为反范式化。
c.反范式化的过程包括：
I.辨别关键性流程，如频频运转、大容量、高优先级的处理操作
II.经过增加冗余来提高关键性流程的功能
III.评价所造成的价值（对查询、修正、存储的影响）和或许丢失的数据一致性
d.完结维护阶段：
I.当规划完结之后，运用数据库办理体系（DBMS）中的数据定义言语（DDL）来创立数据结构。
II.数据库创立完结后，运用程序或用户能够运用数据操作言语（DML）来运用（查询、修正等）该数据库。
III.一旦数据库开端运转，就需求对其功能进行监督。当数据库功能无法满意要求或用户提出新的功能需求时，就需求对该数据库进行再规划与修正。这形成了一个循环：监督–>再规划–>修正–>监督…。
三、数据库规划完结过程
a.需求剖析阶段
I.搜集信息
与该体系有关人员进行交流、坐谈，充沛了解数据库需求完结的使命
b.标识方针（实体－Entity）
标识数据库要办理的关键方针或实体
3.标识每个实体的特点（Attribute）
4.标识方针之间的联络（Relationship）
2.逻辑规划阶段
1.制作E-R图
实体联络图(EntityRelationshipDiagram)，是指供给了表明实体型、特点和联络的方法，用来描绘现实国际的概念模型。
ER模型中的根本元素
根本的ER模型包括三类元素：实体、联络、特点
元素阐明：
1.实体（Entities）：实体是首要的数据方针，常用于表明一个人、地方、某样事物或某个事件。一般称号单词的首字母大写。
辨别强实体与弱实体：
在现实国际中，有时某些实体对于另一些实体有很强的依靠联络，即一个实体的存在有必要以另一实体的存在为条件。前者就称为“弱实体”，后者称为“强实体”。比如在人事办理体系中，职工子女的信息便是以职工的存在为条件的，子女实体是弱实体，子女与职工的联络是一种依靠联络。
强实体内部有仅有的标识符。
弱实体（weakentities）的标识符来自于一个或多个其它强实体。弱实体用双线长方形框表明，依靠于强实体而存在。
2.联络（Relationships）：联络表明一个或多个实体之间的联络。联络依靠于实体，一般没有物理概念上的存在。
联络最常用来表明实体之间，联络可分为以下3种类型：
1对1联络(1∶1)：例如，一个部分有一个经理，而每个经理只在一个部分任职，则部分与经理的联络是1对1的。
一对多联络(1∶N)：例如，部分与职工之间存在一对多的联络“所属”，即每个部分能够有多个职工，可是每个职工只能从属一个部分
多对多联络(M∶N)：例如，学生与课程间的联络(“学”)是多对多的，即一个学生能够学多门课程，而每门课程能够有多个学生来学。联络也或许有特点。例如，学生“学”某门课程所取得的成果，既不是学生的特点也不是课程的特点。因为“成果”既依靠于某名特定的学生又依靠于某门特定的课程，所以它是学生与课程之间的联络“学”的特点。
联络的构图是一个菱形，联络的称号一般为动词。
3.特点（Attributes）：特点为实体供给具体的描绘信息。一个特定实体的某个特点被称为特点值。Employee实体的特点或许有：emp-id,emp-name,emp-address,phone-no……。特点一般以椭圆形表明，并与描绘的实体连接。
特点可被分为两类：
a.标识符（identifiers）：Identifiers能够仅有标识实体的一个实例（key），能够由多个特点组成。ER图中经过在特点名下加上下划线来标识。
b.描绘符（descriptors）：多值特点（multivaluedattributes）用两条线与实体连接，eg：hobbies特点（一个人或许有多个hobby，如reading，movies…）。复合特点（Complexattributes）本身还有其它特点。