需求分析及逻辑设计

首先按照数据库设计的步骤呢,我们先来看一看需求说明,我们的主要设计呢,是一个电子商务网站,相关的设计说明呢,

是这样的,按下面的需求设计一个电子商务网站的数据库结构,首先第一点呢,是本网站只销售图书类的商品,其次这个

网站需要具有以下的功能,如用户登录啊,用户管理啊,商品展示,商品管理,供应商管理以及在线销售功能,虽然项目的

说明是很简单的,但是如果我们对上面的需求进行分析,可以发现,我们所要面对的工作呢,并不像表面上想的那么简单,

按照数据库设计的步骤,我们要对下面的需求进行详细的分析,在真实的环境中呢,我们的需求分析是需要和需求提出的人

反复的沟通的,可以把这个需求确定下来,但是这里我们就不会这么麻烦了,我们只需要自己对着需求进行分析就可以了,

实际上我们就是这个需求的分析和提出人

那么我现在来看一看,如何进行需求分析以及逻辑设计,我们首先来看看用户登录和管理功能需要注意写什么问题,

首先来说,第一点是用户必须进行注册,登陆后才能在网上交易,这里要我们能够通过唯一的标识来标识出每一个用户,

这个唯一的标识呢,也就是我们前面所提到的业务主键,在这里我们选择使用用户名来作为用户信息的业务主键,同时

这就要求我们在系统中用户名呢必须去保持唯一的

第二点就是为了保证用户的安全性呢,我们的系统要求,同一个时间呢,同一个用户,只能在同一个地点来进行登陆,

这个对我们数据库设计的影响呢,要求我们在进行用户表的设计中呢,记录用户是否登陆的状态信息,另外我们还

要求用户在注册时呢,必须提供一些信息,这些信息也会存入到我们的用户表中,下面就是用户信息表的一些属性,

首先红色标识的用户名呢,就是我们前面说的业务主键,而密码和手机号呢,也是用户首先必须要提供的,真实姓名和

出生日期是两个可选的属性,而注册日期和在线状态呢,是由我们系统来维护的两个属性,当然真实的这种网站呢,

用户信息表的属性呢,要多的多,我们举个例子来说呢,以上这些信息就已经足够了,接下来我们再来看看这个用户信息表

的逻辑事务中是否符合我们数据库设计的范式呢

由于这个表中只有一个业务主键,所以一定是符合第二范式要求的,另外也没有属性和我们的业务主键呢,

存在传递依赖的关系,所以也是符合第三范式的要求,现在看来呢,我们用户信息表的逻辑设计已经完成了,

下面我们要来看看其他的业务模块

那么我们就来看看商品展示模块,商品展示模块呢,主要用于前台浏览商品信息,和后台管理编辑商品信息,

和相关的一些模块,前面需求中,提到了我们这个网站呢,实际上只销售一种商品,实际上是图书,这个信息对于

我们设计商品,非常的有用,这就决定了我们商品信息表中商品信息呢,只要记录图书的一些商品信息就可以了,

另外呢除了商品信息外呢,还要记录商品分类,和商品供应商相关的信息,下面就是我们列子中商品模块中所要

记录的一些信息,大家可以看到,包括商品名称,分类名称,供应商名称,出版社名称,图书的价格,图书的描述,作者

等等,由于一本书还可以分属于多个分类,所以商品名称和分类两个属性能够作为我们的业务主键,才能唯一的确认

一本书,当然在真实的环境中,所需要要记录的信息呢,远远比上面的要多得多,如果上面的信息我们放在一个表中,

也会出现那个问题,首先这样的设计一定是不符合三大范式的要求,比如分类信息在这张表中就存在数据维护异常的,

如果我们新增加一个分类,在这个分类中还没有相关的图书的话,那么这个分类呢,就无法进行记录,所以我们要对这张

表进行拆分,使其满足数据库范式的要求,下面就是拆分后商品模块的相关属性信息,我们把商品信息表拆分为商品信息表,

商品分类以及商品分类对应关系三张表,其中商品信息表中的属性呢,包括商品名称,出版社名称,图书价格,还有图书的

描述,作者,其中图书名称就是我们的业务主键,而分类信息表中的主键呢,则有分类名称,和分类描述,其中分类名称

作为分类表中的主键,最后是一个商品和分类的对应关系表,这个表中只有两个属性,就是图书的名称和分类的名称,

并且这个表中的两个属性呢,成为了一个联合的业务属性,以上就是拆分后的三张表,这三张表已经符合三大范式的要求,

并且我们可以看到,这三张表中呢,存在数据冗余啊,以及数据维护异常的问题,这样我们就完成商品模块的设计,接下来我们

来看看电商模块

电商模块是用于记录供应商模块信息的,在我们的例子中呢,也就是出版社的信息,在供应商模块中,

来看一下,在这个模块中呢,可能会记录一下的一些属性,首先对于供应商信息,我们例子中只需要出版社的名称,

