数据库设计漫谈

　引言

　　数据库设计规范，仁者见仁，但是有共同的目标都是想要更加简洁清新，可维护可扩展等等。有时候设计的时候没有想到，等到开发的时候，或者发布完了，客户帮我们发现BUG，那是很得不偿失的事，这些得不偿失的事，我都经历着或经历过，记得刚毕业出来工作，对命名没有什么概念，很随意，更别谈用心去设计了，后面带来的痛只有自己清楚。所以对细节和规范，我觉得特别有感同身受，如果看到某个人的博客，能把数据库的设计多点分享，那是很感激的，自己的想法也有些，但是终究还是没有那么的系统，权作漫谈，或许能有感同身受，那也是一份贡献。

　1.关于主表和从表的命名

　　　　有时候在寻找bug的过程中，会关联主从表进行定位排错。主从表命名的好，查库很方便，很容易就可以定位到错误。这里举个项目中的实例。

　　　　这是我同事设计的表：(因为用到Oracle,所以采用大写命名)

　　　　　　表1：MES_BASIC_PRODUCT_OQC(主表-产品出库检基础资料表)

　　　　　　表2： MES_BAS_OQC_ITEMS(从表-出库检项目明细基础资料表)

　　　　　　表3：MES_OQC_CHECK (主表-出库检验数据采集表)

　　　　　　表4：MES_OQC_CHECK_ITEMS(从表-出库检验项目明细数据采集表) 

　　　　　　其中主表表1,表3 表有一个共同的字段：PRODUCTID

 　　　　　  先谈一谈自己的想法。

　　　　　　第一，如果从美观和一致的原则，MES_BASIC_PRODUCT_OQ这个名称应该命名为MES_BAS_PRODUCT_OQC，这样做可能并不起眼，但是养成一种好的习惯，其实更难能可贵。因为编码本身是一种细活，碰到因为细节导致的灾难太多了。所以要把错误扼杀在萌芽阶段，把一个简单的功能做到能力的极致，我觉得是一个优秀程序员的品德。至于表3和表4命名，遵循了继承的方式，清爽可读。

　　　　　　第二，接下来说说不遵循这种清爽可读带来的麻烦。比如有个这样的问题，我们查询表2(MES_BAS_OQC_ITEMS)和表4(MES_OQC_CHECK_ITEMS)各自的项目编号字段(ITEM_NO)看看是否一致，但是表3是'03-01'，表4是'04-01'，二者应该是一致的，这里出现了不同。于是我们要追溯各自的主表，寻找主表的产品编号PRODUCTID是否一致。于是我们从库里自然而然的开始select *from 主表。这里主表名称叫什么？有什么办法可以根据从表名称直接推断出主表的名称？显然表1和表2的住从表命名给我们带来了麻烦，因为我们无法从从表推断出主表的名称。于是我们要么查阅ER图，要么SQL里去从100多张表里面去定位主表，很麻烦。

　　　　　　汇总：主从表尽可能遵循继承关系，这里的继承指的是名称上的父子关系的直观显示。比如表1和表2，可以这样设计成MES_BAS_PRODUCT_OQC和MES_BAS_PRODUCT_OQC_DETAIL。表3和表4可以设计成MES_OQC_CHECK和MES_OQC_CHECK_DETAIL。这样在以后的定位和寻找就事半功倍，没有什么技术含量，确是很好的习惯。

　2.关于基础表的设计

　　　　我在项目发布后一段时间，客户给我汇总了bug文档，其中有一个问题是这样的，如果下面两张图所示：

　　　　上表-对应数据库的基础表：

　　

　　下表-对应数据库的主表：

　　

　　最后客户在旁边注释到：检验标准中的公差和检验记录信息的公差 值显示有出入。

　　我查了下数据显示不一致原因是是这样的，主表从基础表带过来数据后，客户把基础表的标准，上偏差，下偏差部分修改掉了。这样客户匹配的时候，就出现了同一个产品，检验标准，上下偏差主从表不一致的情况。

