数据库设计中的一些问题（讨论）

前提声明，个人观点：

没有最好的，只有最合适的。
对不同的视角，所谓的“最合适”也是不同的。
设计总是伴随者“妥协”的。

请不要在讨论中试图证明个人的观点是“最好的”。

大家都提出自己的经验、思路、教训等等，让参与讨论的人根据自己的条件（这个我们无法完全为他人设想），有所取舍的得到“我所正需要的”。
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

以下是针对事务型数据库：
1.是否使用联合主键？个人倾向于少采用联合主键。因为这样会降低索引的效率，联合主键一般都要用到至少一个业务字段，往往是字符串型的，而且理论上多字段的索引比单字段的索引要慢些。看上去似乎也不那么清爽。
在实际的设计中，我尽量避免使用联合主键，有些时候“不得不”使用联合主键。

2.PK采用无意义的字段（逻辑主键）还是有意义的字段（业务主键）？个人倾向于“逻辑主键”，理由是这样设计出的数据库模型结构清晰、关系脉络清楚，往往更符合“第三范式”（虽然不是故意的，呵呵）。而且更容易避开“联合主键”，而且可以使用索引效率高的字段类型，比如int、long、number。缺点是用无意义的字段建立表间的关系，使跨表查询增多，效率下降。（矛盾无处不在，前面刚说完可以提高效率，这里马上又降低效率）。“业务主键”可以提升查询编码的简洁度和效率。
个人使用实际状况，总体来说“逻辑主键”比“业务主键”执行效率低，但不会低到无法满足需求。采用“逻辑主键”比采用“业务主键”更利于数据库模型的结构、关系清晰，也更便于维护。
对于分析型数据库，如数据仓库，千万不要这样做。

3.不要使用多对多关系？个人倾向于少使用多对多关系。这个问题其实不是数据库设计的问题了，在数据库设计中，多对多关系也仅仅存在于逻辑模型（E-R）阶段，物理模型不在有多对多关系，实际数据库中也不会有“多对多”关系。这是使用ORM时的问题，比如使用Hibernate，多对多关系有时会使编码看起来灵活一些，代价是效率的明显降低。
个人实际使用中，设计时基本不考虑多对多关系，但编码时总会有小组成员使用一些多对多关系，自己建立多对多的ORM，使自己编码方便些，用在数据量小的地方，影响不大。大数据量，则“禁止使用”。

4.为每个表增加一个state字段？我习惯在设计时给每个表设一个state字段，取值0或1，默认值为1，具体业务意义或操作上的意义可以自定义。可以作为一个状态控制字段，如查询、更新、删除条件，单据是否有效（业务单据对应的表会有业务意义上的“有/无效”或“状态”字段，这种情况下，我还是会再加一个state字段），甚至仅仅是控制一条数据是否“有效”（有效的意义你自己定）。在数据迁移（如转入分析用的数据库）时也可能会发挥作用。

5.为每个表设置一些备用字段？没办法，我总是设计不出“完美”的数据表，给每个表加几个备用字段（我一般用字符串型，随你）可以应付“不时之需”，尤其是需要长期维护的、业务可能有临时性变动的系统。

6.尽量不要在一个表中存入其关联表的字段？建议不存！这样做确实可以提高查询效率，但在一个有很多表，并且关联表多的情况下，很难保持数据的一致性！数据库结构也比较糟糕。而且不存，也不会使效率十分低下。

7.不要去直接修改数据库？个人认为这点很重要，当需要修改时，应该先去修改模型，然后同步物理数据库，尤其是团队开发，否则要多做更多的事情来搞定，也可能会引入更多的错误。

评论

17 楼 ylt 2006-09-04  

hasi 写道

liu_w_j 写道

表间关系最好不要存在闭合环（不是指回路），否则表间关系的操作会很复杂

因该不会有人这么设计吧

很有可能这样。比方说一个父对象有多个子对象，而且一个经常需要的操作就是得到父对象的第一个子对象。那么很可能产生下面的设计：

