分类目录归档:数据库

数据库范式(1NF 2NF 3NF BCNF)详解二


转自:http://jacki6.iteye.com/blog/774889#bc2330403

范式应用

 

我们来逐步搞定一个论坛的数据库,有如下信息:

 

(1) 用户:用户名,email,主页,电话,联系地址

 

(2) 帖子:发帖标题,发帖内容,回复标题,回复内容

 

第一次我们将数据库设计为仅仅存在表:

 

用户名 email 主页 电话 联系地址 发帖标题 发帖内容 回复标题 回复内容

 

这个数据库表符合第一范式,但是没有任何一组候选关键字能决定数据库表的整行,唯一的关键字段用户名也不能完全决定整个元组。我们需要增加”发帖ID”、”回复ID”字段,即将表修改为:

 

用户名 email 主页 电话 联系地址 发帖ID 发帖标题 发帖内容 回复ID 回复标题 回复内容

 

这样数据表中的关键字(用户名,发帖ID,回复ID)能决定整行:

 

(用户名,发帖ID,回复ID) → (email,主页,电话,联系地址,发帖标题,发帖内容,回复标题,回复内容)

 

但是,这样的设计不符合第二范式,因为存在如下决定关系:

 

(用户名) → (email,主页,电话,联系地址)

 

(发帖ID) → (发帖标题,发帖内容)

 

(回复ID) → (回复标题,回复内容)

 

即非关键字段部分函数依赖于候选关键字段,很明显,这个设计会导致大量的数据冗余和操作异常。

 

我们将数据库表分解为(带下划线的为关键字):

 

(1) 用户信息:用户名,email,主页,电话,联系地址

 

(2) 帖子信息:发帖ID,标题,内容

 

(3) 回复信息:回复ID,标题,内容

 

(4) 发贴:用户名,发帖ID

 

(5) 回复:发帖ID,回复ID

 

这样的设计是满足第1、2、3范式和BCNF范式要求的,但是这样的设计是不是最好的呢?

 

不一定。

 

观察可知,第4项”发帖”中的”用户名”和”发帖ID”之间是1:N的关系,因此我们可以把”发帖”合并到第2项的”帖子信息”中;第5项”回复”中的”发帖ID”和”回复ID”之间也是1:N的关系,因此我们可以把”回复”合并到第3项的”回复信息”中。这样可以一定量地减少数据冗余,新的设计为:

 

(1) 用户信息:用户名,email,主页,电话,联系地址

 

(2) 帖子信息:用户名,发帖ID,标题,内容

 

(3) 回复信息:发帖ID,回复ID,标题,内容

 

数据库表1显然满足所有范式的要求;

 

数据库表2中存在非关键字段”标题”、”内容”对关键字段”发帖ID”的部分函数依赖,即不满足第二范式的要求,但是这一设计并不会导致数据冗余和操作异常;

 

数据库表3中也存在非关键字段”标题”、”内容”对关键字段”回复ID”的部分函数依赖,也不满足第二范式的要求,但是与数据库表2相似,这一设计也不会导致数据冗余和操作异常。

 

由此可以看出,并不一定要强行满足范式的要求,对于1:N关系,当1的一边合并到N的那边后,N的那边就不再满足第二范式了,但是这种设计反而比较好!

 

对于M:N的关系,不能将M一边或N一边合并到另一边去,这样会导致不符合范式要求,同时导致操作异常和数据冗余。

对于1:1的关系,我们可以将左边的1或者右边的1合并到另一边去,设计导致不符合范式要求,但是并不会导致操作异常和数据冗余。

 

结论

 

满足范式要求的数据库设计是结构清晰的,同时可避免数据冗余和操作异常。这并意味着不符合范式要求的设计一定是错误的,在数据库表中存在1:1或1:N关系这种较特殊的情况下,合并导致的不符合范式要求反而是合理的。

 

在我们设计数据库的时候,一定要时刻考虑范式的要求。


数据库范式(1NF 2NF 3NF BCNF)详解一


转自:http://jacki6.iteye.com/blog/774866