地址,电话,联系人,银行账号这样的信息就足够了,同时呢我们以出版社名称来作为供应商信息表的业务主键,

由于这个表只有一个主键,同时也不存在非主键对主键的这种部分依赖的关系,所以我们可以把这些信息全部存在

供应商这些表中,这样就已经符合我们的第三范式要求了,下面我们来看一下我们最后一个模块,也是最主要的一个模块,

在线销售模块的设计,在线销售模块呢,是电商网站的一个核心模块,我们所做的一切工作呢都是为了把我们网站的商品

销售出去,所以在线销售模块呢,遇到的内容也会非常的多,以我们的例子来说,至少要包括以下的属性,首先订单编号,

下单用户名,下单日期,订单的金额,订单的商品分类,订单商品名,订单商品的单价,订单商品数量,支付金额以及物流单号,

那这些属性中呢,可以使用订单编号来作为主键,由于在这里只有一个业务主键,所以是符合第二范式的,但是不难发现,

在这些属性中呢,订单商品的单价,订单商品的数量和订单编号,之间是存在传递依赖关系的,如果我们把这些信息放在一张

表中,显然是不符合第三范式要求的,同时这张表还会存在数据冗余的问题,所有的订单商品信息呢,和商品信息表中的商品

信息呢,都是冗余的,所以我们要对这些表进行拆分,使其符合第三范式的要求

首先我们把在线销售这个实体呢,拆分成一个订单表,其中包括订单编号,下单用户名,下单日期,和物流单号这些属性,

大家注意这里并没有物流价格,这也是因为物流价格我们可以通过商品单价乘以商品数量的方式来计算得出,所以严格来

说呢,也是一个冗余数据,另外还拆分了一个订单商品关联表,其中包括订单编号,订单分类,以及商品名,商品数量等属性,

大家可以看到,在这几个属性里面呢,订单编号是订单表的主键,而商品分类和商品名,商品分类关联表的主键,而唯一非主键

属性呢,商品数量,也和主键不存在传递依赖的关系,所以这样拆分之后呢,两张表就变成了符合范式要求的表,这样就完成了

我们演示系统数据库的逻辑设计,但是大家可以想象一下,这样的设计虽然是符合了范式化的要求,但是对于数据库处理来说,

是否就是高效的呢,前面我们说过,进行数据库设计时,不仅要考虑范式化设计的要求,同时还要考虑查询性能的要求,假设我们的

业务中需要进行以下的查询,我们来看一看,这个SQL语句要如何来写

假设我们现在想查询出,用户订单总金额,那么我们SQL要如何写呢,下面就是SQL的样子,首先这个SQL一共关联四张表,

从前面的内容大家可以知道,对于MYSQL来说呢,关联的表越多呢,性能就越差,虽然四张表并不太多,但是由于这样的查询

会非常的频繁,所以如果可以减少关联表的数量呢,可以对数据库性能起到不少的优化作用,另外再请大家考虑一个问题,

如果我们的商品价格变动了的话,会出现什么样的情况,因为现在只在商品表中记录了商品的价格,所以一旦商品的价格

发生了变化,那么我们的程序结果也发生了变化,这是所有人不想看到的结果,我们总是希望查询我们的历史订单的时候呢,

可以碰到当时购买的价格,而不是现在的价格,所以呢,从业务的角度来看呢,设计呢虽然是完全符合范式化表的要求,但是

可能还是存在问题的

我们下面再来看另外一个场景,我们现在要考虑以下问题,我们假设下单用户就是商品的收货人,

所以我们在进行发货前呢,一定要查询出每个订单的下单人的信息,而这些信息呢,全部记录在用户信息表中

这样的话呢,如果想要查询出我们需要的信息呢,那么我们就必须使用下面的SQL来写,大家看到呢,

这个SQL呢要比之前的SQL呢,还要在多关联一张表,那就是用户信息表,而且要在这些信息中呢,增加订单总额,

信息的话,那么这个SQL写起来就会更加的复杂,效率也会更低,而我们所举的两个SQL呢,都是我们系统中经常要

查询的SQL,所以这样看来呢,完全符合范式化的表结构呢,并不能得到良好的SQL查询性能,所以我们要根据我们的业务中的

所使用的SQL查询呢,来对以上的逻辑设计呢,进行一些优化,要进行一些反范式化的设计,下面我们就来看如何对这些表进行

反范式化设计

要对数据库进行反范式化设计,先要了解什么是反范式化,反范式化实际上是针对范式化而言的,我们在前面

介绍了数据库设计的几种范式,那么在范式化的数据库中,每个数据会仅会出现一次,那么所谓的反范式化呢,

是为了提高数据库的查询性能,和读取效率,考虑而适当的对数据库设计范式的要求进行违反,同时允许存在少量的

数据冗余,换句话来说呢,反范式化的目的呢,使用我们的存储空间,来换取更少的时间,那么这样什么是反范式化,

