数据库设计规范

数据库设计规范

意味深长与历久弥新,蕴含于简约之中,与清晰之中,与高效之中.
真正的坚决远不只是删繁就简,而是纷繁里简历秩序.
—– Jonathan Ive

why(为什么要做数据库设计规范)

1 为了软件的扩展性
2 提高开发效率(好的数据库设计可以减少开发中修改数据库)
3 提高磁盘利用率
4 提高查询效率

how(怎么去规范)

1 数据库选择

目前市场上包含两种数据库

1.1 SQL数据库(也就是关系型数据库)MySQL,Oracle,SQL Server等
    特点以及优点:
    更容易理解:二维结构时非常贴近逻辑世界的一个概念,关系模型相对于网状,层次等其他模型来说更容易理解
    使用方便:通用的SQL语言是的操作关系型数据库非常方便
    易于维护:丰富的完整性(实体完整性,参照完整性和用户定义的完整性)大大
减低了数据冗余和数据不一致的概率.
    瓶颈及缺点:
    进行高并发读写
    海量数据的高效率读写
    高扩展性和可用性:在基于web的结构当中,数据库是最难进行
横向扩展的,当一个应用系统的用户量和访问量与日俱增的时候,
数据库却没有办法想web Server和app server那样简单的
通过添加更多的硬件和服务节点来扩展性能和负载能力.
对于很多需要提供24小时不间断服务的网站来说,多数据库系统
进行升级和扩展是非常痛苦的事情,往往需要停机维护和数据迁移.
对于网站来讲,有些数据不再需要关系型数据库的跟多特点:
事务一致性
    关系型数据库在对事物一致性的维护中有很大的开下
,而现在很多web2.0系统对事物的读写一致性都不高
读写实时性
    对关系数据库来说,插入一条数据之后立即查,是
肯定可以读出这条数据的,大师对于很多web应用来说,并不要
这么高的实时性,比如发一条消息之后,过几秒乃至几十秒之后
才看到这条动态是完全可以接受的
    复杂SQL,特别是多表关联查询
        1.2 NoSQL数据库(非关系型数据库)
redis,mangoDB等
        它的优点也就是上述讲到的关系型数据库的缺陷

    2 效率,存储空间的考虑

        2.1 针对开发效率

        1. 数据库涉及字符规范
我们约定：采用26个英文字母（区分大小写）和0－9这十个自然数，加上下划线_组成，共63个字符。不能出现其他字符（注释除外）。
        2. 数据库对象命名规范
        我们约定，数据库对象包括表、视图（查询）、存储过程（参数查询）、函数、约束。对象名字由前缀和实际名字组成，长度不超过30。
        前缀：使用小写字母
        表 tb
        视图 vi
        存储过程 sp
        函数 fn
        实际名字：实际名字尽量描述实体的内容，由单词或单词组合，每个单词的首字母大写，其他字母小写，不以数字和_开头。如
        表 User_In=
        视图 User_List
        存储过程 User_Delete
    因此，合法的对象名字类似如下。
            表 tbUser_Info tbMessage_Detail
            视图 vi_Message_List
            存储过程 sp

3.数据库表命名规范

我们约定，表名由前缀和实际名字组成。
前缀：使用小写字母tb，代表表。实际名字中，一个系统尽量采取同一单词，多个后面加_来连接区分。

因此，合法的表名类似如下。

TbMember

tbMember_Info

tbForum_Board

tbBlog_Comment1

表

表名如Order/UserAccout

符合以下规范：

(1)统一采用单数形式，反对Orders

(2)首字母大写，多个单词的话，单词首字母大写，反对order/Useraccout/ORDER

(3)避免中文拼音，反对AgentBaoCi

(4)避免下划线连接，反对User_Accout（下划线适用Oracle数据库）

(5)避免名称过长，反对WebsiteInfomationModifyRecord

(6)多对多关系表，以Mapping结尾，如UserRoleMapping

(7)避免保留字

4.字段命名规范

我们约定，字段由表的简称,实际名字组组成。如果此字段关联另外的字段，那么加下划线_连接关联表字段的字段名。

因此，合法的字段名类似如下。

UserID_MeID
UserName
UserRegDate

字段

字段名如userID/userName/userType

符合以下规范：

(1)首个字母小写，多个单词的话，单词首字母大写，反对UserID/Userid

(2)必须有一主键，主键不直接用ID，而是表名+ID，如userID/orderID

(3)常用的字段name，不直接用name，而是表名+Name，如userName/orderName

(4)常用的字段desc，不直接用desc，而是表名+Desc，如userDesc/orderDesc

(5)大写字母前必须包含至少两个小写的字母，反对uID/oID

(6)避免中文拼音

(7)避免下划线连接

(8)避免名称过长

(9)避免保留字

对象

(1)存储过程以SP_为前缀

(2)触发器以TR_为前缀

(3)函数以FN_为前缀

(4)主键以PK_为前缀

(5)索引以IX_为前缀

(6)前缀后的首字母大写，多个单词的话，单词首字母大写，如SP_CountFee

(7)所有的关键字的所有字母必须大写，如SELECT userID,username FROM User

5.视图命名规范

我们约定，字段由前缀和实际名字组成，中间用下划线连接。

前缀：使用小写字母vi，表示视图。

因此，合法的视图名类似如下。

