两个数据库设计实例--转

（注：摘自 http://blog.163.com/jiang-640/blog/static/86403594200932994637923）

 

一、树型关系的数据表

不少程序员在进行数据库设计的时候都遇到过树型关系的数据，例如常见的类别表，即一个大类，下面有若干个子类，某 些子类又有子类这样的情况。当类别不确定，用户希望可以在任意类别下添加新的子类，或者删除某个类别和其下的所有子类，而且预计以后其数量会逐步增长，此 时我们就会考虑用一个数据表来保存这些数据。按照教科书上的教导，第二类程序员大概会设计出类似这样的数据表结构：

 

类别表_1(Type_table_1)
名称　　　　　类型　　　　约束条件　　　说明
type_id 　 int 　 无重复　　 类别标识，主键
type_name　 char(50) 不允许为空 类型名称，不允许重复
type_father int 不允许为空 该类别的父类别标识，如果是顶节点的话设定为某个唯一值

 

这样的设计短小精悍，完全满足3NF，而且可以满足用户的所有要求。是不是这样就行呢？答案是NO！Why？我们来估计一下用户希望如何罗列出这个表的数据的。对用户而言，他当然期望按他所设定的层次关系一次罗列出所有的类别，例如这样：

总类别
　　类别1
　　　　类别1.1
　　　　　　类别1.1.1
　　　　类别1.2
　　类别2
　　　　类别2.1
　　类别3
　　　　类别3.1
　　　　类别3.2
　　……

 

看看为了实现这样的列表显示(树的先序遍历)，要对上面的表进行多少次检索？注意，尽管类别1.1.1可能是在类别3.2之后添加的记录，答案 仍然是N次。这样的效率对于少量的数据没什么影响，但是日后类型扩充到数十条甚至上百条记录后，单单列一次类型就要检索数十次该表，整个程序的运行效率就 不敢恭维了。或许第二类程序员会说，那我再建一个临时数组或临时表，专门保存类型表的先序遍历结果，这样只在第一次运行时检索数十次，再次罗列所有的类型 关系时就直接读那个临时数组或临时表就行了。其实，用不着再去分配一块新的内存来保存这些数据，只要对数据表进行一定的扩充，再对添加类型的数量进行一下 约束就行了，要完成上面的列表只需一次检索就行了。下面是扩充后的数据表结构：

 

类别表_2(Type_table_2)
名称　　　　　类型　　　　约束条件　　　 说明
type_id 　 int 　 无重复　　 类别标识，主键
type_name　 char(50) 不允许为空 类型名称，不允许重复
type_father int 不允许为空 该类别的父类别标识，如果是顶节点的话设定为某个唯一值
type_layer char(6) 限定3层,初始值为000000 类别的先序遍历，主要为减少检索数据库的次数

 

按照这样的表结构，我们来看看上面例子记录在表中的数据是怎样的：

 

type_id type_name type_father type_layer
1 总类别 0 000000
2 类别1 1 010000
3 类别1.1 2 010100
4 类别1.2 2 010200
5 类别2 1 020000
6 类别2.1 5 020100
7 类别3 1 030000
8 类别3.1 7 030100
9 类别3.2 7 030200
10 类别1.1.1 3 010101
……

 

现在按type_layer的大小来检索一下：SELECT * FROM Type_table_2 ORDER BY type_layer列出记录集如下：

 

type_id type_name type_father type_layer
1 总类别 0 000000
2 类别1 1 010000
3 类别1.1 2 010100
10 类别1.1.1 3 010101
4 类别1.2 2 010200
5 类别2 1 020000
6 类别2.1 5 020100
7 类别3 1 030000
8 类别3.1 7 030100
9 类别3.2 7 030200
……

 

现在列出的记录顺序正好是先序遍历的结果。在控制显示类别的层次时，只要对type_layer字段中的数值进行判断，每2位一组，如大于0则 向右移2个空格。当然，我这个例子中设定的限制条件是最多3层，每层最多可设99个子类别，只要按用户的需求情况修改一下type_layer的长度和位 数，即可更改限制层数和子类别数。其实，上面的设计不单单只在类别表中用到，网上某些可按树型列表显示的论坛程序大多采用类似的设计。

 

或许有人认为，Type_table_2中的type_father字段是冗余数据，可以除去。如果这样，在插入、删除某个类别的时候，就得对type_layer的内容进行比较繁琐的判定，所以我并没有消去type_father字段，这也正符合数据库设计中适当保留冗余数据的来降低程序复杂度的原则，后面我会举 一个故意增加数据冗余的案例。

　　
二、商品信息表的设计

假设你是一家百货公司电脑部的开发人员，某天老板要求你为公司开发一套网上电子商务平台，该百货 公司有数千种商品出售，不过目前仅打算先在网上销售数十种方便运输的商品，当然，以后可能会陆续在该电子商务平台上增加新的商品出售。现在开始进行该平台 数据库的商品信息表的设计。每种出售的商品都会有相同的属性，如商品编号，商品名称，商品所属类别，相关信息，供货厂商，内含件数，库存，进货价，销售 价，优惠价。你很快就设计出4个表：商品类型表(Wares_type)，供货厂商表(Wares_provider)，商品信息表(Wares_info)：

 