数据库的设计范式是数据库设计所需要满足的规范,满足这些规范的数据库是简洁的、结构明晰的,同时,不会发生插入(insert)、删除(delete)和更新(update)操作异常。反之则是乱七八糟,不仅给数据库的编程人员制造麻烦,而且面目可憎,可能存储了大量不需要的冗余信息。

 

范式说明

 

1.1 第一范式(1NF)无重复的列

 

    所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性,就可能需要定义一个新的实体,新的实体由重复的属性构成,新实体与原实体之间为一对多关系。在第一范式(1NF)中表的每一行只包含一个实例的信息。简而言之,第一范式就是无重复的列。

 

说明:在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。

 

例如,如下的数据库表是符合第一范式的:

 

 


字段1

字段2

字段3

字段4

 

而这样的数据库表是不符合第一范式的:

 

 




字段1

字段2

字段3

字段4

 

 

字段3.1

字段3.2

 

         

 

数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。很显然,在当前的任何关系数据库管理系统(DBMS)中,傻瓜也不可能做出不符合第一范式的数据库,因为这些DBMS不允许你把数据库表的一列再分成二列或多列。因此,你想在现有的DBMS中设计出不符合第一范式的数据库都是不可能的。

 

1.2 第二范式(2NF)属性完全依赖于主键 [ 消除部分子函数依赖 ]

 

如果关系模式R为第一范式,并且R中每一个非主属性完全函数依赖于R的某个候选键, 则称为第二范式模式。

第二范式(2NF)是在第一范式(1NF)的基础上建立起来的,即满足第二范式(2NF)必须先满足第一范式(1NF)。第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

 

例如员工信息表中加上了员工编号(emp_id)列,因为每个员工的员工编号是惟一的,因此每个员工可以被惟一区分。

简而言之,第二范式(2NF)就是非主属性完全依赖于主关键字。

 

所谓完全依赖是指不能存在仅依赖主关键字一部分的属性(设有函数依赖W→A,若存在XW,有X→A成立,那么称W→A是局部依赖,否则就称W→A是完全函数依赖)。如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。

 

假定选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分),关键字为组合关键字(学号, 课程名称),因为存在如下决定关系:

 

(学号, 课程名称) → (姓名, 年龄, 成绩, 学分)

 

这个数据库表不满足第二范式,因为存在如下决定关系:

 

(课程名称) → (学分)

 

(学号) → (姓名, 年龄)

 

即存在组合关键字中的字段决定非关键字的情况。

 

由于不符合2NF,这个选课关系表会存在如下问题:

 

(1) 数据冗余:

 

同一门课程由n个学生选修,”学分”就重复n-1次;同一个学生选修了m门课程,姓名和年龄就重复了m-1次。

 

(2) 更新异常:

 

若调整了某门课程的学分,数据表中所有行的”学分”值都要更新,否则会出现同一门课程学分不同的情况。

 

(3) 插入异常:

 

假设要开设一门新的课程,暂时还没有人选修。这样,由于还没有”学号”关键字,课程名称和学分也无法记录入数据库。

 

(4) 删除异常:

 

假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除。但是,与此同时,课程名称和学分信息也被删除了。很显然,这也会导致插入异常。

 

把选课关系表SelectCourse改为如下三个表:

 

学生:Student(学号, 姓名, 年龄);

 

课程:Course(课程名称, 学分);

 

选课关系:SelectCourse(学号, 课程名称, 成绩)。

 

这样的数据库表是符合第二范式的, 消除了数据冗余、更新异常、插入异常和删除异常。

 

另外,所有单关键字的数据库表都符合第二范式,因为不可能存在组合关键字。

 

1.3 第三范式(3NF)属性不依赖于其它非主属性 [ 消除传递依赖 ]

 

如果关系模式R是第二范式,且每个非主属性都不传递依赖于R的候选键,则称R为第三范式模式。

    满足第三范式(3NF)必须先满足第二范式(2NF)。第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

 

例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。

 

第三范式(3NF):在第二范式的基础上,数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。简而言之,第三范式就是属性不依赖于其它非主属性。

 

所谓传递函数依赖,指的是如果存在”A → B → C”的决定关系,则C传递函数依赖于A。

 

因此,满足第三范式的数据库表应该不存在如下依赖关系:

 

关键字段 → 非关键字段x → 非关键字段y

 

