数据库设计经验

一、设计数据库之前

定义标准的命名规范。如：别名采用表名的前4个字母、键值列采用_NO或_CODE、_ID后缀修饰、视图采用view_前缀、存储过程采用sp_前缀等等。

理解客户需求，百分百地了解业务，ER设计前要有详细的产品功能设计。

二、数据库设计过程

数据库设计中需求分析阶段综合各个用户的应用需求（现实世界的需求），在概念设计阶段形成独立于机器特点、独立于各个DBMS产品的概念模式（信息世界模型），用E-R图来描述。在逻辑设计阶段将E-R图转换成具体的数据库产品支持的数据模型如关系模型，形成数据库逻辑模式。然后根据用户处理的要求，安全性的考虑，在基本表的基础上再建立必要的视图（VIEW）形成数据的外模式。在物理设计阶段根据DBMS特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式。

通过需求分析阶段得到数据流图和数据字典。

分析和表达用户需求的方法主要包括自顶向下和自底向上两类方法。自顶向下的结构化分析方法（Structured Analysis，简称SA方法）从最上层的系统组织机构入手，采用逐层分解的方式分析系统，并把每一层用数据流图和数据字典描述。
数据流图表达了数据和处理过程的关系。系统中的数据则借助数据字典（Data Dictionary，简称DD）来描述。
数据字典是各类数据描述的集合，它是关于数据库中数据的描述，即元数据，而不是数据本身。数据字典通常包括数据项、数据结构、数据流、数据存储和处理过程五个部分

通过对用户需求进行综合、归纳与抽象，形成一个独立于具体DBMS的概念模型，可以用E-R图表示。
概念模型不依赖于某一个DBMS支持的数据模型。概念模型可以转换为计算机上某一DBMS支持的特定数据模型。

将E-R图转换为关系模型实际上就是要将实体、实体的属性和实体之间的联系转化为关系模式

为逻辑数据模型选取一个最适合应用环境的物理结构（包括存储结构和存取方法）。根据DBMS特点和处理的需要，进行物理存储安排，设计索引，形成数据库内模式。
5.
运用DBMS提供的数据语言（例如SQL），根据逻辑设计和物理设计的结果建立数据库，组织数据入库，并进行试运行。 数据库实施主要包括以下工作：用DDL定义数据库结构、组织数据入库 、编制与调试应用程序、数据库试运行
数据库应用系统经过试运行后即可投入正式运行。在数据库系统运行过程中必须不断地对其进行评价、调整、优化与修改。包括：数据库的转储和恢复、数据库的安全性、完整性控制、数据库性能的监督、分析和改进、数据库的重组织和重构造。数据库的优化可以三个层面：数据库的逻辑层优化、数据库的物理层优化、应用程序层SQL优化。

三、设计表和字段

2. 遵照3NF要求，但某些性能原因可以允许数据高级冗余，杜绝低级冗余。

4. 当多个表存在相同字段时，应该保证字段名称、字段类型和长度的一致性。

7. 数据库和程序的版本机制处理，对于不一致的版本支持脚本包的升级

11. 考虑表的性能，对于性能问题的表，可以降低范式，增加数据上的高级冗余，少用触发器，考虑存储过程提高效率。

四、选择键和索引

1. 使用系统内部生成的字段作为主键，不要把用户输入的字段做为主键，因为它随时可能被修改。

2. 键设计的4原则：为关联字段创建外键、所有的键都必须唯一、避免使用复合键、外键总是关联唯一的键字段。

6. 主键设计：a.编号作主键 b.自动增长列作主键 c.max加1作主键 d.自制加1作主键 e.guid主键，更加建议自制加1和guid作主键。

7. 实体与实体间的关系有：一对一，一对多，多对一，多对多，实体间的关系正是利用了主键和外键的配对。

五、保证数据的完整性

实体完整性：主键
参照完整性：
  父表中删除数据：级联删除；受限删除；置空值
  父表中插入数据：受限插入；递归插入
  父表中更新数据：级联更新；受限更新；置空值
对参照完整性可以有两种方法实现：外键实现机制（约束规则）和触发器实现

六、数据库表设计模式

主扩展模式，通常用来将几个相似的对象的共有属性抽取出来，形成一个“公共属性表”；其余属性则分别形成“专有属性表”，且“公共属性表”与“专有属性表”都是“一对一”的关系。

主从模式，是数据库设计模式中最常见、也是大家日常设计工作中用的最多的一种模式，它描述了两个表之间的主从关系，是典型的“一对多”关系。

名值模式，通常用来描述在系统设计阶段不能完全确定属性的对象，这些对象的属性在系统运行时会有很大的变更，或者是多个对象之间的属性存在很大的差异。

多对多模式，也是比较常见的一种数据库设计模式，它所描述的两个对象不分主次、地位对等、互为一对多的关系。

继承模式，可以看作是“主从模式”的一种特殊情况（或者说是“变形”），它所代表的两个对象也是“一对多”的关系。它与“主从模式”的区别是，“继承模式”中从表的主键是复合主键，并且复合主键中必定包含主表的主键列。

根据从表继承主表的列的数量，继承模式又分以下两种情况：

自联结模式，也可以看作是“主从模式”的一种特殊情况（或者说是“变形”），它在一张表内实现了“一对多关系”，并且可以根据业务需要实现“有限层”或者“无限层”的主从嵌套。

简单自联结，就是在一个表里设置当前类ID、父类ID，同时规定最顶层类的父类ID为一个固定值（比如0），在生成树的时候使用递归算法，记录的前后顺序通过“排序号”字段来确定。

扩展自联结，与简单自联结的最大区别就是通过附加冗余字段来避免递归运算，所要实现的主要目标就是一次读取就能生成整个树，一次提高树的生成效率。

单表模式，就是把相关子类的属性统统集中在一个表里，通过“类别”字段来区分表内记录所属的子类以及该类的有效属性。

七、总结数据库设计

1. 一个数据库中表的个数越少越好、一个表中组合主键的字段个数越少越好、一个表中的字段个数越少越好

2. 基本表尽量满足第三范式，在数据冗余和处理速度之间找到合适的平衡点。

3. 键和索引的正确指定，往往它是性能的关键。一个实体不能既无主键又无外键。在E－R 图中, 处于叶子部位的实体, 可以定义主键，也可以不定义主键(因为它无子孙), 但必须要有外键(因为它有父亲)。

4. 抓住基本表，演绎中间表。基础表中的字段是不可再分解的，基本表中的记录是原始的记录，基本表的结构是相对稳定的，基本表中不应该包括可以由其他字段推导出来的字段，除非考虑到性能原因。

5. 利用视图来分隔程序与基本表的逻辑，这样表的变动，只要视图不变动，程序无须变动。