大型软件系统中表结构设计的一点体会和经验

       本人曾经在一家国内算是有一定知明度的ERP公司供职多年，一套ERP系统在国内至少上万家客户使用。但由于系统在设计过程中，表结构设计的最初严重失吴，导致ERP系统的数据逻辑总是偶偶出现问题。现象有，业务单据产生账款单据出现重复，如果一个采购入库单，会出现两个对应的收款记账单，有时候还出现A入库单对应B入库单记账单，B入库单对应了A的入库单。这种几率虽然非常低（有的客户一年出现一次， 有的一个月一次），但这非常要命,非常令人抓狂啊。

       当我们内部做测试时候不管多少人一起做单（模拟并发情况），都不能重现。如果从代码面分析也更加难看出问题。不能重现的问题，对我们软件编程人员来说，就很难抓取这些BUG了。这个软件从99年到现在，这样的问题还是存在，不管多牛的人去分析研究，最好都不了了之！

       离开这家公司后，本人和朋友一起创业，成立现在的软件公司，我在最初设计表结构的时候，我就想，我们不能再迈入前人的覆辙吧。以前软件究竟是问题出在哪里呢？我特做一下分析，跟大家分享。

      旧有的系统，表结构设计的时候，Key字段用了VarChar型数据，具体体现在如客户资料的主Key为CustNo （客户代号），   各种单据的单号BilNo也为Key字段。大家知道，单号在软件使用过程中在并发做单的情况下（如好几个人录入单据），会出现抢单号现象。如果3个人一做单，新增后，大家都会生成一个单号，这时候大家都叫001，当保存到后台后，分别变为001、002、003。那就意味着Key字段的值是发生变化的，而Key字段是关键字段，也是跟其它业务关联的字段，由于这个单号变了，导致很多相关的业务数据使用这个Key值的表（外键值），需要重新同步回写，为了处理这个问题，系统大量的代码来处理这个问题（代码越多，产生BUG的可能性就越大啊）！另外是旧系统有一个问题控件本身缓存机制导致数据回写错误，由于不是本文讨论的访问，这里不谈了。

     上面分析也就是系统使用了客户能修改的字段值来做Key（如果客户代号、单号），也就是说客户可以编辑Key字段，系统怎么能这么设计呢？Key字段应该是内部字段，对最终使用软件的客户来说，应该是看不见，也摸不到才是，应该是隐藏在系统数据库表结构中。那怎么处理这个问题呢？

     我的方法是用大整型（BigInt）做内部ID，ID在新增的时候（保存前）就得到一个系统唯一值（这个很容易实现吧），单据的单号可以设置为一个备注字段（可以设置唯一索引，保证唯一性），这样在并发的的时候，只是单号发生了改变，单据Key字段回写到相关其它表的外键值不用代码取维护，也就不存在产生回写错误机会了。

     我们很多系统都觉得用单号来做单据的主Key,客户代号做客户的主Key，因为他们对系统中都具有唯一性，但忘记了这些字段是用户看得懂的字段且是需要维护修改的字段，你做成了Key，那很多表使用了这个字段做外键，那怎么维护？再说我们现在以为他们是唯一的，未来还不一定呢。曾经有一个系统，建立个人身份信息，设计的人开始用身份证号作为Key，觉得他们是唯一的吧，也永远不会变吧，其实后面发现身份证号不够用（需要加位数）以前的身份证后面加9，这个时候Key的值变了吧！另外外国朋友进系统也可能出现Key值重复哦！这下这个系统麻烦了，所有相关的身份证号的字段都要重新维护更新（恐怖吧，这可是一个Key，很多外键引用啊！）。

    总结一下， 数据库结构设计的时候，Key字段要用内部字段（用户无法干预的）， 不要具有用户意义的字段，这样才能能从数据结构设计方面来保证数据的一致性，从而保证信息系统数据一致性。

   

    我们 3S系统所有的Key字段都是唯一Key字段，带来的好处可以说不完，以下列举部分：

    客户在上ERP系统的时候，期初对基础资料编码经常可能做调整，但系统也开始运行。比如：修改某个货品编号（注意这个货品已经用到，如采购订单中），直接修改就是，不用做任何维护工作，因为货品编号只是一个备注字段，不参与我们其它的表中,内部运算全部是Key字段（客户看不到，更加不能维护）。3S系统由于货品代号可采用智能自动编码，当用户在使用过程中可能调整编码规则，这样要求对所有的货品编号重新编码，只要通过货品编号重整就Ok了，不用担心发生相关联的错误（因为货品编号根本就没有和别人发生关联啊）。

        如果前面的所说身份系统，如果当初不用身份证号Key,只用内部ID做Key，身份证号需要调整，直接修改本表就是了，系统不用做任何调整，也不会带来任何维护成本（让用户修改备注字段（身份证号啊））。

         希望大家在设计表结构的时候不要犯下这种致命的错误，否则后患无穷啊，希望本文对大家有帮助！这也是本人第一次写技术博客，谢谢大家！