网上购物数据库设计

一、概述1.1需求背景伴着电子时代的迅猛发展和人民物质生活的水平的提高，越来越多的电子购物浪潮也汹涌而来。

我们容身在这个信息化的大时代，网购也就成了许多人生活中必不可少的一部分，足不出户的便捷式购物与传统的购物方式大相径庭，人们在享受到方便、实惠的同时也不必担忧安全的问题，既方便了自身也推动着国家经济的发展。

电子商务网络购物平台，无疑是这个时代的进步。

1.2编写目的数据库设计说明书是数据库设计的必要部分，对设计中的数据库的所有标识、逻辑结构和物理结构作出具体的设计规定。

本数据库的设计说明书编写的目的是对网上购物系统设计的说明，明确系统中的各项功能与非功能的需求，从而做出系统的数据流图以及实体联系图。

作为系统的基准文档，为以后的开发和维护提供依据。

1.3软件定义Myeclipse 10.0:一个非常优秀的用于开发Java、J2EE的Eclipse插件集合，Myeclipse功能非常强大，支持也十分广泛，尤其对各种开源产品的支持也不错。

Apache Tomcat 6.0:是一个开放源代码、运行servlet和JSP Web应用软件容器。

Microsoft SQL Server 2005:Structured Query Language1.4开发环境本电子商务网络购物平台的开发环境是Windows 7、Myeclipse10.0、Apache Tomcat6.0，数据库环境是Microsoft SQL Server 2005。

二、需求分析2.1问题陈述设计网络购物系统的数据库。

2.1需完成的功能客户功能：(1) 游客可以查看商品信息，浏览网站信息，经过注册可以成为注册客户。

(2) 注册客户：注册、客户信息查看和修改。

客户登录、确认客户信息，显示客户信息。

商品信息浏览、购物车管理、商品查找、订单查询以及商品评论。

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

商家功能：商品的增删改。

订单处理、订单配送。

客户注册后，登录到电子商务网站，进入购物流程。

客户在浏览所有商品信息后，可以查看每个商品的详细信息，选择购买或加入购物车，购物车内可以随意增删商品，修改商品数量，并同时统计购物车内商品总额。

客户确认购物车内的商品，选中购买，即可生成订单。

可以查看订单状态，“0表示”商品未发出，“1”表示商品已发出。

客户分为普通客户和VIP客户，依据购买商品的金额和数量，自动升级。

不同的VIP等级享有不同的折扣。