　　 如果基础表是个小基础资料表，可以用版本很容易的控制这种情况的发生，但是基础表是个大基础表，客户都是用Excel大批量的进行导入操作的。所以用版本来做，同一个产品有可能出现N个版本，这个表就变得非常庞大，不是很好办。想到的第二种做法是，标准和上偏差，下偏差只存在于基础表当中，主表使用这几个字段。这样主表做显示的时候，直接管理基础表关联数据。这样可以保证数据的一致性。但是问题又来了，客户可能对基础表进行后期的增删改操作，这样就造成，引用他主表的数据关联不到历史数据。想来想去，还是在主表当中保留标准，上偏差，下偏差三个字段，用于存储从基础表带过来的对应数据，这样保证了历史记录的完整性。这样，客户如果修改基础表，那么主表只能引用到最新修改的数据，至于基础表被改后，主表的记录对应的显示只能是历史记录，这样其实可以用来做追溯用。就这样设计吧。暂时想不到完美的办法。

　　

　3.以非空的思想设计字段

　　客户又反馈一个问题：进料检验记录有记录，进料检验日报确没有记录。如下所示

　　进料检验记录：

　　

　　进料检验日报：

　　

　　这两个信息都是来自同一张进料检验表，但是为什么确会出现不同的记录？

　　我们先看一下数据库进料检验表的设计：如下图 

　　

　　我们注意到Nulls这一列，我们来分析问题背后，经常被忽视的设计理念。我们观察发现这张表大部分字段都可空(NULL),当前所在系统，很多表的字段都是尽可能的可空，这张表只是一个代表。可空的好处是放得很宽，这样很符合企业复杂的业务需要，同时编码的时候也可以少些约束和验证。但是不经意的放宽的代价就是后面关联查询做报表显示的时候，会经常出现一些莫名其妙的错误。

　　上面问题的产生，根源在于部门编号(DEPTID)这个字段设计成可空。部门编号使用的目的是用来区分不同部门对数据的操作权限。比如图2中，进料检验日报上面的部门数据选择这个查询条件，可以筛选不同部门的数据信息。那么这个部门编号到底能否为空？按照设计为可空，那么数据采集后的结果可能是这样的：

　　

　　如此，那么上面的进料检验日报图，如何可查询到结果呢？那么，在部门数据选择这个查询条件加一个"全部"查询条件来查询DEPTID为NULL的数据何如？也许可以打补丁式的修复这个问题，但是总感觉特别怪，简单的东西变得复杂化了。那该怎么处理？我觉得还是要回到这个问题：这个部门编号到底能否为空？

从业务上来看，除了基础表可能涉及到可空，其他表其实都应该是不可空，如果可空，那么这条空记录如何追溯？基础表如果用NULL表示该记录属于所有部门，其实也不算很好的做法，存NULL记录还不如直接存储部门里面的根节点，比如项目所属公司的编号。这样整个结构看起来就更加清晰明了了。

　4、关于命名的分类

　　如果数据库的表多了，没有做一个分类，想要快速定位某个模块的某个表其实是很吃力的。为什么这么理解的，先看图示如下：

　　

　　　　

　　　　

　　　　

　　我们先观察一下这个系统的表命名方式。通用级别的表采用的是BAS前缀或SEC前缀，系统级别的用的是MES_BAS或MES_IQC前缀。如果不适用这些前缀会怎样呢？比如我们要定位某个模块的某张表，我们找寻起来就特别费劲。当然因为加上了分类的前缀，表明可能会变长，如果超过30个字符，Oracle就不支持。不过这不是问题，问题是我们不能没有分类，特别是一个大的系统。

　　至于字段的命名，要不要分类呢？这个和表明是否要保持一致？比如User表是用UserID好还是ID好。先看一个我同事设计的表

　　

　　注意到这里使用了USER前缀，其实是多余的，User表为什么还要加User前缀呢？其次，我们写SQL带来工作量的多余，select sec_user.username,sec_user.userId……。如果表格字段很多，那就真的冗余了。当然如果表不多，字段有限，那就另当别论了。但是如果考虑到可扩展，为什么不把简单的东西做到最好呢？

　　