假定学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话),关键字为单一关键字”学号”,因为存在如下决定关系:

 

(学号) → (姓名, 年龄, 所在学院, 学院地点, 学院电话)

 

这个数据库是符合2NF的,但是不符合3NF,因为存在如下决定关系:

 

(学号) → (所在学院) → (学院地点, 学院电话)

 

即存在非关键字段”学院地点”、”学院电话”对关键字段”学号”的传递函数依赖。

 

它也会存在数据冗余、更新异常、插入异常和删除异常的情况,读者可自行分析得知。

 

把学生关系表分为如下两个表:

 

学生:(学号, 姓名, 年龄, 所在学院);

 

学院:(学院, 地点, 电话)。

 

这样的数据库表是符合第三范式的,消除了数据冗余、更新异常、插入异常和删除异常。

 

1.4 鲍依斯-科得范式(BCNF是3NF的改进形式)

 

若关系模式R是第一范式,且每个属性都不传递依赖于R的候选键。这种关系模式就是BCNF模式。即在第三范式的基础上,数据库表中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合鲍依斯-科得范式。

 

假设仓库管理关系表为StorehouseManage(仓库ID, 存储物品ID, 管理员ID, 数量),且有一个管理员只在一个仓库工作;一个仓库可以存储多种物品。这个数据库表中存在如下决定关系:

 

(仓库ID, 存储物品ID) →(管理员ID, 数量)

 

(管理员ID, 存储物品ID) → (仓库ID, 数量)

 

所以,(仓库ID, 存储物品ID)和(管理员ID, 存储物品ID)都是StorehouseManage的候选关键字,表中的唯一非关键字段为数量,它是符合第三范式的。但是,由于存在如下决定关系:

 

(仓库ID) → (管理员ID)

 

(管理员ID) → (仓库ID)

 

即存在关键字段决定关键字段的情况,所以其不符合BCNF范式。它会出现如下异常情况:

 

(1) 删除异常:

 

当仓库被清空后,所有”存储物品ID”和”数量”信息被删除的同时,”仓库ID”和”管理员ID”信息也被删除了。

 

(2) 插入异常:

 

当仓库没有存储任何物品时,无法给仓库分配管理员。

 

(3) 更新异常:

 

如果仓库换了管理员,则表中所有行的管理员ID都要修改。

 

把仓库管理关系表分解为二个关系表:

 

仓库管理:StorehouseManage(仓库ID, 管理员ID);

 

仓库:Storehouse(仓库ID, 存储物品ID, 数量)。

 

这样的数据库表是符合BCNF范式的,消除了删除异常、插入异常和更新异常。

 

四种范式之间存在如下关系:

 


      

    

交叉表、行列转换和交叉查询经典


交叉表、行列转换和交叉查询经典


一、什么是交叉表

“交叉表”对象是一个网格,用来根据指定的条件返回值。数据显示在压缩行和列中。这种格式易于比较数据并辨别其趋势。它由三个元素组成:

  • 摘要字段
  • “交叉表”中的行沿水平方向延伸(从一侧到另一侧)。在上面的示例中,“手套”(Gloves) 是一行。
  • “交叉表”中的列沿垂直方向延伸(上下)。在上面的示例中,“美国”(USA) 是一列。
  • 汇总字段位于行和列的交叉处。每个交叉处的值代表对既满足行条件又满足列条件的记录的汇总(求和、计数等)。在上面的示例中,“手套”和“美国”交叉处的值是四,这是在美国销售的手套的数量。

“交叉表”还可以包括若干总计:

  • 每行的结尾是该行的总计。在上面的例子中,该总计代表一个产品在所有国家/地区的销售量。“手套”行结尾处的值是 8,这就是手套在所有国家/地区销售的总数。

    注意:    总计列可以出现在每一行的开头。


  • 每列的底部是该列的总计。在上面的例子中,该总计代表所有产品在一个国家/地区的销售量。“美国”一列底部的值是四,这是所有产品(手套、腰带和鞋子)在美国销售的总数。

    注意:    总计列可以出现在每一行的顶部。


  • “总计”(Total) 列(产品总计)和“总计”(Total) 行(国家/地区总计)的交叉处是总计。在上面的例子中,“总计”列和“总计”行交叉处的值是 12,这是所有产品在所有国家/地区销售的总数。

