数据库设计漫谈
数据库设计规范，仁者见仁，但是有共同的目标都是想要更加简洁清新，可维护可扩展等等。有时候设计的时候没有想到，等到开发的时候，或者发布完了，客户帮我们发现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、他山之石
博客园的Jimmy Zhang对数据库命名和设计说的很恳切，收益颇丰，特此引用，以资鼓励。
<STRONG><SPAN style="FONT-SIZE: 18pt">"</SPAN></STRONG>我看过很多的开发人员设计出来的数据库，给我的感觉就是：在他们眼里，数据库的作用就如同它的名称一样――仅仅是用来存放数据的，除了不得不建的主键以外，什么都没有...没有 Check约束，没有索引，没有外键约束，没有视图，甚至没有存储过程。 在这里，我提出如下数据库设计的建议： 如果要写代码来确保表中的行都是唯一的，就为表添加一个主键。 如果要写代码来确保表中的一个单独的列是唯一的，就为表添加一个约束。 如果要写代码确定表中的列的取值只能属于某个范围，就添加一个Check约束。 如果要写代码来连接 父－子 表，就创建一个关系。 如果要写代码来维护“一旦父表中的一行发生变化，连带变更子表中的相关行”，就启用级联删除和更新。 如果要调用大量的Join来进行一个查询，就创建一个视图。 如果要逐条的写数据库操作的语句来完成一个业务规则，就使用存储过程。 NOTE：这里我没有提到触发器，实践证明触发器会使数据库迅速变得过于复杂，更重要的是触发器难以调试，如果不小心建了个连环触发器，就更让人头疼了，所以我更倾向于根本就不使用触发器。< |