我们就可以着手对我们之前设计的,完全符合范式化逻辑结构来进行反范式化设计了

适当的增加一些数据冗余,以获得更好的查询性能,下面是需要进行反范式化设计的表,首先我们先来看看,

商品信息表来进行反范式化设计,之前我们所设计的商品信息表是这样的,三张表,商品信息表,分类信息表,

以及商品分类关系表,那么从刚才的查询中呢,可以看到,如果要查询出一个商品的信息,我们必须要关联出这

三张表,而商品分类和商品名称这两列呢,由于标识了一个具体的分类,所以几乎每次查询呢,都要重复用到,

所以我们对商品信息表进行反范式化呢,就会增加分类名称这一列,现在在商品信息表中呢,包含了商品名称和

分类名称,这样处理以后呢,商品信息表中增加了一些冗余数据,为了避免操作异常呢,同时还会暴露分类信息表,

这样对于新增的无图书分类呢,可以在分类信息表中进行记录,所以也就不会造成丢失信息分类的这种情况,这样我们

就完成了商品信息表,相关的反范式化设计,大家可以看到,这时呢,如果我们想要查询商品信息的话,我们只需要从商品

信息表中就可以得到了,减少了两个表的关联

下面我们来看看根据之前的查询,所看出来的另一个需要做反范式化优化的表,也就是销售相关的一些表,

原本呢我们按照范式化的设计呢,订单表是如下的,包括订单表和订单商品关联表,之前我们在写查询语句的时候呢,

发现,和订单表一起查询的用户的相关信息呢,用户名,另一个就是用户下单的手机号,原来的表设计呢,如果想要获取

用户名和其对应的手机号呢,就必须要关联用户表,来进行查询,一旦用户在维护信息中,改变了手机号,订单信息的手机号

也就会发生变化,这在业务上也是不允许的,因为不可能我们下单时写手机号,而我们配送时又是另一个手机号,我们很难

联系到买这个的人,我们就要把我们用户的手机号记录到我们的订单表中,另一个增加到订单表中的列呢,就是订单金额列,

这一列也是经常会被查询到的,原来我们是需要通过订单商品关联表和订单表中的信息呢,才能计算这一列的值,而如果订单

表中的信息如果发生变动,我们的订单金额也会随之变动,所以为了避免这种情况的发生呢,我们把这一列呢在订单表中也进行了

冗余,那么反范式化后的订单表呢,就变成了现在的这种结构,包括了订单编号,下单用户名,手机号,以及订单的金额这些信息,

我们再来看一下订单商品关系表,由于之前说的,商品信息表中的商品价格变动呢,同样也会对我们的商品订单商品关联表中的

信息进行影响,所以我们在订单商品关联表中呢,同样冗余了单价,这样订单商品关联表呢,就变成了这种结构,我们把商品单价在

在订单商品关联表中进行了冗余,我们上面对表进行了反范式化的优化,优化之后呢,我们对刚才所提出的需求来进行一些处理

看一下现在的SQL语句变成了什么样子,同样我们还是来看一下反范式化后,查询出每一个用户订单总金额的需求

变成什么样子了,由于我们已经把订单金额,在订单表中进行了冗余,这个查询就变得非常的简单,大家可以看到,

现在我们只要对一个表进行汇总操作,就可以拿到之前关联四张表才能达到的目的,这样就大大提高SQL的执行效率,

可见呢,我们的反范式化优化呢,是十分有效地,下面我们再来看看另外一个查询需求的情况

编写SQL查询出下单用户和订单详情信息,那么这个SQL呢,之前我们关联了5张表才能够实现,而现在我们对订单表,

和订单商品表的关联呢,已经商品信息表进行反范式化操作之后,在进行同样的查询呢,MYSQL就会变得非常的简单,

由于我们对于用户的手机号,商品价格的订单表和订单商品表中呢,进行了冗余处理,所以现在的SQL就变成了现在这个

样子,这个SQL就是经过反范式化处理以后的查询的SQL,和之前的商品经过反范式化处理以后的SQL呢,同样变得高效了很多,

可见我们在进行数据库设计时呢,并不能完全的按照范式化的要求来进行设计

同时还必须要考虑我们以后要如何使用这些表,而在范式化设计基础之上呢,根据应用中查询的SQL呢,

表设计要进行反范式化的优化,这样可以设计出最符合性能和业务要求的数据库的结构,从上面的例子中呢,

我们可以看出,数据库的设计呢,对数据库性能确实是有很大的影响,以上面的两个SQL为例,如果我们的设计呢,

是完全按照范式化设计来进行的,那么无论后期如何对SQL进行优化呢,都达不到对数据库结构进行反范式化之后

的查询效率,不过完全的反范式化设计呢,也行不通的,因为那样会造成大量的数据冗余,和数据的维护异常,下面我们

来总结一下,范式化设计和反范式化设计的优缺点,这样大家在进行数据库设计时呢,就知道如何选择对表进行范式化

设计和反范式化设计了