商品类型表(Wares_type)
名称　　　　　类型　　　　约束条件　　　 说明
type_id 　 int 　 无重复　　 类别标识，主键
type_name　 char(50) 不允许为空 类型名称，不允许重复
type_father int 不允许为空 该类别的父类别标识，如果是顶节点的话设定为某个唯一值
type_layer char(6) 限定3层,初始值为000000 类别的先序遍历，主要为减少检索数据库的次数

 

供货厂商表(Wares_provider)
名称　　　　　 类型　　　　约束条件　　　 说明
provider_id int 　 无重复　　 供货商标识，主键
provider_name char(100) 不允许为空 供货商名称

 

商品信息表(Wares_info)
名称　　　　 类型　　　　约束条件　　　 说明
wares_id int 　 无重复　　 商品标识，主键
wares_name char(100) 不允许为空 商品名称
wares_type　 int 不允许为空　　　　　　　商品类型标识，和Wares_type.type_id关联
wares_info char(200) 允许为空 相关信息
provider int 不允许为空 供货厂商标识，和Wares_provider.provider_id关联
setnum int 初始值为1 内含件数，默认为1
stock int 初始值为0 库存，默认为0
buy_price money 不允许为空 进货价
sell_price money 不允许为空 销售价
discount money 不允许为空 优惠价

 

你拿着这3个表给老板检查，老板希望能够再添加一个商品图片的字段，不过只有一部分商品有图片。OK，你在商品信息表(Wares_info)中增加了一个haspic的BOOL型字段，然后再建了一个新表——商品图片表(Wares_pic)：

 

商品图片表(Wares_pic)
名称　　　　 类型　　　　约束条件　　　 说明
pic_id int 　 无重复　　 商品图片标识，主键
wares_id int 不允许为空 所属商品标识，和Wares_info.wares_id关联
pic_address char(200) 不允许为空　　　　　　 图片存放路径

 

程序开发完成后，完全满足老板目前的要求，于是正式启用。一段时间后，老板打算在这套平台上推出新的商品销售，其中，某类商品全部都需添加“长 度”的属性。第一轮折腾来了……当然，你按照添加商品图片表的老方法，在商品信息表(Wares_info)中增加了一个haslength的BOOL型 字段，又建了一个新表——商品长度表(Wares_length)：

 

商品长度表(Wares_length)
名称　　　　 类型　　　　约束条件　　　 说明
length_id int 　 无重复　　 商品图片标识，主键
wares_id int 不允许为空 所属商品标识，和Wares_info.wares_id关联
length　 char(20) 不允许为空　　　　　　　商品长度说明

 

刚刚改完没多久，老板又打算上一批新的商品，这次某类商品全部需要添加“宽度”的属性。你咬了咬牙，又照方抓药，添加了商品宽度表(Wares_width)。又过了一段时间，老板新上的商品中有一些需要添加“高度”的属性，你是不是开始觉得你所设计的数据库按照这种方式增长下去， 很快就能变成一个迷宫呢？那么，有没有什么办法遏制这种不可预见性，但却类似重复的数据库膨胀呢？我在阅读《敏捷软件开发：原则、模式与实践》中发现作者 举过类似的例子：7.3　“Copy”程序。其中，我非常赞同敏捷软件开发这个观点：在最初几乎不进行预先设计，但是一旦需求发生变化，此时作为一名追求 卓越的程序员，应该从头审查整个架构设计，在此次修改中设计出能够满足日后类似修改的系统架构。下面是我在需要添加“长度”的属性时所提供的修改方案：

 

去掉商品信息表(Wares_info)中的haspic字段，添加商品额外属性表(Wares_ex_property)和商品额外信息表(Wares_ex_info)2个表来完成添加新属性的功能。

 

商品额外属性表(Wares_ex_property)

名称　　　　 类型　　　　约束条件　　　 说明
ex_pid int 　 无重复　　 商品额外属性标识，主键
p_name char(20) 不允许为空 额外属性名称

 

商品额外信息表(Wares_ex_info)
名称　　　　 类型　　　　约束条件　　　 说明
ex_iid int 　 无重复　　 商品额外信息标识，主键
wares_id int 不允许为空 所属商品标识，和Wares_info.wares_id关联
property_id　 int 不允许为空　　　　　　　商品额外属性标识，和Wares_ex_property.ex_pid关联
property_value char(200) 不允许为空 商品额外属性值

 

在商品额外属性表(Wares_ex_property)中添加2条记录：

ex_pid p_name
1 商品图片
2 商品长度

 

再在整个电子商务平台的后台管理功能中追加一项商品额外属性管理的功能，以后添加新的商品时出现新的属性，只需利用该功能往商品额外属性表(Wares_ex_property)中添加一条记录即可。不要害怕变化，被第一颗子弹击中并不是坏事，坏的是被相同轨道飞来的第二颗、第三颗子弹击 中。第一颗子弹来得越早，所受的伤越重，之后的抵抗力也越强8)