　5、关于分类表的设计

　　客户，供应商，订单等等都有自己的分类，包括博客园里面的文章分类什么的，经常要设计这么一个分类表来单独存放分类信息，也许你会觉得很简单，直接分类ID,分类名称,备注什么的不就OK了。但是如果分类多了，分类下还可能出现子类，改如何处理？比如博客园的分类我感觉不怎么好用，比如我想在Asp.Net下新建一个MVC和WebForm子类；想在.NET类下建一个CLR,CFL，Ado.Net，UI四个子类是无法做到的。于是我想起以前设计分类表犯下的同样的简单化的错误，其实把简单做到极致是很不容易的。如果当初能多一个ParentID列，也许就不至于现在加个子类都麻烦，最后把所有的和BS相关的都放在Asp.net类目下。

　　

　　这里的父类编号，让分类变成了一个树，虽然可能用不着子类，但是让他冗余这，心理还是比较踏实。

　6、关于跨库的细节

　  1.关键字陷进：

　　我们所设计的ER模型图，有可能会移植到其他的数据库，而且我们也为多数据库支持使用了ORM或者抽象工厂，当我们觉得很完美的时候，如果一个不小心，又是大的体力劳动。比如你的SqlServer库的User表有UID字段，DEFECT表有LEVEL(等级)字段，你发现ORACLE是不支持的，于是很郁闷的一个一个去手动修改，坑爹呀，建议建好库后，跨库各执行以下SQL，就知道不兼容在那儿了。

　　2.存储过程和触发器的陷进：

　　这个优点就不说了，跨库的后果就是全部重写。所以大家经常说尽量不用存储过程，NoSql是有道理的。

　　3.自增长字段的陷进：

　　自增长字段保证了对象的唯一，但是使用后给跨库带来的麻烦也是一堆一堆的，比如ORACLE不支持自增长。还有万一客户要求合并库，于是数据的冲突带给你的痛苦，只有自己懂得。检验统一使用VARCHAR(36)长度的GUID字段。GUID字段值由程序生成。

　　4.表格命名不超过30个字符。

　　这是ORACLE的要求，死的，没有办法。

　　5.表名的规范以谁为主？

　　曾经一个项目，表都设计好了，自我感觉很规范，最后ER文档发给客户的时候，客户说这个规范我看了不习惯，必须按我们的规范来，说服了半天，对方说我们要源码还要进行二次开发，必须的按他们的来。谁让客户是上帝，还是一个大客户，没有办法，于是带着满腹的脏话100多张表全部一一改掉。可见如果碰到需要二次开发的，懂得内行的客户，还是多留意的好。

　　

　7、他山之石

　　博客园的Jimmy Zhang对数据库命名和设计说的很恳切，收益颇丰，特此引用，以资鼓励。 

"我看过很多的开发人员设计出来的数据库，给我的感觉就是：在他们眼里，数据库的作用就如同它的名称一样――仅仅是用来存放数据的，除了不得不建的主键以外，什么都没有...没有 Check约束，没有索引，没有外键约束，没有视图，甚至没有存储过程。

在这里，我提出如下数据库设计的建议：

如果要写代码来确保表中的行都是唯一的，就为表添加一个主键。
如果要写代码来确保表中的一个单独的列是唯一的，就为表添加一个约束。
如果要写代码确定表中的列的取值只能属于某个范围，就添加一个Check约束。
如果要写代码来连接 父－子 表，就创建一个关系。
如果要写代码来维护“一旦父表中的一行发生变化，连带变更子表中的相关行”，就启用级联删除和更新。
如果要调用大量的Join来进行一个查询，就创建一个视图。
如果要逐条的写数据库操作的语句来完成一个业务规则，就使用存储过程。
NOTE：这里我没有提到触发器，实践证明触发器会使数据库迅速变得过于复杂，更重要的是触发器难以调试，如果不小心建了个连环触发器，就更让人头疼了，所以我更倾向于根本就不使用触发器。"