网上购物系统-关系型数据库设计举例

网上购物系统

1.系统需求分析

网上购物系统分前台功能和后台功能两大部分。前台主要供用户浏览和购买商品，后台主要供管理员使用，管理员可以对商品信息、订单信息及网站的新闻、公告进行管理。

1.1前台功能分析

网上购物系统前台的用户共分两类：一类是注册用户(正式用户)，这类用户有基本的信息，可以对自己的信息进行查看与修改，可以随时实现网上购物。当用户在网站所购商品总金额达一定数量，可以根据所购商品总金额数量不同自动升级成为不同等级的VIP会员，并享受不同折扣优惠；另一类用户是游客（未注册用户），他们只能查看、浏览网站信息，可以把商品加入购物车或收藏夹，但不能实现购买。

游客：可以查看商品信息、浏览网站信息，可以把商品加入购物车或收藏夹，但不能实现购买。经过注册可以成为注册用户。

注册用户：

登录后对可以对个人信息进行查看和修改。

商品信息浏览、商品查找、商品评论和建议。

注册用户不仅可以对网站商品进行浏览和查找外，还可以对商品进行评论、向管理员发送消息提出自己的建议。

选购商品加入购物车或收藏夹、对购物车或收藏夹信息进行管理。

用户注册后，登陆到电子商务网站中，可以进入购物流程。用户在浏览商品后，可将满意商品放入购物车或收藏夹，购物车内可以随意增加、删除商品，修改商品数量，并同时统计购物车内商品总额。用户可对购物车的商品进行修改或删除，或对收藏夹中商品进行删除。

结帐、确认订单、订单状态查询、历史订单查询。

用户确认购物车内信息无误，即可生成订单。在生成订单时，必须填写一张配送单。配送单默认为用户注册时的基本信息，当然配送地址可由用户修改为合适的收货地址，支付方式也可根据提示由用户自定。下单后，用户可以在前台页面查看订单状态，订单状态可以是“末处理”，“已发货”，“已付款”。

5、发表及回复留言。

为了加强注册用户之间的交流，网站还提供了论坛功能，注册用户可以在某一个论坛版块中发贴，也可以回复别人的贴子。

1.2后台功能分析

网上购物系统后台主要是供管理员使用的，管理员可对商品的一级分类信息、二级分类信息、商品信息进行添加、删除、查询及修改；对用户订单进行处理；管理用户在论坛中发表的留言，删除不健康及不利于网站的留言；回复用户发送的消息；对网站的新闻、公告进行管理。

管理员也可以具有不同的权限分为超级管理员和普通管理员，普通管理员具有以上权限，超级管理员除了可以具有以上所有功能外，还可以添加、删除普通管理员。

2.数据库设计

2.1数据库概念结构设计

对网上购物系统进行分析之后，抽象出有关的数据，按照现实世界的事物能作为属性对待的，尽量作为属性对待的原则。作为“属性”，不能再具有需要描述的性质，“属性”必须是不可分的数据项，不能包含其它的属性；“属性”不能与其它实体具有联系，E-R图中所表示的联系是实体与实体的联系。依照以上准则，可以确定哪些为实体，哪些为属性，每个实体具有哪些属性，实体之间存在何种联系。经分析之后，该系统中包含的实体以及实体之间的联系如下所示：

实体：一级分类、二级分类、商品、用户、订单、订单明细、送货地址、论坛版块、留言、VIP用户等级、管理员、新闻、公告。

（注：因为订单中包含若干订单明细，根据“属性”是不可分的数据项，所以要反“订单明细”上升为实体。同样的道理，一个用户对应多个送货地址，所以“送货地址”也要上升为实体。另外，因为用户等级要与优惠政策挂钩，所以用户等级也要定义为实体，即VIP等级。）

实体间存在的联系：

一级分类和二级分类之间存在一对多的子类联系

二级分类与商品之间存在一对多的分类联系

商品与注册用户之间存在三个多对多的联系：收藏、选购和评论

