浅谈数据库设计技巧.doc

1、浅谈数据库设计技巧 数据库的最初雏形据说源自美国一个奶牛场的记账薄(纸质的，由此可见，数据库并不 一定是存储在电脑里的数据_)，里面记录的是该奶牛场的收支账目，程序员在将其整理、录入到电脑中时从中受到启发。当按照规定好的数据结构所采集到的 数据量大到一定程度后，出于程序执行效率的考虑，程序员将其中的检索、更新维护等功能分离出来，做成单独调用的模块，这个模块后来就慢慢发展、演变成现在 我们所接触到的数据库管理系统(DBMS)程序开发中的一个重要分支。下面进入正题，首先给数据库设计人员的功底分一下类：、没有系统学习过数据结构的程序员。这类程序员的作品往往只是他们的即兴玩具，他们往往习惯只设计有限的

2、几个表，实现某类功能的数据全部塞在一个表 中，各表之间几乎毫无关联。网上不少的免费管理软件都是这样的东西，当程序功能有限，数据量不多的时候，其程序运行起来没有什么问题，但是如果用其管理比 较重要的数据，风险性非常大。、系统学习过数据结构，但是还没有开发过对程序效率要求比较高的管理软件的程序员。这类人多半刚从学校毕业不 久，他们在设计数据库表结构时，严格按照教科书上的规定，死扣 E-R 图和 3NF(别灰心，所有的数据库设计高手都是从这一步开始的)。他们的作品，对于一 般的 access 型轻量级的管理软件，已经够用。但是一旦该系统需要添加新功能，原有的数据库表差不多得进行大换血。、第二类程序员

3、，在经 历过数次程序效率的提升，以及功能升级的折腾后，终于升级成为数据库设计的老鸟，第一类程序员眼中的高人。这类程序员可以胜任二十个表以上的中型商业数据 管理系统的开发工作。他们知道该在什么样的情况下保留一定的冗余数据来提高程序效率，而且其设计的数据库可拓展性较好，当用户需要添加新功能时，原有数据 库表只需做少量修改即可。、在经历过上十个类似数据库管理软件的重复设计后，第三类程序员中坚持下来没有转行，而是希望从中找出“偷懒”窍 门的有心人会慢慢觉悟，从而完成量变到质变的转换。他们所设计的数据库表结构有一定的远见，能够预测到未来功能升级所需要的数据，从而预先留下伏笔。这类 程序员目前大多晋级成数

4、据挖掘方面的高级软件开发人员。、第三类程序员或第四类程序员，在对现有的各家数据库管理系统的原理和开发都有一定的钻研后，要么在其基础上进行二次开发，要么自行开发一套有自主版权的通用数据库管理系统。本文所列出的一些设计技巧只适合第二类和部分第三类数据库设计人员。一、树型关系的数据表不少程序员在进行数据库设计的时候都遇到过树型关系的数据，例如常见的类别表，即一个大类，下面有若干个子类，某些子类又有子类这样的情况。当类别不确 定，用户希望可以在任意类别下添加新的子类，或者删除某个类别和其下的所有子类，而且预计以后其数量会逐步增长，此时我们就会考虑用一个数据表来保存这些 数据。按照教科书上的教导，第二类

5、程序员大概会设计出类似这样的数据表结构：类别表_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类

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

7、扩充，再对添加类型的数量进行一下约束就行 了，要完成上面的列表只需一次检索就行了。下面是扩充后的数据表结构：类别表_2(Type_table_2)名称 类型 约束条件 说明type_id int 无重复 类别标识，主键type_name char(50) 不允许为空 类型名称，不允许重复type_father int 不允许为空 该类别的父类别标识，如果是顶节点的话设定为某个唯一值type_layer char(6) 限定 3 层,初始值为 000000 类别的先序遍历，主要为减少检索数据库的次数按照这样的表结构，我们来看看上面例子记录在表中的数据是怎样的：type_id type_name

