文档章节

数据库设计中常见表结构的设计技巧(转)

v
 viewsonic001
发布于 2016/09/23 10:54
字数 5339
阅读 277
收藏 2

一、树型关系的数据表

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

类别表_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)

三、多用户及其权限管理的设计

开发数据库管理类的软件,不可能不考虑多用户和用户权限设置的问题。 尽管目前市面上的大、中型的后台数据库系统软件都提供了多用户,以及细至某个数据库内某张表的权限设置的功能,我个人建议:一套成熟的数据库管理软件,还是应该自行设计用户管理这块功能,原因有二:

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 不允许为空 所属用户组标识,和User_group.group_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 不允许为空 用户标识,和Renter_table.renter_id关联
b_id int 不允许为空 书籍标识,和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 不允许为空 批量借阅时间

这样的设计好吗?我们来看看为了列出某个用户某次借阅的所有书籍,需要如何查询?首先检索批量借阅表(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 不允许为空 书籍标识,和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 不允许为空 每餐总表标识,和Eatdata1.totle_id关联
c_id int 不允许为空 员工标识标识,和Clerk_table.clerk_id关联
price money 不允许为空 每人每餐花费

其中,就餐计费细表(Eatdata2)的记录就是把每餐总表(Eatdata1)的一条记录按就餐员工平摊拆开,是个不折不扣的冗余表。当然,也可以把每餐总表(Eatdata1)的部分字段合并到就餐计费细表(Eatdata2)中,这样每餐总表(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 (totleid=tid) WHERE eat_date>=CONVERT(datetime,'"&the_date&"') AND eat_date<DATEADD(month,1,CONVERT(datetime,'"&the_date&"')) GROUP BY c_id

想象一下,如果不用这个冗余表,每次统计每人每月的餐费总帐时会多麻烦,程序效率也够呛。那么,到底什么时候可以增加一定的冗余数据呢?我认为有2个原则:

1、用户的整体需求。当用户更多的关注于,对数据库的规范记录按一定的算法进行处理后,再列出的数据。如果该算法可以直接利用后台数据库系统的内嵌函数来完成,此时可以适当的增加冗余字段,甚至冗余表来保存这些经过算法处理后的数据。要知道,对于大批量数据的查询,修改或删除,后台数据库系统的效率远远高于我们自己编写的代码。

2、简化开发的复杂度。现代软件开发,实现同样的功能,方法有很多。尽管不必要求程序员精通绝大部分的开发工具和平台,但是还是需要了解哪种方法搭配哪种开发工具的程序更简洁,效率更高一些。冗余数据的本质就是用空间换时间,尤其是目前硬件的发展远远高于软件,所以适当的冗余是可以接受的。不过我还是在最后再强调一下:不要过多的依赖平台和开发工具的特性来简化开发,这个度要是没把握好的话,后期维护升级会栽大跟头的。

本文转载自:http://www.cnblogs.com/hoojjack/p/4705830.html

上一篇: mysql 安装--Windows7
下一篇: 面向对象
v
粉丝 1
博文 52
码字总数 9151
作品 0
杭州
程序员
私信 提问
SQL Server 2012入门T-SQL基础篇:(2)SQL的两个基础概念

这里介绍两个SQL的两个基本的概念,以方便后期的学习: (1)SQL全称structured query language,中文为结构化查询语言;它可以在各类数据库上使用,例如,mysql,oracle等,当然包括SQL server. T-SQL...

技术小阿哥
2017/11/27
0
0
数据库设计中常见的糟糕设计以及对应的改良。

大家知道有讲述数据库设计常见问题并给出相应解决方案的书籍或文章吗?求推荐啊。 前段时间看了《代码重构》一书,当时就有种被击中G点般的亢奋。因为里边提到很多我实际工作中提到的问题,并...

itwriter
2015/03/27
347
3
java入门学习之路

java是面向对象的编程,个人觉得记忆比较重要。 第一阶段 java基础部分 unix开发环境 熟练掌握开发中常用的操作系统linux的安装及使用:掌握文件系统、网络、用户管理方法:能熟练使用vi和v...

瑞克3号
2017/07/10
0
0
基于C# winform企业进销存软件开发

基于C# winform企业进销存软件开发视频教程 下载地址分享:http://pan.baidu.com/s/1jI4w4bO 密码:qtvc 课程共6章,70讲,本教程以进销存管理实务为基础,使用流行的C#语言。涵盖软件开发的全...

妮妮我爱你
2017/03/29
204
1
ARM 选型 —— arm,dap,fpga

http://lib.csdn.net/article/deeplearning/53068 ARM公司自1990年正式成立以来,在32位RISC(Reduced Instruction Set Computer)CPU开发领域不断取得突破,其结构已经从V3发展到V6。由于A...

ilmfmly
2017/05/19
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Java Varargs 可变参数使用

Java1.5 提供了一个叫varargs的新功能,就是可变长度的参数。 "Varargs"是 “variable number of arguments”的意思。有时候也被简单的称为 “variable arguments”。 定义实参个数可变的方法...

honeymoose
今天
69
0
IDEA 拉取、上传、更新 项目到 Gitee+GitHub_超详细超简单版

注:本人使用的idea是最新版(2019.1.2),要是其他的版本的不要惊慌〜,基本上都一样,没有什么太大的差别的 首先我要说一下,拉取项目分两个,一个,你就没有项目,拉取仓库的整个项目,而...

杨木发
今天
54
0
pyqt5环境搭建(Ubuntu19.10+pycharm+python3)

1.安装pyqt5 sudo apt-get install python3-pyqt5 sudo apt-get install qttools5-dev-tools sudo apt-get install qt5-default 2.安装pycharm 下载pycharm社区版安装包并解压 在桌面新建pyc......

小芯片
今天
54
0
Vue造轮子-tab组件(中)

1. 如果给一个标签一个class,标签本身又有class,vue是默认会合并的。只有两个属性是这样一个是class,一个是style。这样就比较好改样式。 <g-tabs-head class="red"></g-tabs> 2. 组件的...

ories
昨天
59
0
Windows 版本 Anaconda 配置加速源安装软件

C:\Users\lenovo\.condarc 首先安装Anaconda最新版本。 其次添加安装目录到环境变量。文本为 C:\ProgramData\Anaconda3\Library\bin 运行 conda 命令在 Windows 用户下生成文件 .conda...

白豆腐徐长卿
昨天
232
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部