18115228185
News
新闻资讯

mysql 数据库设计规范

日期:2020-06-01 00:00:00 访问:620 作者:巨石网络
   

一、数据库设计的原则

1.      表设计原则

(1)规范化与反规范化

规范化的优点是减少了数据冗余,节约了存储空间,相应逻辑和物理的I/O次数减少,同时加快了增、删、改的速度。但是一个完全规范化的设计并不总能生成最优的性能,因为对数据库查询通常需要更多的连接操作,从而影响到查询的速度,而且范式越高性能就会越差。出于性能和方便管理的考虑,原则上表设计应满足第三范式。有时为了提高某些查询或应用的性能而可以破坏规范规则,即反规范化。

数据应当按两种类别进行组织:频繁访问的数据和频繁修改的数据。对于频繁访问但是不频繁修改的数据,内部设计应当物理不规范化。对于频繁修改但并不频繁访问的数据,内部设计应当物理规范化。比较复杂的方法是将规范化的表作为逻辑数据库设计的基础,然后再根据整个应用系统的需要,物理地非规范化数据。

 

(2)数据表分类说明

    根据应用的实际需要和特点,可以将数据表进行如下分类:

l        基本数据表:描述业务实体的基本信息。例如,人员基本信息、单位基本信息等。

l        标准编码表:描述属性的列表值。例如,职称、民族、状态等。

l        业务数据表:记录业务发生的过程和结果。例如,人员调动登记、变更通知单等。

l        系统信息表:存放与系统操作、业务控制有关的参数。例如,用户信息、权限、用户配置信息等。

l        统计数据表:存放业务数据统计值。例如,通知单统计、人员类别统计等。

l        临时处理表:存放业务处理过程中的中间结果。

l        其他类型表:存放应用层的日志、消息记录等。

 

2.      字段设计原则

(1)一般来说,应该使用能正确存储和表示数据的最小类型。如果不确定需要什么数据类型,则选择不会超出范围的最小类型。

(2)选择更简单的数据类型。例如,比较整数的代价小于比较字符,因为字符集和排序规则使字符比较更复杂。

(3)尽可能把字段定义为 NOT NULL。对于字段能否NULL,应该在SQL建表脚本中明确指明,不应使用缺省。

(4)一个表中的字段不要太多,理论上不要超过80个。

(5)数据库中所有布尔型中数值0表示为假;数值1表示为真

(6)当字段定义为字符串类型时使用VARCHAR2而不用NVARCHAR

(7)字段尽可能有默认值,字符型的默认值为一个空字符值串,数字型的默认值为数值0。

 

3.      键设计原则

(1)为关联字段创建外键。

(2)所有的键都必须唯一。

(3)尽可能避免使用复合键。

(4)外键总是关联唯一的键字段。

(5)尽可能使用系统生成(如序列SEQUENCE产生)的主键。

(6)可选键有时可做主键。

(7)一个表中组合主键的字段个数尽可能少。

 

4.      索引设计原则

(1)如果一列出现在表达式或函数中,不会使用该列上的索引

(2)要索引外键

(3)对于索引选择性高的列使用B-Tree索引

(4)对于索引选择性低的列使用位图索引

(5)HASH索引只适用于相等比较

(6)不要索引大型字段(有很多字符的字段)

(7)不要索引常用的小型表

 

5.      LOB设计原则

如无特别需要,避免使用大字段(BLOB、CLOB、LONG等)。如使用时必须使用BLOB或CLOB类型。

 

二、完整性设计

采用数据库系统实现数据的完整性。这不但包括通过标准化实现的完整性而且还包括数据的功能性。在写数据的时候还可以增加触发器来保证数据的正确性。不要依赖于应用程序保证数据完整性,它不能保证表之间(外键)的完整性。

 

1.主键约束

每个表要求有主健,主健字段或组合字段必须满足非空属性和唯一性要求。

 

2.外键约束

(1)对于关联两个表的字段,一般应该分别建立主键、外键。实际是否建立外键,根据对数据完整性的要求决定。

(2)根据需要适当设置父表数据修改时对子表的影响:

l        父表中删除数据:级联删除;受限删除;置空值。

l        父表中插入数据:受限插入;递归插入。

l        父表中更新数据:级联更新;受限更新;置空值。

 

3.NULL值

由于NULL值在参加任何运算时,结果均为NULL,所以必须利用NVL()函数把可能为NULL值得字段或变量转换为非NULL的默认值。

 

4.CHECK条件

对于字段有检查性约束,要求指定CHECK规则。

 

5.触发器

触发器是一种特殊的存储过程,通过对表的DML操作而触发执行,是为确保数据的完整性和一致性不被破坏而创建,实现数据的完整约束。选择触发器的BEFORE或AFTER事务属性的时候,对表操作的事务属性必须与应用程序事务属性保持一致,以避免死锁发生。在大量修改数据时,尽量避免使用触发器。

 

6.视图

为了在数据库和应用程序之间提供另一层抽象,可以为应用程序建立专门的视图而不必非要应用程序直接访问表。这样做还在处理数据库变更时提供了更多的自由。视图是虚拟的数据库表,在使用时要遵循以下原则:

l        为简化查询,将复杂的检索或子查询通过视图实现。

l        提高数据的安全性,只将需要查看的数据信息显示给权限有限的人员。

l        视图中如果嵌套使用视图,级数不要超过3级。

l        由于视图中只能固定条件或没有条件,所以对于数据量较大或随时间的推移逐渐增多的表,不宜使用视图,可以采用实体化视图代替。

l        除特殊需要,避免类似SELECT * FROM [TableName] 而没有检索条件的视图。

l        视图中尽量避免出现数据排序的SQL语句。

 

