数据库经典设计案例
说到数据库，我认为不能不先谈数据结构。1996年，在我初⼊⼤学学习计算机编程时，当时的⽼师就告诉我们说：计算机程序＝数据结构＋算法。尽管现在的程序开发已由⾯向过程为主逐步过渡到⾯向对象为主，但我还是深深赞同8年前⽼师的告诉我们的公式：计算机程序＝数据结构＋算法。⾯向对象的程序开发，要做的第⼀件事就是，先分析整个程序中需处理的数据，从中提取出抽象模板，以这个抽象模板设计类，再在其中逐步添加处理其数据的函数(即算法)，最后，再给类中的数据成员和函数划分访问权限，从⽽实现封装。
数据库的最初雏形据说源⾃美国⼀个奶⽜场的记账薄(纸质的，由此可见，数据库并不⼀定是存储在电脑⾥的数据^_^)，⾥⾯记录的是该奶⽜场的收⽀账⽬，程序员在将其整理、录⼊到电脑中时从中受到启发。当按照规定好的数据结构所采集到的数据量⼤到⼀定程度后，出于程序执⾏效率的考虑，程序员将其中的检索、更新维护等功能分离出来，做成单独调⽤的模块，这个模块后来就慢慢发展、演变成现在我们所接触到的数据库管理系统(DBMS)——程序开发中的⼀个重要分⽀。
下⾯进⼊正题，⾸先按我个⼈所接触过的程序给数据库设计⼈员的功底分⼀下类：
１、没有系统学习过数据结构的程序员。这类程序员的作品往往只是他们的即兴玩具，他们往往习惯只设计有限的⼏个表，实现某类功能的数据全部塞在⼀个表中，各表之间⼏乎毫⽆关联。⽹上不少的免费管理
软件都是这样的东西，当程序功能有限，数据量不多的时候，其程序运⾏起来没有什么问题，但是如果⽤其管理⽐较重要的数据，风险性⾮常⼤。
２、系统学习过数据结构，但是还没有开发过对程序效率要求⽐较⾼的管理软件的程序员。这类⼈多半刚从学校毕业不久，他们在设计数据库表结构时，严格按照教科书上的规定，死扣E-R图和3NF(别灰⼼，所有的数据库设计⾼⼿都是从这⼀步开始的)。他们的作品，对于⼀般的access型轻量级的管理软件，已经够⽤。但是⼀旦该系统需要添加新功能，原有的数据库表差不多得进⾏⼤换⾎。
３、第⼆类程序员，在经历过数次程序效率的提升，以及功能升级的折腾后，终于升级成为数据库设计的⽼鸟，第⼀类程序员眼中的⾼⼈。这类程序员可以胜任⼆⼗个表以上的中型商业数据管理系统的开发⼯作。他们知道该在什么样的情况下保留⼀定的冗余数据来提⾼程序效率，⽽且其设计的数据库可拓展性较好，当⽤户需要添加新功能时，原有数据库表只需做少量修改即可。
４、在经历过上⼗个类似数据库管理软件的重复设计后，第三类程序员中坚持下来没有转⾏，⽽是希望从中出“偷懒”窍门的有⼼⼈会慢慢觉悟，从⽽完成量变到质变的转换。他们所设计的数据库表结构有⼀定的远见，能够预测到未来功能升级所需要的数据，从⽽预先留下伏笔。这类程序员⽬前⼤多晋级成数据挖掘⽅⾯的⾼级软件开发⼈员。
５、第三类程序员或第四类程序员，在对现有的各家数据库管理系统的原理和开发都有⼀定的钻研后，
要么在其基础上进⾏⼆次开发，要么⾃⾏开发⼀套有⾃主版权的通⽤数据库管理系统。
我个⼈正处于第三类的末期，所以下⾯所列出的⼀些设计技巧只适合第⼆类和部分第三类数据库设计⼈员。同时，由于我很少碰到有兴趣在这⽅⾯深钻下去的同⾏，所以⽂中难免出现错误和遗漏，在此先⾏声明，欢迎⼤家指正，不要藏私哦8)
⼀、树型关系的数据表
不少程序员在进⾏数据库设计的时候都遇到过树型关系的数据，例如常见的类别表，即⼀个⼤类，下⾯有若⼲个⼦类，某些⼦类⼜有⼦类这样的情况。当类别不确定，⽤户希望可以在任意类别下添加新的⼦类，或者删除某个类别和其下的所有⼦类，⽽且预计以后其数量会逐步增长，此时我们就会考虑⽤⼀个数据表来保存这些数据。按照教科书上的教导，第⼆类程序员⼤概会设计出类似这样的数据表结构：
类别表_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        不允许为空商品类型标识，和pe_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__pid关联
property_value  char(200)  不允许为空商品额外属性值
在商品额外属性表(Wares_ex_property)中添加2条记录：
ex_pid            p_name
1                商品图⽚
2                商品长度
再在整个电⼦商务平台的后台管理功能中追加⼀项商品额外属性管理的功能，以后添加新的商品时出现新的属性，只需利⽤该功能往商品额外属性表(Wares_ex_property)中添加⼀条记录即可。不要害怕变化，被第⼀颗⼦弹击中并不是坏事，坏的是被相同轨道飞来的第⼆颗、第三颗⼦弹击中。第⼀颗⼦弹来得越早，所受的伤越重，之后的抵抗⼒也越强8)(待续)
三、多⽤户及其权限管理的设计
开发管理类的软件，不可能不考虑多⽤户和⽤户权限设置的问题。尽管⽬前市⾯上的⼤、中型的后台数据库系统软件都提供了多⽤户，以及细⾄某个数据库内某张表的权限设置的功能，我个⼈建议：⼀套成熟的数据库管理软件，还是应该⾃⾏设计⽤户管理这块功能，原因有⼆：
1.那些⼤、中型后台数据库系统软件所提供的多⽤户及其权限设置都是针对数据库的共有属性，并不⼀定能完全满⾜某些特例的需求；
2.不要过多的依赖后台数据库系统软件的某些特殊功能，多种⼤、中型后台数据库系统软件之间并不完全兼容。否则⼀旦⽇后需要转换数据库平台或后台数据库系统软件版本升级，之前的架构设计很可能⽆法重⽤。
下⾯看看如何⾃⾏设计⼀套⽐较灵活的多⽤户管理模块，即该数据库管理软件的系统管理员可以⾃⾏添加新⽤户，修改已有⽤户的权限，删除已有⽤户。⾸先，分析⽤户需求，列出该数据库管理软件所有需要实现的功能；然后，根据⼀定的联系对这些功能进⾏分类，即把某类⽤户需使⽤的功能归为⼀类；最后开始建表：
功能表(Function_table)
名称    　类型    约束条件  　说明
f_id          int      ⽆重复功能标识，主键
f_name        char(20)    不允许为空功能名称，不允许重复
f_desc        char(50)    允许为空功能描述
⽤户组表(User_group)
名称    　类型    约束条件  　说明
group_id      int        ⽆重复⽤户组标识，主键
group_name    char(20)    不允许为空⽤户组名称
group_power  char(100)  不允许为空⽤户组权限表，内容为功能表f_id的集合
⽤户表(User_table)
名称    　类型    约束条件  　说明
user_id      int        ⽆重复⽤户标识，主键
user_name    char(20)    ⽆重复⽤户名
user_pwd      char(20)    不允许为空⽤户密码
user_type    int        不允许为空所属⽤户组标识，和up_id关联
采⽤这种⽤户组的架构设计，当需要添加新⽤户时，只需指定新⽤户所属的⽤户组；当以后系统需要添加新功能或对旧有功能权限进⾏修改时，只⽤操作功能表和⽤户组表的记录，原有⽤户的功能即可相应随之变化。当然，这种架构设计把数据库管理软件的功能判定移到了前台，使得前台开发相对复杂⼀些。但是，当⽤户数较⼤(10⼈以上)，或⽇后软件升级的概率较⼤时，这个代价是值得的。
四、简洁的批量m:n设计
碰到m:n的关系，⼀般都是建⽴3个表，m⼀个，n⼀个，m:n⼀个。但是，m:n有时会遇到批量处理的情况，例如到图书馆借书，⼀般都是允许⽤户同时借阅n本书，如果要求按批查询借阅记录，即列出某个⽤户某次借阅的所有书籍，该如何设计呢？让我们建好必须的3个表先：
书籍表(Book_table)
名称    　类型    约束条件  　说明
book_id      int        ⽆重复书籍标识，主键
book_no      char(20)    ⽆重复书籍编号
book_name    char(100)  不允许为空书籍名称
……
借阅⽤户表(Renter_table)
名称    　类型    约束条件  　说明
renter_id    int        ⽆重复⽤户标识，主键
renter_name  char(20)    不允许为空⽤户姓名
……
借阅记录表(Rent_log)
名称    　类型    约束条件  　说明
rent_id      int        ⽆重复借阅记录标识，主键
r_id          int        不允许为空⽤户标识，和_id关联
b_id          int        不允许为空书籍标识，和Book_table.book_id关联
rent_date    datetime    不允许为空借阅时间
……
为了实现按批查询借阅记录，我们可以再建⼀个表来保存批量借阅的信息，例如：
批量借阅表(Batch_rent)
名称    　类型    约束条件  　说明
batch_id      int        ⽆重复批量借阅标识，主键
batch_no      int        不允许为空批量借阅编号，同⼀批借阅的batch_no相同
rent_id      int        不允许为空借阅记录标识，和_id关联
batch_date    datetime    不允许为空批量借阅时间
这样的设计好吗？我们来看看为了列出某个⽤户某次借阅的所有书籍，需要如何查询？⾸先检索批量借阅表(Batch_rent)，把符合条件的的所有记录的rent_id字段的数据保存起来，再⽤这些数据作为查询条件带⼊到借阅记录表(Rent_log)中去查询。那么，有没有什么办法改进呢？下⾯给出⼀种简洁的批量设计⽅案，不需添加新表，只需修改⼀下借阅记录表(Rent_log)即可。修改后的记录表(Rent_log)如下：
借阅记录表(Rent_log)
名称    　类型    约束条件  　说明
rent_id      int        ⽆重复借阅记录标识，主键
r_id          int        不允许为空⽤户标识，和_id关联
b_id          int        不允许为空书籍标识，和Book_table.book_id关联
batch_no      int        不允许为空批量借阅编号，同⼀批借阅的batch_no相同
rent_date    datetime    不允许为空借阅时间
……
其中，同⼀次借阅的batch_no和该批第⼀条⼊库的rent_id相同。举例：假设当前最⼤rent_id是64，接着某⽤户⼀次借阅了3本书，则批量插⼊的3条借阅记录的batch_no都是65。之后另外⼀个⽤户租了⼀套碟，再插⼊出租记录的rent_id是68。采⽤这种设计，查询批量借阅的信息时，只需使⽤⼀条标准T_SQL的嵌套查询即可。当然，这种设计不符合3NF，但是和上⾯标准的3NF设计⽐起来，哪⼀种更好呢？答案就不⽤我说了吧。
五、冗余数据的取舍
上篇的“树型关系的数据表”中保留了⼀个冗余字段，这⾥的例⼦更进⼀步——添加了⼀个冗余表。先看看例⼦：我原先所在的公司为了解决员⼯的⼯作餐，和附近的⼀家⼩餐馆联系，每天吃饭记账，费⽤按⼈数平摊，⽉底由公司现⾦结算，每个⼈每个⽉的⼯作餐费从⼯资中扣除。当然，每天吃饭的⼈员和⼈数都不是固定的，⽽且，由于每顿⼯作餐的所点的菜⾊不同，每顿的花费也不相同。例如，星期⼀中餐5⼈花费40元，晚餐2⼈花费20，星期⼆中餐6⼈花费36元，晚餐3⼈花费18元。为了⽅便计算每个⼈每个⽉的⼯作餐费，我写了⼀个简陋的就餐记账管理程序，数据库⾥有3个表：
员⼯表(Clerk_table)
名称    　类型    约束条件  　说明
clerk_id      int        ⽆重复员⼯标识，主键
clerk_name    char(10)    不允许为空员⼯姓名
每餐总表(Eatdata1)
名称    　类型    约束条件  　说明
totle_id      int        ⽆重复每餐总表标识，主键
persons      char(100)  不允许为空就餐员⼯的员⼯标识集合
eat_date      datetime    不允许为空就餐⽇期
eat_type      char(1)    不允许为空就餐类型，⽤来区分中、晚餐
totle_price  money      不允许为空每餐总花费
persons_num  int        不允许为空就餐⼈数
就餐计费细表(Eatdata2)
名称    　类型    约束条件  　说明
id            int        ⽆重复就餐计费细表标识，主键
t_id          int        不允许为空每餐总表标识，和le_id关联
c_id          int        不允许为空员⼯标识标识，和Clerk_table.clerk_id关联
price        money      不允许为空每⼈每餐花费
其中，就餐计费细表(Eatdata2)的记录就是把每餐总表(Eatdata1)的⼀条记录按就餐员⼯平摊拆开，是个不折不扣的冗余表。当然，也可以把每餐总表(Eatdata1)的部分字段合并到就餐计费细表(Eatdata2)中，这样每餐总表(Eatdata1)就成了冗余表，不过这样所设计出来的就餐计费细表重复数据更多，相⽐来说还是上⾯的⽅案好些。但是，就是就餐计费细表(Eatdata2)这个冗余表，在做每⽉每⼈餐费统计的时候，⼤⼤简化了编程的复杂度，只⽤类似这么⼀条查询语句即可统计出每⼈每⽉的寄餐次数和餐费总帐：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。