数据库设计漫谈

 引言

  数据库设计规范,仁者见仁,但是有共同的目标都是想要更加简洁清新,可维护可扩展等等。有时候设计的时候没有想到,等到开发的时候,或者发布完了,客户帮我们发现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:这里我没有提到触发器,实践证明触发器会使数据库迅速变得过于复杂,更重要的是触发器难以调试,如果不小心建了个连环触发器,就更让人头疼了,所以我更倾向于根本就不使用触发器。"

  

上一篇:趣写算法系列之--匈牙利算法


下一篇:采样方法(一)