二、行列转换和交叉查询:

1: 列转为行:

eg1:

假设有张学生成绩表(CJ)如下
name      subject         result

张三         语文             80

张三         数学             90

张三         物理             85

李四         语文             85

李四         数学             92

李四         物理             82


相关sql语句:

Create table CJ(name char(10),subject char(10),result int);

insert into CJ(name,subject,result) values(‘张三’,’语文’,99);

insert into CJ(name,subject,result) values(‘张三’,’数学’,86);

insert into CJ(name,subject,result) values(‘张三’,’英语’,75);

insert into CJ(name,subject,result) values(‘李四’,’语文’,78);

insert into CJ(name,subject,result) values(‘李四’,’数学’,85);

insert into CJ(name,subject,result) values(‘李四’,’英语’,78)

select * from CJ


想变成如下的交叉表    
姓名        语文        数学        物理

张三         99          90           85

李四         85          92           82

我们首先来看一下如何建立静态的交叉表,也就是说列数固定的交叉表,这种情况其实只要一句简单的Select查询就可以搞定:

select name,sum(case when a.subject=’语文’ then result else null end) as “语文”,

                        sum(case when a.subject=’数学’ then result else null end) as “数学”,

                        sum(case when a.subject=’英语’ then result else null end) as “英语”


           from CJ a

           group by name;

当要增加“总计”列:”合计总分”时,如下表所示:

姓名        合计总分 语文        数学        物理

张三          260             99          90          85

李四          241             85          92          82

只需增加sum(a.result) as “合计总分”,sql如下:

select name,sum(a.result) as “合计总分”,

                        sum(case when a.subject=’语文’ then result else null end) as “语文”,

                        sum(case when a.subject=’数学’ then result else null end) as “数学”,

                        sum(case when a.subject=’英语’ then result else null end) as “英语”


           from CJ a

           group by name;

 

其中利用了CASE语句判断,如果是相应的列,则取需要统计的cj数值,否则取NULL,然后再合计。

其中有两个常见问题说明一下:

a、用NULL而不用0是有道理的,假如用0,虽然求和函数SUM可以取到正确的数,但类似COUNT函数(取记录个数),结果就不对了,因为Null不算一条记录,而0要算,同理空字串(””)也是这样,总之在这里应该用NULL,这样任何函数都没问题。

b、假如在视图的设计界面保存以上的查询,则会报错“没有输出列”,从而无法保存,其实只要在查询前面加上一段:Create View ViewName AS …,ViewName是你准备给查询起的名称,…就是我们的查询,然后运行一下,就可以生成视图了,对于其他一些设计器不支持的语法,也可以这样保存。

以上查询作用也很大,对于很多情况,比如产品销售表中按照季度统计、按照月份统计等列头内容固定的情况,这样就行了,但往往大多数情况下列头内容是不固定的,象City,用户随时可能删除、添加一些城市,这种情况就是我们所说的动态交叉表,在SQLServer中我们可以用存储过程来解决。下面我们补充一些知识:

相关子查询

相关子查询和普通子查询区别在于:相关子查询引用了外部查询的列。这种引用外部查询的能力意味着相关子查询不能自己独立运行,其中对于外部查询引用会使会使其无法正常执行。因此相关子查询的执行顺序如下:

1.首先执行一遍外部查询

2.对于外部查询的每一行分别执行一遍子查询,而且每次执行子查询时候都会引用外部的当前行的值。使用子查询的结果来确定外部查询的结果集。

举个例子;

SELECT t1.type

FROM titles t1

GROUP BY t1.type

HAVING MAX(t1.advance) >=ALL

        (SELECT 2 * AVG(t2.advance)

        FROM titles t2

        WHERE t1.type = t2.type)

这个结果返回最高预付款超过给定组中平均预付款两倍的书籍类型。

再举个例子:

要求返回每一个编号的最大值(列出id,name,score)
ID Name(编号) Score(分数)

1          a                   88

2          b                   76

3          c                   66

4          c                   90

5          b                   77

6          a                   56

7          b                   77

8          c                   67

9          a                   44


select * from t a where score=