三、命名规范

1.总则

(1)所有命名采用26个英文大小写字母和0-9这十个自然数,加上下划线_组成。不能出现其他字符(注释除外)。

(2)长度不超过30个字符。

(3)实际名字尽量描述实体的内容,由英文单词、单词组合或单词缩写组成,不以数字和_开头。

(4)命名中禁止使用SQL关键字。

(5)对象名尽量短。

 

2.表

表以单数形式名词或名词短语命名。如果表名仅有一个单词,那么建议不使用缩写,而是用完整的单词。

数据表     t_inf_<系统标识>_<表标识>

编码表     t_cod_<系统标识>_<表标识>

系统表    t_sys_<系统标识>_<表标识>

统计表     t_sta_<系统标识>_<表标识>

临时表     t_tmp_<系统标识>_<表标识>

日志表     t_log_<系统标识>_<表标识>

 

3.字段

l        采用有意义的字段名,应该是易于理解,能表达字段功能的英文单词或单词缩写,一般不超过三个英文单词。

l        系统中所有属于内码的字段(仅用于表示唯一性和程序内部用到的标识性字段),名称取为:ID。

l        系统中属于是业务范围内的编号的字段,其代表一定的业务信息,这样的字段建议命名为CODE,其数据类型为VARCHAR,该字段需加唯一索引。

l        字段名不要与表名重复

l        不要在列的名称中包含数据类型。

 

4.主键

PK_<表名>

 

5.外键

FK_<表名>_<主表名>_<外键字段名>

 

6.索引

IDX_<表名>_<构成索引的字段名>

如果复合索引的构成字段较多,则只包含第一个字段,并添加序号。

 

7.视图

V_<系统标识>_<视图标识>

 

8.存储过程

SP_<系统标识>_<存储过程标识>

 

9.函数

F_<系统标识>_<函数标识>

 

10.            触发器

TR_<表名>_

 

11.            用户定义数据类型

UD_<自定义数据类型标识>

 

12.            序列

SEQ_<序列标识>

 

13.            局部变量

L_<变量标识>

 

14.            全局变量

G_<变量标识>

 

15.            游标变量

L_CUR_<变量标识>或G_CUR_<变量标识>

 

16.            存储过程或函数定义中的参数

IN型参数:P_<参数标识>

OUT型参数:R_<参数标识>

函数返回值:R_<变量标识>

 

四、安全性设计

1.管理默认用户

在生产环境中,必须严格管理SYS和SYSTEM用户,必须修改其默认密码,禁止用该用户建立应用数据库对象。删除或锁定SCOTT等默认安装但不使用的用户。

 

2.数据库级用户权限设计

必须按照应用需求,设计不同的用户访问权限。包括应用系统管理用户,普通用户等,按照业务需求建立不同的应用角色。用户访问另外的用户对象时,应该通过创建同义词对象SYNONYM进行访问。

 

3.角色与权限

确定每个角色对数据库表的操作权限,如创建、检索、更新、删除等。每个角色拥有刚好能够完成任务的权限,不多也不少。在应用时再为用户分配角色,则每个用户的权限等于他所兼角色的权限之和。

 

4.应用级用户设计

应用级的用户帐号密码不能与数据库相同,防止用户直接操作数据库。用户只能用帐号登录到应用软件,通过应用软件访问数据库,而没有其它途径操作数据库。

 

5.用户密码管理

用户帐号的密码必须进行加密处理,确保在任何地方查询都不会出现密码的明文。

 

五、SQL语句编写

1.字符类型数据

SQL中的字符类型数据应该统一使用单引号。特别对纯数字的字符串,必须用单引号,否则会导致内部转换而引起性能问题或索引失效问题。利用TRIM(),LOWER()等函数格式化匹配条件。

 

2.复杂SQL

对于非常复杂的SQL(特别是有多层嵌套,带子句或相关子查询的),应该先考虑是否设计不当引起的。对于一些复杂SQL可以考虑使用程序实现。

 

3.避免IN子句

使用 IN 或 NOT IN 子句时,特别是当子句中有多个值且表数据较多时,速度会明显下降。可以采用连接查询或外连接查询来提高性能。

 

4.避免使用SELECT * 语句

如果不必要取出所有数据,不要用 * 来代替,应给出字段列表。

 

5.避免不必要的排序

不必要的数据排序大大的降低系统性能。

 

6.INSERT语句

使用INSERT语句一定要给出插入值的字段列表,这样即使表加了字段也不会影响现有系统的运行。

 

7.多表连接

做多表操作时,应该给每个表取一个别名,每个表字段都应该标明其所属哪个表。

 

8.参数的传递

SQL语句的编写,变量尽量使用“?”绑定变量。

 

9.存储过程、函数中的注释,示例如下:

/*

目的:

作者:

创建日期:

*/

 

/*

修改顺序号:

修改者:

修改日期:

修改原因:(具体原因详细描述)

*/

 

六、建模管理方法

1.建模工具

统一使用PowerDesigner软件建模。推荐版本PowerDesigner 15中文版。

 

2.建模步骤

(1)逻辑建模

根据数据库设计和命名规范先在PowerDesigner中建立逻辑模型(LDM)文件。要求每个表和字段都要有注释说明;Check Model不能出现错误。

(2)根据逻辑模型文件创建对应数据库的物理模型文件。

(3)生成数据库结构及其相应的SQL脚本。

 

3.模型维护

(1)所有关于数据库的表、字段及关系、说明等均以物理模型文件为准。

(2)由开发人员将变更需求提交项目负责人审批。

(3)项目负责人同意变更后由相应开发人员负责编写变更脚本提交DBA。

(4)DBA更新数据库及其相关文档,并维护所有部分的一致性。