vi_User
vi_UserInfo

6.存储过程命名规范

我们约定，字段由前缀和实际名字加操作名字组成，中间用下划线连接。

前缀：使用小写字母sp，表示存储过程。

操作名字：Insert|Delelte|Update|Caculate|Confirm

例如：

sp_User_Insert

7.数据库设计文档规范

所有数据库设计要写成文档，文档以模块化形式表达。大致格式如下：

‘——————————————-

’ 表名： tbUser_Info

’ 作者： Yezi(叶子)

’ 日期： 2004-12-17

’ 版本： 1.0
’ 描述： 保存用户资料

’ 具体内容：

’ UserId int，自动增量 用户代码
’ UserName char(12) 用户名字

8.sql语句规范

我们约定，所有sql关键词全部大写，比如SELECT,UPDATE,FROM,ORDER,BY等。

针对存储空间

1 表设计原则

(1)规范化的优点是减少了数据冗余，节约了存储空间，相应逻辑和物理的I/O次数减少，同时加快了增、删、改的速度。但是一个完全规范化的设计并不总能生成最优的性能，因为对数据库查询通常需要更多的连接操作，从而影响到查询的速度，而且范式越高性能就会越差。出于性能和方便管理的考虑，原则上表设计应满足第三范式。有时为了提高某些查询或应用的性能而可以破坏规范规则，即反规范化。
(2)对各类数据表应该有不同的处理方式
        基本数据表：描述业务实体的基本信息。例如，人员基本信息、单位基本信息等。应对:此类表插入后一般不会修改 b
        标准编码表：描述属性的列表值。例如，职称、民族、状态等。应对:此类表中的字段最好用数字表示(做好注释)
         业务数据表：记录业务发生的过程和结果。例如，人员调动登记、变更通知单等。应对:此类表可能会有频繁的插入,修改
         系统信息表：存放与系统操作、业务控制有关的参数。例如，用户信息、权限、用户配置信息等。 应对:此类表通常之间有很强的关系,最好用关系表关联两者之前要关联的字段,如果需要频繁查询可以在关系表中添加索引.

---

        临时处理表：存放业务处理过程中的中间结果。
         其他类型表：存放应用层的日志、消息记录等此类数据可以采用非关系数据库进行存储
         注意:频繁访问的数据可以考虑noSQL数据库,对于数据量大的数据可以选择分库,分表

2 数据类型选择

（1）使用能正确存储和表示数据的最小类型(列如日期的存储可以使用timestamp,MySQL对它的存储进行了优化)
（2）选择更简单的数据类型.例如，比较整数的代价小于比较字符，因为字符集和排序规则使字符比较更复杂。
（3）尽可能把字段定义为 NOT NULL。对于字段能否NULL，应该在SQL建表脚本中明确指明，不应使用缺省。
（4）一个表中的字段不要太多，理论上不要超过80个。
（5）数据库中所有布尔型中数值0表示为假；数值1表示为真
（6）当字段定义为字符串类型时,对于非常短的列或者所有值都接近同一长度,char(固定长度)比varchar(可变长度)的有更高的存储效率,而且char因为固定长度不容易产生碎片(如固定长度密码的存储)
当然对于比较长的字符串使用可变长度的varchar更节省空间
使用varchar合适的情况:字符串列的最大长度比平均长度大的多;
列的更新更少,所以碎片不是问题;
使用了想UITF-8这样复杂的字符集,每个字符都是用了不同的字节数据进行存储
（7）字段尽可能有默认值，字符型的默认值为一个空字符值串，数字型的默认值为数值0。

3 键设计原则:

(1) 为关联字段创建外键
(2) 所有的键(主键,外键等)都必须唯一
(3) 尽量避免使用复合键
(4) 尽可能使用系统生成(如序列SEQUENCE产生)的主键

4 索引设计原则:

(1)如果一列出现表达式或函数,不会使用该列上的索引
(2)要索引外键
(3)对于索引选择性高的列使用B-Tree索引
(4)不要索引大型字段(有很多字符的字段)

外键约束

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

（2）根据需要适当设置父表数据修改时对子表的影响：

    父表中删除数据：级联删除；受限删除；置空值。

    父表中插入数据：受限插入；递归插入。

    父表中更新数据：级联更新；受限更新；置空值。

SQL语句编写

1．字符类型数据

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

2．复杂SQL

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

3．避免IN子句

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

4．避免使用SELECT * 语句

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

5．避免不必要的排序

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

6．INSERT语句

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

7．多表连接

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

8．参数的传递

SQL语句的编写，变量尽量使用“?”绑定变量。
尽量只存储单一实体类型的数据

其他设计技巧

1．避免使用触发器

触发器的功能通常可以用其他方式实现。在调试程序时触发器可能成为干扰。假如确实需要采用触发器，最好集中对它文档化。

2．保存常用信息

让一个表专门存放一般数据库信息非常有用。在这个表里存放数据库当前版本、最近检查/修复、关联设计文档的名称、客户等信息。这样可以实现一种简单机制跟踪数据库。

3．编制文档

对所有的命名规范、限制、数据字典、存储过程、函数都要编制文档。采用给表、列、触发器等加注释的数据库工具。对开发、支持和跟踪修改非常有用。对数据库文档化也会大大减少犯错的机会。