8、type_father type_layer1 总类别 0 0000002 类别 1 1 0100003 类别 1.1 2 0101004 类别 1.2 2 0102005 类别 2 1 0200006 类别 2.1 5 0201007 类别 3 1 0300008 类别 3.1 7 0301009 类别 3.2 7 03020010 类别 1.1.1 3 010101现在按 type_layer 的大小来检索一下：SELECT * FROM Type_table_2 ORDER BY type_layer列出记录集如下：type_id type_name type_father type_l

9、ayer1 总类别 0 0000002 类别 1 1 0100003 类别 1.1 2 01010010 类别 1.1.1 3 0101014 类别 1.2 2 0102005 类别 2 1 0200006 类别 2.1 5 0201007 类别 3 1 0300008 类别 3.1 7 0301009 类别 3.2 7 030200现在列出的记录顺序正好是先序遍历的结果。在控制显示类别的层次时，只要对type_layer 字段中的数值进行判断，每 2 位一组，如大于 0 则向右移 2 个空格。当然，我这个例子中设定的限制条件是最多 3 层，每层最多可设 99 个子类别，只要按用户的需求情况修

10、改一下 type_layer 的长度和位数，即可 更改限制层数和子类别数。其实，上面的设计不单单只在类别表中用到，网上某些可按树型列表显示的论坛程序大多采用类似的设计。或许有 人认为，Type_table_2 中的 type_father 字段是冗余数据，可以除去。如果这样，在插入、删除某个类别的时候，就得对 type_layer 的内容进行比较繁琐的判定，所以我并没有消去 type_father 字段，这也正符合数据库设计中适当保留冗余数据的来降低程序复杂度的原则，后面我会举 一个故意增加数据冗余的案例。二、商品信息表的设计假设你是一家百货公司电脑部的开发人员，某天老板要求你为公司开发一 套

11、网上电子商务平台，该百货公司有数千种商品出售，不过目前仅打算先在网上销售数十种方便运输的商品，当然，以后可能会陆续在该电子商务平台上增加新的商 品出售。现在开始进行该平台数据库的商品信息表的设计。每种出售的商品都会有相同的属性，如商品编号，商品名称，商品所属类别，相关信息，供货厂商，内含 件数，库存，进货价，销售价，优惠价。你很快就设计出 4 个表：商品类型表(Wares_type)，供货厂商表(Wares_provider)，商品信 息表(Wares_info)：商品类型表(Wares_type)名称 类型 约束条件 说明type_id int 无重复 类别标识，主键type_name ch

12、ar(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

13、) 不允许为空 商品名称wares_type int 不允许为空 商品类型标识，和Wares_type.type_id 关联wares_info char(200) 允许为空 相关信息provider int 不允许为空 供货厂商标识，和Wares_provider.provider_id 关联setnum int 初始值为 1 内含件数，默认为 1stock int 初始值为 0 库存，默认为 0buy_price money 不允许为空 进货价sell_price money 不允许为空 销售价discount money 不允许为空 优惠价你拿着这 3 个表给老板检查，老板希望能够再添加

14、一个商品图片的字段，不过只有一部分商品有图片。OK，你在商品信息表(Wares_info)中增加了一个 haspic 的 BOOL 型字段，然后再建了一个新表商品图片表(Wares_pic)：商品图片表(Wares_pic)名称 类型 约束条件 说明pic_id int 无重复 商品图片标识，主键wares_id int 不允许为空 所属商品标识，和 Wares_info.wares_id 关联pic_address char(200) 不允许为空 图片存放路径程序开发完成后，完全满足老板目前的要求，于是正式启用。一段时间后，老板打算在这套平台上推出新的商品销售，其中，某类商品全部都需添加“长

15、度”的属 性。第一轮折腾来了当然，你按照添加商品图片表的老方法，在商品信息表(Wares_info)中增加了一个 haslength 的 BOOL 型字段，又 建了一个新表商品长度表(Wares_length)：商品长度表(Wares_length)名称 类型 约束条件 说明length_id int 无重复 商品图片标识，主键wares_id int 不允许为空 所属商品标识，和 Wares_info.wares_id 关联length char(20) 不允许为空 商品长度说明刚刚改完没多久，老板又打算上一批新的商品，这次某类商品全部需要添加“宽度”的属性。你咬了咬牙，又照方抓药，添加了商

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