(select Max(Score) from t b       where a.name=b.name)

再给一个排位的sql语句

SELECT (

SELECT count(*) 1 as dd

FROM [Test ] as a where a.[F2]<b.[F2] ) AS ord,b.[F1], b.[F2]

FROM [Test ] as b

order by b.[F2];

好了关于sql的相关子查询先讲到这里。

SQLServer中局部变量赋值方法

有两种:

一种: set @变量名 = 值

二种: select @变量名 = 值

第二种可以从某个表中得到数据再赋值给变量

例: 从用户信息表中查询中cid为 20 的用户姓名将他赋值给变量 name

declare @name varchar(10) –用户名

select @name=userName from userInfo where cid = 20

print ‘cid为20的用户姓名:’ + @name

递归的select变量

递归的select变量是指使用select语句和子查询将一个变量与其自身拼接起来。语法形式如下:select @variable = @variable + table.column from table—见《sql server2000宝典》:P354,这是一种很优美的查询方法.从而将基础表中垂直的列数据改为水平方向的数据。这样就可以替代游标。动态的交叉表这样就代替了传统的游标。

SQL语句解决方法:

写法一:

declare @sql varchar(4000)

set @sql = ‘select name’

select @sql = @sql + ‘,sum(case subject when ”’+subject+”’ then result end) as ‘+subject

             from (select distinct subject from CJ) as a

select @sql = @sql+’ from CJ group by name’

exec(@sql)

写法二:

declare @sql varchar(4000)

set @sql = ‘select name’

select @sql = @sql + ‘,sum(case subject when ”’+subject+”’ then result end) as ‘+subject

           +’ from CJ group by subject

select @sql = @sql+’ from CJ group by name’

exec(@sql)

具体不同的多种写法参见本文相关链接文章中的其他例子

在Access中还提供了TransForm来实现行列转换
TRANSFORM count(Result) AS number

SELECT 姓名

FROM 学生成绩表

GROUP BY 姓名

PIVOT Subject;


TransForm 用法如下:

=========================================================

TRANSFORM aggfunction

selectstatement

PIVOT pivotfield [IN (value1[, value2[, …]])]


TRANSFORM 语句可分为以下几个部分:


部分                 描述

aggfunction 在选定数据上运作的 SQL 合计函数。

selectstatement       SELECT 语句。

pivotfield 在查询的结果集中创建列标题时用的字段或表达式。

value1, value2 用来创建列标题的固定值。


说明

使用交叉表查询来摘要数据时,从指定的字段或表达式中选定值作为列标题,

这样,可以用比选定查询更紧凑的格式来观察数据。

TRANSFORM 是可选的,但在使用它时,要作为       SQL 字符串中的第一个语句。

它出现在 SELECT 语句(指定作为行标题的字段的)之前,还出现在 GROUP BY 子句

(指定行分组的)之前。可以有选择地包含其它子句,例如 WHERE 子句,它指定附

加的选择或排序条件。也可以将子查询当作谓词,特别是在叉表查询的 WHERE 子句中。


pivotfield 返回的值被用作查询结果集中的列标题。

例如,在交叉表查询中,将根据销售图表按销售月份创建 12 个列。

可以限制 pivotfield 用列在可选的 IN 子句中的固定值(value1, value2)来创建标题。

也可以用没有数据存在的固定值来创建附加的列。

2. 列行转换
暂时保留

3. 行列转换–加合并

有表A,
id pid

1        1

1        2

1        3

2        1

2        2

3        1


如何化成表B:
id      pid

1       1,2,3

2       1,2

3       1



创建一个合并的函数
create function fmerg(@id int)

returns varchar(8000)

as

begin

declare @str varchar(8000)

set @str=”

select @[email protected]+’,’+cast(pid as varchar) from 表A where [email protected]

set @str=right(@str,len(@str)-1)

return(@str)

End

go


–调用自定义函数得到结果

select distinct id,dbo.fmerg(id) from 表A


 

相关链接:

把列变成行的sql语句:
http://blog.csdn.net/liaoxiaohua1981/archive/2006/05/30/763721.aspx


应用SQL交叉表实现行列转换:
http://blog.csdn.net/sivee/archive/2007/05/06/1598039.aspx