table parent
       id 
       first_child_id

table child
       id
       order_id
       parent_id

这样就产生了闭合环。当然这是一个不好的设计，宁可多做一次查询。

16 楼 hasi 2006-09-04  

liu_w_j 写道

表间关系最好不要存在闭合环（不是指回路），否则表间关系的操作会很复杂

因该不会有人这么设计吧

15 楼 liu_w_j 2006-08-16  

表间关系最好不要存在闭合环（不是指回路），否则表间关系的操作会很复杂

14 楼 抛出异常的爱 2006-07-31  

关于联合主键
我想到一个方法
可以用equlse方法来使用
将两到三个应该联合的字段用equlse包装起来

不知这种作法合不合理有没有必病

13 楼 tianxinet 2006-07-27  

为了不使篇幅拉的太长，就不全部引用原文了：

这是根据情况的, 一般说来,对于业务型表, 使用逻辑主键比较好,而对于公共基础表, 则使用业务主键比较好.
>>一般是这样。（我曾接触过大型汽车制造集团的BOM，BOM对应到信息系统中就是基础表，一般我们说有“工艺BOM”、“生产BOM”，但是这里甚至还有一个“开发BOM”，承袭关系为“开发BOM”->“工艺BOM”->“生产BOM”，都是几十万以上的数据，从表的角度来看不算大表，但算是大的基础表，而且是分多层的（整车->总成->分总成->...->零件->材料，想想它涉及的面有多广），在国产化过程中所谓的“开发BOM”不断变动，导致“工艺BOM”，“生产BOM”也不断变动，这种情况还是使用逻辑主键比较好）

多对多关系是来自于逻辑模型, 但逻辑模型是物理模型的抽象, 谁所物理模型中不存在多对多关系呢? 在基于ORM的处理中,多对多关系往往是简化成双向的一对多关系(相对来说,多对多是复杂的). 是否禁止使用,更加在于模型的建立.
>>当然，从抽象来说，物理模型中存在多对多。但既然已经从抽象到具体了，这里指的就是具体，就别抽象回去了

是个不错的主意,不过,一般留着做状态的字段更加建议使用字位数据,比如0,1,2,4,8,而不是简单的0和1.
>>同意，这样更灵活

一个好的结构设置不在于备用字段,而在于扩展表. 备用字段始终都是有限并且类型固定的.
>>我说的备用字段是不做“正式”用途的，在维护过程中、临时业务调整中可能会用到。


对于非流程性的业务,也就是数据模块相对独立的,楼主的建议不错,但是对于流程性的业务,如基于订单的MRP, 供应链等, 特定的业务信息,也就是业务字段最好能够存在于相应的后续表中, 光关注结构而忽略效率并不是一个可取的方式.
>>这是一对矛盾，我觉得衡量的最恰当标准是性能、资源消耗情况，矛盾突出的时候最好的办法是在搭建模拟运行环境实测。根据实测情况选一个“性价比”比较好的方案。

在订单中,业务主键是订单编号,orderNo, 我不清楚对应楼上的名称还是id, 不过正如我前面说的,对于业务类型表,使用逻辑主键比较好些. 但是需要注意的是, 关联的含义有两种,一种是直接表的关联,一种是经过多层关联建立的间接关联. 对于前者,楼主的建议是对的,但对于后者, 就不一定了. 往往在实际系统中是需要牺牲结构来换取效率的.
>>没错，牺牲结构换效率的事经常做，牺牲效率换结构的事也没少做，呵呵

12 楼 凤舞凰扬 2006-03-03  

tianxinet 写道

hanny0918 写道

不错，不过第6条“尽量不要在一个表中存入其关联表的字段？”楼主怎么解决两张表的关联？
另建一张表来当两张表的关联表？这样是否太麻烦一点？
或者另有解决方法？

不是指的外键。

比如“订单表”和“收货表”通过orderid建立关系，收货表中当然要存orderid这个字段，但尽量不要为了方便把“ordername”这个字段也存入收货表