17、(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 关联prop

18、erty_id int 不允许为空 商品额外属性标识，和Wares_ex_property.ex_pid 关联property_value char(200) 不允许为空 商品额外属性值在商品额外属性表(Wares_ex_property)中添加 2 条记录：ex_pid p_name1 商品图片2 商品长度再在整个电子商务平台的后台管理功能中追加一项商品额外属性管理的功能，以后添加新的商品时出现新的属性，只需利用该功能往商品额外属性表 (Wares_ex_property)中添加一条记录即可。不要害怕变化，被第一颗子弹击中并不是坏事，坏的是被相同轨道飞来的第二颗、第三颗子弹击 中。第一颗子弹

19、来得越早，所受的伤越重，之后的抵抗力也越强 8)(待续)三、多用户及其权限管理的设计开发数据库管理类的软件，不可能不考虑多用户和用户权限设置的问题。尽管目前市面上的大、中型的后台数据库系统软件都提供了多用户，以及细至某个数据库内某张表的权限设置的功能，我个人建议：一套成熟的数据库管理软件，还是应该自行设计用户管理这块功能，原因有二：1.那些大、中型后台数据库系统软件所提供的多用户及其权限设置都是针对数据库的共有属性，并不一定能完全满足某些特例的需求；2.不要过多的依赖后台数据库系统软件的某些特殊功能，多种大、中型后台数据库系统软件之间并不完全兼容。否则一旦日后需要转换数据库平台或后台数据库系统

20、软件版本升级，之前的架构设计很可能无法重用。下面看看如何自行设计一套比较灵活的多用户管理模块，即该数据库管理软件的系统管理员可以自行添加新用户，修改已有用户的权限，删除已有用户。首先，分 析用户需求，列出该数据库管理软件所有需要实现的功能；然后，根据一定的联系对这些功能进行分类，即把某类用户需使用的功能归为一类；最后开始建表：功能表(Function_table)名称 类型 约束条件 说明f_id int 无重复 功能标识，主键f_name char(20) 不允许为空 功能名称，不允许重复f_desc char(50) 允许为空 功能描述用户组表(User_group)名称 类型 约束条件

21、说明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 不允许为空 所属用户组标识，和 User_group.group_id 关联采用这种用户组的架构设计，当需要添加新用户时，只需指定新用户所属的用户

22、组；当以后系统需要添加新功能或对旧有功能权限进行修改时，只用操作功能表和 用户组表的记录，原有用户的功能即可相应随之变化。当然，这种架构设计把数据库管理软件的功能判定移到了前台，使得前台开发相对复杂一些。但是，当用户数 较大(10 人以上)，或日后软件升级的概率较大时，这个代价是值得的。四、简洁的批量 m:n 设计碰到 m:n 的关 系，一般都是建立 3 个表，m 一个，n 一个，m:n 一个。但是，m:n 有时会遇到批量处理的情况，例如到图书馆借书，一般都是允许用户同时借阅 n 本书，如果 要求按批查询借阅记录，即列出某个用户某次借阅的所有书籍，该如何设计呢？让我们建好必须的 3 个表先：书