oracle 行列转换:
http://blog.csdn.net/gogogo520/archive/2005/10/10/498779.aspx


行列转换例子:
http://blog.csdn.net/zsl5305256/archive/2006/12/05/1430422.aspx


动态SQL的使用例子, 行列转换:
http://blog.csdn.net/hertcloud/archive/2007/04/05/1552626.aspx


SqlServer如何生成动态交叉表查询:
http://dev.csdn.net/article/12/12618.shtm


SQL语句精典收藏http://hi.baidu.com/suofang/blog/item/35de9d23af3e5945ad34de8a.html

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


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

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

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

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


说到数据库,我认为不能不先谈数据结构。1996年,在我初入大学学习计算机编程时,当时的老师就告诉我们说:计算机程序=数据结构+算法。尽管现在的程序开发已由面向过程为主逐步过渡到面向对象为主,但我还是深深赞同8年前老师的告诉我们的公式:计算机程序=数据结构+算法。面向对象的程序开发,要做的第一件事就是,先分析整个程序中需处理的数据,从中提取出抽象模板,以这个抽象模板设计类,再在其中逐步添加处理其数据的函数(即算法),最后,再给类中的数据成员和函数划分访问权限,从而实现封装。

  数据库的最初雏形据说源自美国一个奶牛场的记账薄(纸质的,由此可见,数据库并不一定是存储在电脑里的数据^_^),里面记录的是该奶牛场的收支账目,程序员在将其整理、录入到电脑中时从中受到启发。当按照规定好的数据结构所采集到的数据量大到一定程度后,出于程序执行效率的考虑,程序员将其中的检索、更新维护等功能分离出来,做成单独调用的模块,这个模块后来就慢慢发展、演变成现在我们所接触到的数据库管理系统(DBMS)——程序开发中的一个重要分支。

  下面进入正题,首先按我个人所接触过的程序给数据库设计人员的功底分一下类:

  1、没有系统学习过数据结构的程序员。这类程序员的作品往往只是他们的即兴玩具,他们往往习惯只设计有限的几个表,实现某类功能的数据全部塞在一个表中,各表之间几乎毫无关联。网上不少的免费管理软件都是这样的东西,当程序功能有限,数据量不多的时候,其程序运行起来没有什么问题,但是如果用其管理比较重要的数据,风险性非常大。

  2、系统学习过数据结构,但是还没有开发过对程序效率要求比较高的管理软件的程序员。这类人多半刚从学校毕业不久,他们在设计数据库表结构时,严格按照教科书上的规定,死扣E-R图和3NF(别灰心,所有的数据库设计高手都是从这一步开始的)。他们的作品,对于一般的access型轻量级的管理软件,已经够用。但是一旦该系统需要添加新功能,原有的数据库表差不多得进行大换血。

  3、第二类程序员,在经历过数次程序效率的提升,以及功能升级的折腾后,终于升级成为数据库设计的老鸟,第一类程序员眼中的高人。这类程序员可以胜任二十个表以上的中型商业数据管理系统的开发工作。他们知道该在什么样的情况下保留一定的冗余数据来提高程序效率,而且其设计的数据库可拓展性较好,当用户需要添加新功能时,原有数据库表只需做少量修改即可。

  4、在经历过上十个类似数据库管理软件的重复设计后,第三类程序员中坚持下来没有转行,而是希望从中找出“偷懒”窍门的有心人会慢慢觉悟,从而完成量变到质变的转换。他们所设计的数据库表结构有一定的远见,能够预测到未来功能升级所需要的数据,从而预先留下伏笔。这类程序员目前大多晋级成数据挖掘方面的高级软件开发人员。

  5、第三类程序员或第四类程序员,在对现有的各家数据库管理系统的原理和开发都有一定的钻研后,要么在其基础上进行二次开发,要么自行开发一套有自主版权的通用数据库管理系统。

  我个人正处于第三类的末期,所以下面所列出的一些设计技巧只适合第二类和部分第三类数据库设计人员。同时,由于我很少碰到有兴趣在这方面深钻下去的同行,所以文中难免出现错误和遗漏,在此先行声明,欢迎大家指正,不要藏私哦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        不允许为空           商品类型标识,和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)(待续)