在订单中,业务主键是订单编号,orderNo, 我不清楚对应楼上的名称还是id, 不过正如我前面说的,对于业务类型表,使用逻辑主键比较好些. 但是需要注意的是, 关联的含义有两种,一种是直接表的关联,一种是经过多层关联建立的间接关联. 对于前者,楼主的建议是对的,但对于后者, 就不一定了. 往往在实际系统中是需要牺牲结构来换取效率的.

11 楼 凤舞凰扬 2006-03-03  

tianxinet 写道

以下是针对事务型数据库：
1.是否使用联合主键？个人倾向于少采用联合主键。因为这样会降低索引的效率，联合主键一般都要用到至少一个业务字段，往往是字符串型的，而且理论上多字段的索引比单字段的索引要慢些。看上去似乎也不那么清爽。
在实际的设计中，我尽量避免使用联合主键，有些时候“不得不”使用联合主键。

赞成!

引用

2.PK采用无意义的字段（逻辑主键）还是有意义的字段（业务主键）？个人倾向于“逻辑主键”，理由是这样设计出的数据库模型结构清晰、关系脉络清楚，往往更符合“第三范式”（虽然不是故意的，呵呵）。而且更容易避开“联合主键”，而且可以使用索引效率高的字段类型，比如int、long、number。缺点是用无意义的字段建立表间的关系，使跨表查询增多，效率下降。（矛盾无处不在，前面刚说完可以提高效率，这里马上又降低效率）。“业务主键”可以提升查询编码的简洁度和效率。
个人使用实际状况，总体来说“逻辑主键”比“业务主键”执行效率低，但不会低到无法满足需求。采用“逻辑主键”比采用“业务主键”更利于数据库模型的结构、关系清晰，也更便于维护。
对于分析型数据库，如数据仓库，千万不要这样做。

这是根据情况的, 一般说来,对于业务型表, 使用逻辑主键比较好,而对于公共基础表, 则使用业务主键比较好.

引用

3.不要使用多对多关系？个人倾向于少使用多对多关系。这个问题其实不是数据库设计的问题了，在数据库设计中，多对多关系也仅仅存在于逻辑模型（E-R）阶段，物理模型不在有多对多关系，实际数据库中也不会有“多对多”关系。这是使用ORM时的问题，比如使用Hibernate，多对多关系有时会使编码看起来灵活一些，代价是效率的明显降低。
个人实际使用中，设计时基本不考虑多对多关系，但编码时总会有小组成员使用一些多对多关系，自己建立多对多的ORM，使自己编码方便些，用在数据量小的地方，影响不大。大数据量，则“禁止使用”。

多对多关系是来自于逻辑模型, 但逻辑模型是物理模型的抽象, 谁所物理模型中不存在多对多关系呢? 在基于ORM的处理中,多对多关系往往是简化成双向的一对多关系(相对来说,多对多是复杂的). 是否禁止使用,更加在于模型的建立.

引用

4.为每个表增加一个state字段？我习惯在设计时给每个表设一个state字段，取值0或1，默认值为1，具体业务意义或操作上的意义可以自定义。可以作为一个状态控制字段，如查询、更新、删除条件，单据是否有效（业务单据对应的表会有业务意义上的“有/无效”或“状态”字段，这种情况下，我还是会再加一个state字段），甚至仅仅是控制一条数据是否“有效”（有效的意义你自己定）。在数据迁移（如转入分析用的数据库）时也可能会发挥作用。

是个不错的主意,不过,一般留着做状态的字段更加建议使用字位数据,比如0,1,2,4,8,而不是简单的0和1.

引用

5.为每个表设置一些备用字段？没办法，我总是设计不出“完美”的数据表，给每个表加几个备用字段（我一般用字符串型，随你）可以应付“不时之需”，尤其是需要长期维护的、业务可能有临时性变动的系统。

一个好的结构设置不在于备用字段,而在于扩展表. 备用字段始终都是有限并且类型固定的.

引用