2.1数据流图网络购物系统总数据流图如下图1：网络购物系统总数据流图2.1数据字典UserID 客户ID BigInt Not null 主键UserName 客户名Varchar(30) Not null RealName 客户真实姓名Varchar(30) Not null UserPassword 客户密码Varchar(30) Not nullEmail 客户邮箱Varchar(30) Not nullPhone 联系电话Varchar(30) Not nullID 身份证号Varchar(30) Not nullQuestion 密码问题Varchar(30) NullResult 答案Varchar(30) NullRegTime 注册时间Datetime Not nullGradeID 会员等级BigInt Not null AddressID 地址Int Not null表1：客户表字段信息GoodsID 商品号BigInt Not null 主键GName 商品名称Varchar(30) Not nullGPrice 商品价格Smallmoney Not nullGTypeID 商品类别BigInt Not nullGIntro 商品描述Varchar(200) NullGImage 商品图片路径Varchar(100) NullGCount 商品计数Int Not nullGSail 商品销量BigInt Not nullGWeight 商品重量BigInt Null GDateTime 上架日期Datetime Not nullGSize 商品规格Varchar(100) Not null表2：商品表字段信息TypeID 商品类别号BigInt Not null 主键TypeNam类别描述Varchar(50) Not nulle表3：商品类别表字段信息ProducerID 商家号BigInt Not null 主键ProName 商家名Varchar(30) Not null ProAddress 商家地址Varchar(50) Not null ProPhone 商家电话Varchar(30) Not null表4：商家表字段信息订单表数据字典：SubID 订单号Varchar(30) Not null 主键UserID 客户号BigInt Not nullGoodsID 商品号BigInt Not null RAddress 送货地址Varchar(50) Not nullSpDate 订购日期Datetime Not nullSpCount 订购数量Int Not nullGPrice 单价Smallmoney Not null ReceiptID 发票号BigInt Not nullSpPrice 订单金额Money Not null SendDate 发货时间Datetime Not nullPayment 付款方式Varchar(20) Not null SendType 运送方式Varchar(20) Not null SendPrice 邮费Smallmoney Not nullState 订单状态Int Not null表5：订单表字段信息CarID 购物车ID BigInt Not null 主键UserID 客户ID Varchar(10) Not nullGoodsID 商品ID Varchar(20) Not null OrderTime 订购时间Datetime Not nullOrderCount 订购数量Int Not null GoodsPrice 单价Smallmoney Not null UserCheck 用户确认Int Not null OrderPrice 购物总额Money Not null表6：购物车表字段信息GradeID 会员等级ID BigInt Not null 主键等级分类Int Not null GradeType表7：会员等级表字段信息CommentID 评论ID BigInt Not null 主键Content1 内容Varchar(200) Not nullTitle 标题Varchar(20) Not nullUserID 客户ID BigInt Not null ProducerID 商家ID BigInt Not null CommentTime 评论时间Datetime Not null表8：评论表字段信息SupplyID 供应ID BigInt Not null 主键RealStock 实际存货Int Not nullMinStock 最低存货Int Not null GoodsID 商品ID BigInt Not null ProducerID 商家ID BigInt Not null表9：供应表字段信息ReceiptID 发票ID BigInt Not null 主键SubID 订单号BigInt Not null表10：发票表字段信息AddressID 地址ID Int Not null 主键Address 地址Varchar(50) Not nullUserID 客户ID BigInt Not null表11：发票表字段信息三、数据库概念结构设计3.1 E-R模型图的设计（1）寻找实体集客户VIP会员商品商家商品类别地址（2）寻找实体间联系客户与商品之间多对多联系：订单，评论1>客户与VIP会员之间多对一联系：属于2>会员等级与订单细节一对多：参照13>订单细节与商品多对一：参照24>订单细节预订单多对一：组成客户与商品之间一对多联系：购物车1>客户与购物车一对一：拥有2>购物车与商品一对多：存放3>购物车与订单细节一对多：结算商家与商品之间多对多联系：供应商品与商品类别之间多对一联系：分类客户与地址之间一对多联系：所在（3）确定实体的属性客户：客户ID 客户名客户真实姓名客户密码客户邮箱联系电话身份证号密码问题答案注册时间会员等级IDVIP会员：会员等级ID 等级分类商品：商品号商品名称商品价格商品类别商品描述商品图片路径商品计数商品销量商品重量上架日期商品规格商家：商家号商家名商家地址商家电话地址：地址号地址名称商品类别：商品类别号类别描述（3）确定联系的属性订单：订单号客户号商品号送货地址订购日期订购数量单价发票号订单金额发货时间付款方式运送方式邮费订单状态购物车：购物车ID 客户ID 商品ID 订购时间订购数量单价用户确认供应：供应ID实际存货最低存货商品ID 商家ID分类：商品号商品分类客户类型属于：客户号客户类型评论：评论ID 内容标题客户ID 商家ID 评论时间客户地址所在:客户号客户地址（4）画出E-R图图2：客户与商品实体间的详细联系E-R图图3：网络购物系统E-R图四、数据库逻辑结构设计4.1 E-R模型图转化为关系表（1）实体转化为单独的一张表客户VIP会员商品商家商品类别地址（2）多对多联系转化为单独的一张关系表购物车订单评论供应（3）其他一对多的联系通过在多的一方增加属性来实现客户表与会员等级：在客户表中增加会员等级ID商品与商品类型：在商品中增加类型号4.2 描述基本表的关系E-R图转化为关系模型，关系的码用下划线标出。