用户与订单之间存在一对多的下单联系

订单与送货地址之间存在多对一的对应2联系

用户与送货地址之间存在一对多的对应1联系

订单与订单明细之间存在1对多的包含1联系

订单明细与商品之间存在多对1的包含2联系

论坛版块与留言之间存在一对多的归属联系

用户与留言之间存在一对多的发贴联系和多对多的回复联系

用户与VIP等级之间存在多对一的属于联系

用户与管理员之间存在多对多的消息联系

实体的属性：

一级分类：一级分类号，一级分类名

二级分类：二级分类号，二级分类名，一级分类号

商品：商品号，商品名称，所属分类，颜色，大小，商品描述，单价，库存量，已售出量，其他

用户：用户号，用户名，密码，真实姓名，性别，出生年月，邮箱，电话，单位，城市，地址，注册时间，积分，用户等级，安全问题，安全答案

（注：积分属性用来记录该用户的总订单金额，一元为一分；为了让忘记密码的用户可以从邮箱中找回密码，设置安全问题，安全答案两个属性）

VIP用户等级：用户等级，用户折扣，积分下限，积分上限

（注：用户等级分为四等，根据等级分别享有10折（即普通客户）、9折、8.5折、7.5折优惠。）

订单：订单号，用户号，订货时间，收货人，收货人电话，送货方式，送货地址，邮编，订单总金额，发货时间，订单状态

订单明细：订单明细号，订单号，商品号，数量，单价，折扣价

送货地址：用户号，地址，邮编，电话。

论坛版块：版块号，版块名称，版主

留言：留言号，用户号，标题，内容，时间，回复数量，查看数量，最后回复人，是否置顶，是否精华，所属版块。

管理员：管理员ID，密码，权限

新闻：新闻号，标题，内容，时间

公告：公告号，标题，内容，时间

加了下划线的属性组为实体的码。

联系的属性：

选购：用户号，商品号，数量，单价，折扣价，选购时间

收藏：用户号，商品号，收藏时间

评论：用户号，商品号，标题，评论内容，评论时间

消息：消息发送者，消息接收者，内容，时间，状态

回复：用户号，留言号，主题，内容，回复时间

系统E-R如下图所示：

2.2数据库逻辑结构设计

2.2.1关系模型的设计

根据系统E-R图，把实体与实体之间的联系转换成关系模型，E-R图中的每个实体转换成一个关系模型，实体之间一对多的联系合并到多方实体对应的关系模型中，把一方的码与联系的属性纳入到多方实体对应的关系模型中，为实体之间多对多的联系创建一个新的关系模型，它包含双方的码以及联系的属性。具有相同码的关系模型有些情况下可以考虑把它们合并。在转换过程中应该按照关系规范化的理论，对关系模型进行优化，减少冗余和数据操作异常，提高查询速度，在性能与范式之间作出权衡，一般所设计出的关系数据库达到3NF就基本符合要求。按照以上原则，我们可以把系统E-R图中实体及实体之间的联系转换成关系模型，如下所示：

一级分类（一级分类号，一级分类名）

二级分类（二级分类号，二级分类名，一级分类号） //已经包含了一级分类与二级分类之间的一对多联系

商品（商品号，商品名称，所属分类，颜色，大小，商品描述，单价，库存量，已售出量，其他） //已经包含了二级分类与商品之间的一对多联系

用户（用户号，用户名，密码，真实姓名，性别，出生年月，邮箱，电话，单位，城市，地址，注册时间，积分，用户等级，安全问题，安全答案） //已经包含了VIP用户等级与用户之间的一对多联系

VIP用户等级（用户等级，用户折扣，积分下限，积分上限）

订单（订单号，用户号，订货时间，收货人，收货人电话，送货方式，送货地址，邮编，订单总金额，发货时间，订单状态） //已经包含了用户与订单之间、送货地址与订单之间的一对多联系

