数据库表设计细节

数据库建表原则

数据库表及字段命名、设计规范

SQL优化-索引

一  有关表字段设置(以oracle为例)

对于任何字段长度都不应该过于小气,否则未知的变化会造成前后台都要修改  
                     
1、对于开关型字段建议number(1,0) 而不是varchar2(1),避免用户错误保存Y/N,而不是1/0,这样可能会引入大小写问题 （同意）
                                  
2、对于数据字典编码字段,不要小气的确定为3位,最好统一为32位
经验证明,有时受从其它系统数据接入的影响,由于没有对照项,会直接保存原始值,而原始值一般都5-10位
统一为32位的好处是,可以考虑利用GUID来生成数据字典编码,这样在数据合并时非常有优势. （很同意）
                          
3、对于一般性录入字段,如:编号,轴号,车号,不要为了一时的"绝对"而设置确定的长度,最好统一成较优的长度,如32位!  
      如:车号最早是6位,没多久就改成了7位! 轴号开始为8位,但实际上有15位的轴号!轴承编号由10改为了20位  
                                    
4、对于类似名称的字段: 如单位名称, 数据字典项目的名称等,最好再大一些,设成60位!  
              
5、对于备注类型的字段,一般内容在30个汉字左右,所以推荐设置为100  
              
6、对于长文本的字段,一般内容在200个汉字左右,推荐设置为1000  
                    
7、对保存SQL语句的字段(特殊情况,如配置传输条件等),至少要设置为2000,最大是4000  
                                      
8、对于数字字段,除非精度要求,统一为number是个较好的选择 （如果需要，请尽量提前考虑好精度可能的变化）
number默认精度为15位(整数位数+小数位数=15位,小数点位置任意),其它大数值也可以保存,但是采用的是科学计数法,有精度损失  
      用number,不指定精度的最大的好处是不限制数值的精度和范围  
       如果指定number(2,1),则存入的数值范围在-9.9 至 9.9之间,如果用户提出精度调整为2位,则需要修改数据库和程序!  
                            
9、对于日期型的就没有什么说法了 

二  选择键和索引的原则

1 键选择原则：

1) 键设计4 原则
为关联字段创建外键。 　　
所有的键都必须唯一。 　　
避免使用复合键。 　　
外键总是关联唯一的键字段。
　　
2) 使用系统生成的主键

设计数据库的时候采用系统生成的键作为主键，那么实际控制了数据库的索引完整性。这样，数据库和非人工机制就有效地控制了对存储数据中每一行的访问。
采用系统生成键作为主键还有一个优点：当拥有一致的键结构时， 找到逻辑缺陷很容易。

3) 不要用用户的键(不让主键具有可更新性)
在确定采用什么字段作为表的键的时候，可一定要小心用户将要编辑的字段。通常的情况下不要选择用户可编辑的字段作为键。

4) 可选键有时可做主键
把可选键进一步用做主键，可以拥有建立强大索引的能力。

2 索引使用原则：

　　索引是从数据库中获取数据的最高效方式之一。95%的数据库性能问题都可以采用索引技术得到解决。
　　1) 逻辑主键使用唯一的成组索引，对系统键（作为存储过程）采用唯一的非成组索引，对任何外键列采用非成组索引。考虑数据库的空间有多大，表如何进行访问，还有这些访问是否主要用作读写。

　　2) 大多数数据库都索引自动创建的主键字段，但是可别忘了索引外键，它们也是经常使用的键，比如运行查询显示主表和所有关联表的某条记录就用得上。

　　3) 不要索引memo/note 字段，不要索引大型字段（有很多字符），这样作会让索引占用太多的存储空间。

　　4) 不要索引常用的小型表
　　不要为小型数据表设置任何键，假如它们经常有插入和删除操作就更别这样作了。对这些插入和删除操作的索引维护可能比扫描表空间消耗更多的时间。

三 其它设计规范

(1)所有字段在设计时，除以下数据类型timestamp、image、datetime、smalldatetime、uniqueidentifier、 binary、sql_variant、binary 、varbinary外，必须有默认值。
字符型的默认值为一个空字符值串’’；数值型的默认值为数值0；逻辑型的默认值为数值0；其中：系统中所有逻辑型中数值0表示为“假”；数值1表示为“真”。datetime、smalldatetime类型的字段没有默认值，必须为NULL。

(2)当字段定义为字符串形时建议使用varchar而不用 nvarchar。

(3)表内不应该存储依赖于其他键的非键信息

四 数据库表设计中的经验

表是Oracle数据库中最基本的对象之一。万丈高楼从平地起，这个基础对象对于数据库来说，非常重要。因为其设计是否合理，直接跟数据库的性能相关。从Oracle数据库菜鸟到数据库专家这个过程中，在表设计与管理上，或多或少，会犯一些错误。笔者今天就谈谈自己在这方面的经验与教训，或许能够给大家一些警示作用。

经验一：在设计大型数据库时，把允许NULL的列放在表的后面。

在数据库表设计过程中，有些字段要求必须为非空，如表的关键字，单据编号字段等等。在数据库表创建的时候，往往需要把这些字段设置为非空。如此的话，就可以强制用户输入数据，以增强数据的一致性。

同时，在一张表中，也会有不少的字段，如员工信息表中，员工爱好、注释等字段，就可能允许为空。利用数据库专业术语来说，这些没有内容的字段就是“NULL”字段。

在数据库表设计的时候，对于这种类型的空字段要特别的留意。一方面，这个NULL字段，不是说其里面存储的是空格。若利用“’’”这个符号去查询空字段的记录的话，往往查不到我们想要的结果。因为在数据库存储的时候，若字段为NULL，则这个值根本没有存储。另一方面，正因为上面这种特性，所以，在表设计的时候，最好把允许NULL的字段放在表的末尾，当数据库比较大或者空字段比较多的话，则可以大大的减少数据库的存储空间。

另外，在数据库创建表的时候，是按照列建立的时间来排序的。所以，当某个列建立好之后，除非重新删除再建立，否则无法调整列的顺序。这就要求数据库管理员在创建表之前，就要对列、以及是否为空有一个明确的定义。只有如此，才能够合理安排列的顺序。

经验二：养成勤于写注释的习惯。

判断一个人是否是数据库专家，从一个“注释”的小习惯中就可以 看得出来。若一个人在数据库开发的时候，勤与写注释，如在建立表或者字段的时候，能够利用comment命令对表与字段进行注释，则可以从一定程度上判断他是这方面的行家。相反，若其开发的数据库，注释没有多少，则基本可以判断，他是一个刚入门的菜鸟。或者说，其没有参加过大型数据库的设计。

这主要是因为，在数据库开发过程中，往往不是一个人可以完成的。如在开发一个ERP数据库的时候，可能有些人专门负责开发基础表，而有些人负责维护视图，等等。大家都是分工合作。同时，在前台也有程序员需要调用后台的数据库表与字段。所以，中大型的数据库与应用系统往往是大家协作的结果。

为此，为了提高表与字段的可读性，一个数据库管理人员开发的表格，大家都可以看的懂，就需要给表与字段设置比较详尽的注释。在Oracle数据库中，为了给其他伙伴提供有意义的帮助信息，就可以利用Comment命令来描述表、字段的作用以及一些引用的注意事项。这个小小的命令，可以大大提高表与字段的可读性，提高数据库与程序开发团队的协作性。