6.尽量不要在一个表中存入其关联表的字段？建议不存！这样做确实可以提高查询效率，但在一个有很多表，并且关联表多的情况下，很难保持数据的一致性！数据库结构也比较糟糕。而且不存，也不会使效率十分低下。

对于非流程性的业务,也就是数据模块相对独立的,楼主的建议不错,但是对于流程性的业务,如基于订单的MRP, 供应链等, 特定的业务信息,也就是业务字段最好能够存在于相应的后续表中, 光关注结构而忽略效率并不是一个可取的方式.

引用

7.不要去直接修改数据库？个人认为这点很重要，当需要修改时，应该先去修改模型，然后同步物理数据库，尤其是团队开发，否则要多做更多的事情来搞定，也可能会引入更多的错误。

非常赞成.

10 楼 tianxinet 2006-02-26  

hanny0918 写道

不错，不过第6条“尽量不要在一个表中存入其关联表的字段？”楼主怎么解决两张表的关联？
另建一张表来当两张表的关联表？这样是否太麻烦一点？
或者另有解决方法？

不是指的外键。

比如“订单表”和“收货表”通过orderid建立关系，收货表中当然要存orderid这个字段，但尽量不要为了方便把“ordername”这个字段也存入收货表

9 楼 微雨燕双飞 2005-12-07  

wl95421 写道

我一般
还经常有的两个字段

一个是DOMAIN
还有一个是STAMP(时间戳)

加上Domain是因为我做的一些东西如权限
经常被多个系统使用
而且有时候有些表单的东西也用到DOMAIN

8 楼 微雨燕双飞 2005-12-06  

我一般使用如下的组织机构设计，在组织机构中增加一个内部码
1  总公司
  101 分公司
   10101 分公司部门

很多业务实体如果属于某一个部门，不是用部门表的主键去关联，而是直接使用内部码，这样可以方便的检索各级机构范围的数据

7 楼 戏说乾隆 2005-12-06  

学习了:)

6 楼 wl95421 2005-12-05  

我一般
还经常有的两个字段

一个是DOMAIN
还有一个是STAMP(时间戳)

加上Domain是因为我做的一些东西如权限
经常被多个系统使用
而且有时候有些表单的东西也用到DOMAIN

5 楼 微雨燕双飞 2005-12-04  

在没个表中增加一个精确到秒的时间戳字段，新增、修改时都设置新的值，在跟踪BUG太方便了

4 楼 hasi 2005-12-01  

cyberwjw 写道

我觉的修改记录时间，修改记录人一般不需要，你只是保存当前修改的人，如果下一个人人修改该条记录，就把前面的覆盖了；
创建记录时间，创建记录人这个看表的情况而定，如果不是重要的表，我觉的没有必要加。

当然只记录当前修改的人和时间了，因为以前修改的都无效阿，数据库里也没有历史记录

3 楼 hanny0918 2005-11-30  

不错，不过第6条“尽量不要在一个表中存入其关联表的字段？”楼主怎么解决两张表的关联？
另建一张表来当两张表的关联表？这样是否太麻烦一点？
或者另有解决方法？

2 楼 cyberwjw 2005-11-30  

我也基本上也同意搂主的，但是第5点一般我不会留备用字段，假如备用字段增加多了，也需要空间保留呀，会对数据库造成影响的，再说了如果需要增加字段的话我们可以临时加入，也不会影响以后的维护，对系统也不会造成影响。

hasi 写道

基本上同意楼主的观点，
还有我们一般在每个表加 创建记录时间，创建记录人，修改记录时间，修改记录人 四个字段的。

我觉的修改记录时间，修改记录人一般不需要，你只是保存当前修改的人，如果下一个人人修改该条记录，就把前面的覆盖了；
创建记录时间，创建记录人这个看表的情况而定，如果不是重要的表，我觉的没有必要加。

1 楼 hasi 2005-11-30  

基本上同意楼主的观点，
还有我们一般在每个表加 创建记录时间，创建记录人，修改记录时间，修改记录人 四个字段的。