订单明细（订单明细号，订单号，商品号，数量，单价，折扣价，订货时间）//已经包含了订单与订单明细之间、商品与订单明细之间的一对多联系

送货地址（用户号，地址，邮编，电话）

论坛版块（版块号，版块名称，版主）

留言（留言号，用户号，标题，内容，时间，回复数量，查看数量，最后回复人，是否置顶，是否精华，所属版块）//已经包含了论坛版块与留言之间的一对多联系

管理员（管理员ID，密码，权限）

新闻（新闻号，标题，内容，时间）

公告（公告号，标题，内容，时间）

收藏夹（用户号，商品号，收藏时间） // 对应收藏联系

购物车（用户号，商品号，数量，折扣价，选购时间） //对应选购联系

评论（用户号，商品号，标题，评论内容，评论时间） //对应评论联系

消息（消息发送者，消息接收者，内容，时间，状态） //对应消息联系

回复（用户号，留言号，主题，内容，回复时间）//对应回复联系

各关系模型中加下划线的属性组为码。为了方便起见，也可为某些表添加一个具有唯一标识的属性作为码，如：

回复（回复ID，用户号，留言号，主题，内容，回复时间）//添加回复ID作为码

消息（消息ID，消息发送者，消息接者，内容，时间，状态）//添加消息ID作为码

评论（评论ID，用户号，商品号，标题，评论内容，评论时间）//添加评论ID作为码

购物车（购物车ID，用户号，商品号，数量，折扣价，选购时间）//添加购物车ID作为码

收藏夹（收藏ID，用户号，商品号，收藏时间）//添加收藏ID作为码

2.2.2约束的说明

在SQL Server2000中或2005中创建网上购物系统的数据库NetShop，建立各个表及有关的完整性约束，建议在SQL Server中创建的表名及表中的字段名都使用英文，且做到见名知意。

对约束的要求：

（1）实体完整性约束：为各个表创建主键约束，以实现实体完整性约束。

（2）参照完整性约束：为各个表创建外键约束或创建触发器来实现参照完整性约束。

（3）用户自定义完整性约束： 根据本系统对数据的要求，为表中的某些列实现以下自定义完整性约束。有的可以通过在定义表时指定数据类型，长度来实现，有的通过核查约束来实现，有的通过默认值、是否允许空值来实现，有的通过触发器来实现。

数据类型约束

数据长度、精度约束

取值范围约束

用户表中密码至少6位，并不能与用户号同名。

性别只能取‘男’或‘女’

订单表中订单号共12位，前8位是订货日期，后4位是流水号，格式为“200707010001”。

订货时间要早于发货时间。

订单状态取值为“末处理”，“已发货”，“已付款”。

订单金额必须是明细表中同一订单所有商品总价格之和（触发器完成）。

其他：如默认值、空值等等

还有其他约束吗？

2.2.3检查是否支持复杂应用

1、当用户订购某一商品时，要根据订货数量与商品库存量比较的结果选择是否能够正常订购，或不能，提示有关信息，若能，还应即时修改商品的库存量与已售出量。

可以创建一个触发器，当用户添加订购信息时能做出相应处理。

2、如何使用户在完成一定的订购金额或数量后自动VIP用户？

普通用户变成VIP用户主要看用户累计完成的订购金额或数量，如果是达到一定要求，也必须由触发器便自动将用户升级为不同的VIP用户。

3、订单上的订单金额是如何取得其值？

在一个订单上可能有多种商品，因此，订单金额是一个计算列，不能让用户输入，可以设置触发器来完成统计功能。

查看某个用户的所有订单。

可以创建一个带一个输入参数的存储过程来实现。

查看某张订单中的订单明细。

可以创建一个带一个输入参数的存储过程来实现。

进一步的思考

如何实现商品销售排行榜？

如何确认畅销商品、滞销商品？

这些关系表达到了第几范式？

2.3数据库物理结构设计

在经常查询，但更新较少的列上建立索引以提高查询速度。