浅谈数据库设计技巧Word格式.docx
《浅谈数据库设计技巧Word格式.docx》由会员分享，可在线阅读，更多相关《浅谈数据库设计技巧Word格式.docx（13页珍藏版）》请在冰点文库上搜索。
一、树型关系的数据表
不少程序员在进行数据库设计的时候都遇到过树型关系的数据，例如常见的类别表，即一个大类，下面有若干个子类，某些子类又有子类这样的情况。
当类别不确定，用户希望可以在任意类别下添加新的子类，或者删除某个类别和其下的所有子类，而且预计以后其数量会逐步增长，此时我们就会考虑用一个数据表来保存这些数据。
按照教科书上的教导，第二类程序员大概会设计出类似这样的数据表结构：
类别表_1（Type_table_1）
名称 类型 约束条件 说明
type_id
int
无重复
类别标识，主键
type_name char（50）
不允许为空
类型名称，不允许重复
type_father
该类别的父类别标识，如果是顶节点的话设定为某个唯一值
这样的设计短小精悍，完全满足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_layer
char（6）
限定3层,初始值为000000
类别的先序遍历，主要为减少检索数据库的次数
按照这样的表结构，我们来看看上面例子记录在表中的数据是怎样的：
type_name
type_father
type_layer
1
总类别
0
000000
2
类别1
1
010000
3
类别1.1
2
010100
4
类别1.2
010200
5
类别2
020000
6
类别2.1
5
020100
7
类别3
030000
8
类别3.1
7
030100
9
类别3.2
030200
10
类别1.1.1
3
010101
……
现在按type_layer的大小来检索一下：
SELECT*FROMType_table_2ORDERBYtype_layer
列出记录集如下：
现在列出的记录顺序正好是先序遍历的结果。
在控制显示类别的层次时，只要对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）
供货厂商表（Wares_provider）
provider_id
供货商标识，主键
provider_namechar（100）
供货商名称
商品信息表（Wares_info）
名称
类型 约束条件
wares_id
商品标识，主键
wares_name
char（100）
商品名称
wares_type int
不允许为空 商品类型标识，和Wares_type.type_id关联
wares_info
char（200）
允许为空
相关信息
provider
供货厂商标识，和Wares_provider.provider_id关联
setnum
初始值为1
内含件数，默认为1
stock
初始值为0
库存，默认为0
buy_price
money
进货价
sell_price
销售价
discount
优惠价
你拿着这3个表给老板检查，老板希望能够再添加一个商品图片的字段，不过只有一部分商品有图片。
OK，你在商品信息表（Wares_info）中增加了一个haspic的BOOL型字段，然后再建了一个新表——商品图片表（Wares_pic）：
商品图片表（Wares_pic）
pic_id
商品图片标识，主键
所属商品标识，和Wares_info.wares_id关联
pic_address char（200）
不允许为空 图片存放路径
程序开发完成后，完全满足老板目前的要求，于是正式启用。
一段时间后，老板打算在这套平台上推出新的商品销售，其中，某类商品全部都需添加“长度”的属性。
第一轮折腾来了……当然，你按照添加商品图片表的老方法，在商品信息表（Wares_info）中增加了一个haslength的BOOL型字段，又建了一个新表——商品长度表（Wares_length）：
商品长度表（Wares_length）
length_id
length
char（20）
不允许为空 商品长度说明
刚刚改完没多久，老板又打算上一批新的商品，这次某类商品全部需要添加“宽度”的属性。
你咬了咬牙，又照方抓药，添加了商品宽度表（Wares_width）。
又过了一段时间，老板新上的商品中有一些需要添加“高度”的属性，你是不是开始觉得你所设计的数据库按照这种方式增长下去，很快就能变成一个迷宫呢？
那么，有没有什么办法遏制这种不可预见性，但却类似重复的数据库膨胀呢？
我在阅读《敏捷软件开发：
原则、模式与实践》中发现作者举过类似的例子：
7.3 “Copy”程序。
其中，我非常赞同敏捷软件开发这个观点：
在最初几乎不进行预先设计，但是一旦需求发生变化，此时作为一名追求卓越的程序员，应该从头审查整个架构设计，在此次修改中设计出能够满足日后类似修改的系统架构。
下面是我在需要添加“长度”的属性时所提供的修改方案：
去掉商品信息表（Wares_info）中的haspic字段，添加商品额外属性表（Wares_ex_property）和商品额外信息表（Wares_ex_info）2个表来完成添加新属性的功能。
商品额外属性表（Wares_ex_property）
ex_pid
商品额外属性标识，主键
p_name
额外属性名称
商品额外信息表（Wares_ex_info）
ex_iid
商品额外信息标识，主键
property_id
不允许为空 商品额外属性标识，和Wares_ex_property.ex_pid关联
property_value
商品额外属性值
在商品额外属性表（Wares_ex_property）中添加2条记录：
p_name
商品图片
商品长度
再在整个电子商务平台的后台管理功能中追加一项商品额外属性管理的功能，以后添加新的商品时出现新的属性，只需利用该功能往商品额外属性表（Wares_ex_property）中添加一条记录即可。
不要害怕变化，被第一颗子弹击中并不是坏事，坏的是被相同轨道飞来的第二颗、第三颗子弹击中。
第一颗子弹来得越早，所受的伤越重，之后的抵抗力也越强8）（待续）
三、多用户及其权限管理的设计
开发数据库管理类的软件，不可能不考虑多用户和用户权限设置的问题。
尽管目前市面上的大、中型的后台数据库系统软件都提供了多用户，以及细至某个数据库内某张表的权限设置的功能，我个人建议：
一套成熟的数据库管理软件，还是应该自行设计用户管理这块功能，原因有二：
1.那些大、中型后台数据库系统软件所提供的多用户及其权限设置都是针对数据库的共有属性，并不一定能完全满足某些特例的需求；
2.不要过多的依赖后台数据库系统软件的某些特殊功能，多种大、中型后台数据库系统软件之间并不完全兼容。
否则一旦日后需要转换数据库平台或后台数据库系统软件版本升级，之前的架构设计很可能无法重用。
下面看看如何自行设计一套比较灵活的多用户管理模块，即该数据库管理软件的系统管理员可以自行添加新用户，修改已有用户的权限，删除已有用户。
首先，分析用户需求，列出该数据库管理软件所有需要实现的功能；
然后，根据一定的联系对这些功能进行分类，即把某类用户需使用的功能归为一类；
最后开始建表：
功能表（Function_table）
f_id
功能标识，主键
f_name
功能名称，不允许重复
f_desc
char（50）
功能描述
用户组表（User_group）
group_id
无重复
用户组标识，主键
group_name
用户组名称
group_power
用户组权限表，内容为功能表f_id的集合
用户表（User_table）
user_id
用户标识，主键
user_name
用户名
user_pwd
用户密码
user_type
所属用户组标识，和User_group.group_id关联
采用这种用户组的架构设计，当需要添加新用户时，只需指定新用户所属的用户组；
当以后系统需要添加新功能或对旧有功能权限进行修改时，只用操作功能表和用户组表的记录，原有用户的功能即可相应随之变化。
当然，这种架构设计把数据库管理软件的功能判定移到了前台，使得前台开发相对复杂一些。
但是，当用户数较大（10人以上），或日后软件升级的概率较大时，这个代价是值得的。
四、简洁的批量m:
n设计
碰到m:
n的关系，一般都是建立3个表，m一个，n一个，m:
n一个。
但是，m:
n有时会遇到批量处理的情况，例如到图书馆借书，一般都是允许用户同时借阅n本书，如果要求按批查询借阅记录，即列出某个用户某次借阅的所有书籍，该如何设计呢？
让我们建好必须的3个表先：
书籍表（Book_table）
book_id
书籍标识，主键
book_no
书籍编号
book_name
书籍名称
借阅用户表（Renter_table）
renter_id
renter_name
用户姓名
借阅记录表（Rent_log）
rent_id
借阅记录标识，主键
r_id
用户标识，和Renter_table.renter_id关联
b_id
书籍标识，和Book_table.book_id关联
rent_date
升级会员 