23、籍表(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 不允许为空 用户标识，和 Renter_table.renter_id 关联b_id in

24、t 不允许为空 书籍标识，和 Book_table.book_id 关联rent_date datetime 不允许为空 借阅时间为了实现按批查询借阅记录，我们可以再建一个表来保存批量借阅的信息，例如：批量借阅表(Batch_rent)名称 类型 约束条件 说明batch_id int 无重复 批量借阅标识，主键batch_no int 不允许为空 批量借阅编号，同一批借阅的 batch_no 相同rent_id int 不允许为空 借阅记录标识，和 Rent_log.rent_id 关联batch_date datetime 不允许为空 批量借阅时间这样的设计好吗？我们来看看为了列出某个用户

25、某次借阅的所有书籍，需要如何查询？首先检索批量借阅表(Batch_rent)，把符合条件的的所有记录 的 rent_id 字段的数据保存起来，再用这些数据作为查询条件带入到借阅记录表(Rent_log)中去查询。那么，有没有什么办法改进呢？下面给出一 种简洁的批量设计方案，不需添加新表，只需修改一下借阅记录表(Rent_log)即可。修改后的记录表(Rent_log)如下：借阅记录表(Rent_log)名称 类型 约束条件 说明rent_id int 无重复 借阅记录标识，主键r_id int 不允许为空 用户标识，和 Renter_table.renter_id 关联b_id int 不允许

26、为空 书籍标识，和 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 的嵌套查询即可。当然，这种设计不

27、符合 3NF，但是和上面标准的 3NF 设计比起来，哪一种更好呢？答案就不用我说了 吧。五、冗余数据的取舍上篇的“树型关系的数据表”中保留了一个冗余字段，这里的例子更进一步添加了一个冗 余表。先看看例子：我原先所在的公司为了解决员工的工作餐，和附近的一家小餐馆联系，每天吃饭记账，费用按人数平摊，月底由公司现金结算，每个人每个月的 工作餐费从工资中扣除。当然，每天吃饭的人员和人数都不是固定的，而且，由于每顿工作餐的所点的菜色不同，每顿的花费也不相同。例如，星期一中餐 5 人花费 40 元，晚餐 2 人花费 20，星期二中餐 6 人花费 36 元，晚餐 3 人花费 18 元。为了方便计算每个人每个

28、月的工作餐费，我写了一个简陋的就餐记账管理程序，数 据库里有 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 不允许为空 每餐总花费

29、persons_num int 不允许为空 就餐人数就餐计费细表(Eatdata2)名称 类型 约束条件 说明id int 无重复 就餐计费细表标识，主键t_id int 不允许为空 每餐总表标识，和 Eatdata1.totle_id 关联c_id int 不允许为空 员工标识标识，和 Clerk_table.clerk_id 关联price money 不允许为空 每人每餐花费其中，就餐计费细表(Eatdata2)的记录就是把每餐总表(Eatdata1)的一条记录按就餐员工平摊拆开，是个不折不扣的冗余表。当然，也可以 把每餐总表(Eatdata1)的部分字段合并到就餐计费细表(Eatdat

30、a2)中，这样每餐总表(Eatdata1)就成了冗余表，不过这样所设计出 来的就餐计费细表重复数据更多，相比来说还是上面的方案好些。但是，就是就餐计费细表(Eatdata2)这个冗余表，在做每月每人餐费统计的时候，大大 简化了编程的复杂度，只用类似这么一条查询语句即可统计出每人每月的寄餐次数和餐费总帐：SELECT clerk_name AS personname,COUNT(c_id) as eattimes,SUM(price) AS ptprice FROM Eatdata2 JOIN Clerk_tabsle ON (c_id=clerk_id) JOIN eatdata1 ON (t

31、otleid=tid) WHERE eat_date=CONVERT(datetime,“&the_date&“) AND eat_dateDATEADD(month,1,CONVERT(datetime,“&the_date&“) GROUP BY c_id想象一下，如果不用这个冗余表，每次统计每人每月的餐费总帐时会多麻烦，程序效率也够呛。那么，到底什么时候可以增加一定的冗余数据呢？我认为有 2 个原则：、用户的整体需求。当用户更多的关注于，对数据库的规范记录按一定的算法进行处理后，再列出的数据。如果该算法可以直接利用后台数据库系统的内嵌函数 来完成，此时可以适当的增加冗余字段，甚至冗余表来保存这些经过算法处理后的数据。要知道，对于大批量数据的查询，修改或删除，后台数据库系统的效率远远 高于我们自己编写的代码。、简化开发的复杂度。现代软件开发，实现同样的功能，方法有很多。尽管不必要求程序员精通绝大部分的开发工具和平 台，但是还是需要了解哪种方法搭配哪种开发工具的程序更简洁，效率更高一些。冗余数据的本质就是用空间换时间，尤其是目前硬件的发展远远高于软件，所以适 当的冗余是可以接受的。不过我还是在最后再强调一下：不要过多的依赖平台和开发工具的特性来简化开发，这个度要是没把握好的话，后期维护升级会栽大跟头 的。