此为客户实体对应的关系模式：客户(客户号，密码，会员等级号，…)此为VIP会员实体对应的关系模式：VIP会员(会员等级号，等级类型)此为商品实体对应的关系模式：商品(商品号，描述，商品分类，商品名，…)此为商家实体对应的关系模式：商家(商家号，商家名，商家电话，…)此为商品类别实体对应的关系模式：商品类别(商品类别号，商品类别)此为联系“购物车”所对应的关系模式：购物车(购物车号，客户号，商品号，…)此为联系“订单”所对应的关系模式：订单(订单号，客户号，商品号，…)此为联系“评论”所对应的关系模式：评论(评论号，内容，客户号，…)此为联系“供应”所对应的关系模式：供应(供应号，实际存货，最少存货，…)4.3 所有关系表达到BCNF范式4.4 定义视图（1）订单表中只关联客户、商品的ID，而最终客户核对的应该是包含客户真实姓名在内的客户必要信息以及包含商品名称的商品信息，分别建立两个视图：1>参照商品的订单细节create view View_goodsasselect * from tb_goodswhere GoodsID='201201'2>参照客户信息的订单细节create view View_userasselect * from tb_userwhere UserID='201'4.5 定义索引在经常查询的列上（主键）建立聚簇索引如：在商品信息表上建立索引create cluster index Index_ GoodsIDontb_goods(GoodsID)4.6 定义触发器（1）注册客户自动升级VIP--创建触发器tr_grade，实现注册客户VIP自动升级Create Trigger tr_grade ON tb_subFor updateAsIF Update(SpPrice)BeginDeclare @price moneySelect @price=SpPrice From insertedIf @price>=30000Update tb_user set GradeID=4ElseIf @price>=20000Update tb_user set GradeID=3ElseIf @price>=10000Update tb_user set GradeID=2ElseIf @price>=0Update tb_user set GradeID=1End（2）更新订单，订单金额是商品价格总和--创建一个更新触发器，及时更新订单Create Trigger tr_update On tb_sub For Update AsIf Update (SpCount) Or Update (GPrice)BeginDeclare @orderNo Bigint,@productNo BigintDeclare cur_order Cursor ForSelect SubID,GoodsID From DeletedOpen cur_orderBegin TransactionFetch cur_order into @orderNo,@productNoWhile (@@fetch_status=0)BeginUpdate tb_subSettb_sub.SpPrice=tb_sub.SpPrice-D.SpCount*D.GPrice+I.SpCount*I.GPriceFrom inserted I,deleted DWhere tb_sub.subID=I.subID And I.subID=D.subIDAnd tb_sub.subID=@orderNo And I.GoodsID=D.GoodsIDAnd I.GoodsID=@productNoFetch cur_order into @orderNo,@productNo EndCommit tranClose cur_orderDeallocate cur_orderEnd（3）更新购物车，选中商品更新商品总额--更新购物车，选中商品更新商品总额Create Trigger tr_car On tb_car For Update AsIf Update (OrderCount) Or Update (GoodsPrice)BeginDeclare @CarID Bigint,@GoodsID BigintDeclare cur_car Cursor ForSelect CarID,GoodsID From DeletedOpen cur_carBegin TransactionFetch cur_car into @CarID,@GoodsIDWhile (@@fetch_status=0)BeginUpdate tb_carSettb_car.OrderPrice=tb_car.OrderPrice-D.OrderCount*D.GoodsPrice+I.OrderCount*I.GoodsPriceFrom inserted I,deleted DWhere tb_car.CarID=I.CarID And I.CarID=D.CarIDAnd tb_car.CarID=@CarID And I.GoodsID=D.GoodsIDAnd I.GoodsID=@GoodsIDFetch cur_car into @CarID,@GoodsIDEndCommit tranClose cur_carDeallocate cur_carEnd（4）创建触发器只允许注册会员在网上下订单--创建一个触发器tr_sub，只允许注册会员在网上提交订单Create Trigger tr_sub on tb_subFor insertAsIf Not Exists(select * From insertedWhere UserID in(select tb_erID from tb_user)) BeginRaiserror('提交订单前请先注册！',16,1)Rollback TransactionEnd4.7 定义约束（1）确定实体完整性约束主键约束（2）参照完整性约束--为tb_user表添加参照性约束alter table tb_userwith nocheckadd constraint fk_user foreign key(GradeID) references tb_grade(GradeID)--为tb_goods表添加参照性约束alter table tb_goodswith nocheckadd constraint fk_goods foreign key(GTypeID) references tb_type(TypeID)--为tb_address增加外键约束alter table tb_addresswith nocheckadd constraint fk_address foreign key(UserID) references tb_user(UserID)（3）用户自定义完整性约束1>客户表密码不能少于6位，且不能与客户号相同Alter table tb_userAdd constraint Ck_passCheck (6<=len(Userpassword) and Userpassword<>UserID)2>电子信箱格式为***********格式Alter table tb_userAdd constraint Ck_emailCheck (Email like '%@qq.%')3>订单号共12位，前8位是订货日期，后4位是流水号，格式为“2013 12150001”3>订货时间早于发货时间alter table tb_subwith nocheckadd constraint ck_sub check(SpDate<SendDate)5>发票号码唯一alter table tb_receiptadd constraint un_receipt unique(ReceiptID)6>订单状态取值为“0”或“1”alter table tb_subwith nocheckadd constraint ck_state check(State in (0,1))7>身份证号必须为16或者18位Alter table tb_userAdd constraint Ck_idCheck (ID in (16,18))8>……五、结束语一个软件最重要的不是前台页面，而是软件的生存期，即实用性的长远，而后者则主要取决于后台的维护。