浅谈数据库设计技巧 (2)

这样的设计短小精悍，完全满足 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)    不允许为空　　　　　　　　　　 商品长度说明