数据库设计文档

1、*中心开发部文档编 号2011-6-14版 本A1密级商密A项目名 称时代集团产品跟踪平台项目来 源时代集团DR-RD-020(V1.1) 数据 库设写:查:核:计说明书（内部资料请勿外传）期:期:期:准:期:版权所有不得复制时代集团产品跟踪平台数据库设计说明书 1引言1.1编写目的1.2术语表.1.3参考资料2 数据库环境说明 3 数据库的命名规则 4 逻辑设计5 物理设计5.1 表汇总5.2 表X : XXX表5.3 视图的设计 5.4 存储过程、函数及触发器的设计 6 安全性设计6.1 防止用户直接操作数据库的方法 6.2 用户帐号密码的加密方法 6.3 角色与权限 7 优化8数据库管理

2、与维护说明1引言1.1 编写目的本文档是时代集团产品跟踪平台概要设计文档的组成部分，编写数据库设计文档的目的是：明确数据库的表名、字段名 等数据信息，用来指导后期的数据库脚本的开发，本文档遵循SQL数据库设计和开发规范 本文档的读者对象是需求人员、系统设计人员、开发人员、测试人员。1.2 术语表序号术语或缩略语说明性定义1ESIDEn terStorageld 入库编号2ProldProductId产品编号3PMIDProductManagerld产品管理员编号1.3 参考资料资料名称作者文件编号、版本资料存放地点2数据库环境说明数据库 实例数据库系统数据库 部署环 境数据库设 计工具数据库存

3、 放位置说明3数据库的命名规则数据库名称：时代集团的英文名称time-group 表名：英文（表的用途）+下划线+英文字段名：相关属性的英文名4逻辑设计提示：数据库设计人员根据需求文档，创建与数据库相关的那部分实体关系图（ ERD 如果采用面向对象方法（00A）,这里实体相当于类（class ）。5物理设计提示：（1）主要是设计表结构。一般地，实体对应于表，实体的属性对应于表的列，实体之间的关系成为表的约束。逻辑设计中的实体大部分可以转换成物理设计中的表，但是它们并不一定是对应的。（2）对表结构进行规范化处理（第三范式）。5.1 表汇总表名功能说明Enter_storage产品入库（新产品的登

4、记、修改、查询）Exit_storage产品岀库（登记产品岀库信息）Seller_info销售商信息维护表（对销售商信息的添加、修改和查询）Product_info产品信息表（对新产品的发布，修改，查询等维护）Repair_info维修表（对已岀售产品的维修进行登记，修改）L管理员表（包含五种身份，销售人员，库存管理人员，售后服务人员，产品 信息管理人员，系统管理员）5.2 表:表的索引：索引是否建立要根据具体的业务需求来确定。允许为空：不填的表示为“是”。唯一：不填的表示为“是”。表的记录数和增长量：根据具体的业务需求确定。增长量应确定单位时间如果量大可以按每天，如果不大可以按每月。表字段的

5、区别度：主要是考虑到将来在此字段上建立索引类型选择时作为参考，当字段值唯一时可以不考虑，当字段值不唯一时，估算一个区别度，近似即可。 例如：如果一个表的 NAME字段有共2000个值,其中有1999个不同 值,1999/2000=0.99越接近1区别度越高,反之区别度越低。表的并发：根据具体的业务需求预测表的并发。1.表名En ter storage数据库用户库存管理人员主键ESID入库产品编号其他排序字段Product， date，num,storageManagerld索引字段序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别 度默认值约束条件/说明1ESIDintNY2Prod

6、uctVarchar(50)NN3dateSmallDateTimeNN4numVarchar(50)NN5storageMa nager IdVarchar(50)NYMysql脚本CREATE TABLE dbo.Enter_storage(ESID int IDENTITY(1,1) NOT NULL,product varchar(50) NOT NULL,date smalldatetime NOT NULL,num varchar(6) NOT NULL,storageManagerId nvarchar(50) NOT NULL,CONSTRAINT PK_addStorage

7、PRIMARY KEY CLUSTERED(ESID ASC)WITH (PADN DEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LO ON) ON PRIMARY)ON PRIMARYGOSET ANSI_PADDING OFFGO记录数增长量表的并发补充说明2.表名Exit storage数据库用户库存管理人员主键ExitStorageId出库产口口编号其他排序字段Product， date，num,storageManagerld索引字段序号字

8、段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1ExitStorageIdintNY2ProductVarchar(50)NN3dateSmallDateTimeNN4numVarchar(50)NN5storageMa nager IdVarchar(50)NYMysql脚本CREATE TABLE dbo.Exit_storage(ExitStorageldint IDENTITY(1,1) NOTNULL,product varchar(50) NOT NULL, date smalldatetime NOT NULL,n um varchar (6) NO

9、T NULL,CONSTRAINPK_subStorage PRIMARYKEYCLUSTERE (ExitStorageId ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPl =OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCK ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY )ON PRIMARYGOSET ANSI_PADDING OFFGO/*Object:Table dbo.E nter_storageScript Date: 01/17/2011 16:00:59 */SET

10、ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO记录数增长量表的并发补充说明3.表名Seller i nfo数据库用户销售商管理员主键n ame其他排序字段telephone ， address， product, stockNum， stockDate ， stockPrice索引字段序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1n ameVarchar(50)NY2telepho neVarchar(50)NN3addressSmallDateTimeNN4productVar

11、char(50)NN5stockNumVarchar(50)NN6stockDat eSmallDateTimeNN7stockPriVarchar(50)NNceMysql脚本CREATE TABLE dbo.Seller_i nfo( n ame varchar(50) NOT NULL, telephone varchar(20) NOT NULL, address varchar(50) NOT NULL, product varchar(50) NOT NULL, stockNum varchar (6) NOT NULL, stockDate smalldatetime NOT N

12、ULL, stockPrice varchar(10) NOT NULL,CONSTRAINT PK_maketer_l nfo PRIMARY KEY CLUSTERE (n ame ASC)WITH (PADN DEX = OFF, STATISTICS_NORECOMPUTE OFF, IGNORE_DUP_KEY OFF, ALLOW_ROW_LOCKS ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY)ON PRIMARYGOSET ANSI_PADDING OFFGO/* Object: Table dbo.Repair_i nfo ScriptDate:

13、 12/16/2010 09:59:13 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO记录数增长量表的并发补充说明4.表名Product i nfo数据库用户产品信息管理人员主键proId其他排序字段name price ，proDate, storage索引字段序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1proIdintNY2n ameVarchar(50)NN3priceVarchar(50)NN4proDateSmallDateTimeNN5storageV

14、archar(50)NYMysql脚本CREATE TABLE dbo.Product_i nfo( prold int IDENTITY(1,1) NOT NULL, n ame varchar(50) NOT NULL, price varchar(6) NOT NULL, proDate smalldatetime NOT NULL, storage varchar NOT NULL,CONSTRAINT PK_product_lnfoPRIMARY KEYCLUSTERED(proId ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPl =O

15、FF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCK ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY )ON PRIMARYGOSET ANSI_PADDING OFFGO/* Object: Table dbo.Logi n ScriptDate: 01/17/2011 16:00:59 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO记录数增长量表的并发补充说明5.表名Repair i nfo数据库用户售后服务人员主键repairId产品维修编号其

16、他排序字段repairName， repairtel ， product, customer， date， customertel索引字段序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1repairIdintNY2repairNameVarchar(50)NN3repairtelSmallDateTimeNN4productVarchar(50)NN5customerVarchar(50)NNdateSmallDateTimeNNcustomert elVarchar(50)NNMysql脚本CREATE TABLE dbo.Repair_i nfo( re

17、pairld int IDENTITY(1,1) NOT NULL, repairName nchar(10) NULL, repairtel nchar(10) NULL, product varchar(50) NOT NULL, customer varchar(20) NOT NULL, date smalldatetime NOT NULL, customertel nchar(10) NULL,CONSTRAINT PK_Table_1 PRIMARY KEY CLUSTERED (repairId ASC)WITH (PAD_INDEX = OFF, STATISTICS_NOR

18、ECOMPUTE OFF, IGNORE_DUP_KEY OFF, ALLOW_ROW_LOCKS ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY)ON PRIMARYGOSET ANSI_PADDING OFFGO/*Object:Tabledbo.Product_i nfoScript Date: 01/17/2011 16:00:59 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGO记录数增长量表的并发补充说明6.表名Log in数据库用户管理人员主键ruserId用户编号

19、其他排序字段password，userName tel, birthday ，registerTime ，type索引字段序号字段名称数据类型（精度范围）允许为空Y/N唯一Y/N区别度默认值约束条件/说明1userIdintNY2passwordVarchar(50)NN3userNameSmallDateTimeNN4telVarchar(50)NN5birthdayVarchar(50)NN6registerTi meSmallDateTimeNN7typeVarchar(50)NNMysql脚本CREATE TABLE dbo.Login(userId varchar(10) NOT N

20、ULL,password varchar(16) NOT NULL,userName varchar(20) NOT NULL,tel varchar(20) NOT NULL,birthday smalldatetime NOT NULL,registerTime smalldatetime NOT NULL,type nvarchar(50) NOT NULL,CONSTRAINT PK_manager_lnfo PRIMARY KEY CLUSTERED(userId ASC)WITH (PAD_INDEX= OFF, STATISTICS_NORECOMPUTE = OFF,IGNOR

21、E_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS ON PRIMARY)ON PRIMARYGOSET ANSI_PADDING OFFGO记录数增长量表的并发补充说明5.3 视图的设计根据XXX产品的概要设计文档来确定。视图的命名按照XX数据库设计规范中关于视图的命名规范命名 视图的设计应注意以下几点：1尽量减少列中使用的公式。2去掉所有不必要的列。3不要使同一个文档属于多个分类。4避免使用表单公式。5.4 存储过程、函数及触发器的设计存储过程及触发器的命名按照XX数据库设计规范中关于存储过程及触发器的命名 规范命名。存储过程：根

22、据具体得业务逻辑确定输入参数个数，类型，确定对哪几个表进行何种作。在定义存储过程时，要使用其完成单一、相对集中的任务，不要定义已由其他提供功能的过程。例如：不要定义强制数据完整性的过程（使用完整性约束)。函数 ：函数与存储过程非常相似，它也是存储在数据库中的对象。但是可以在 SQL 命令中使用函数。就好像建立自己的 substr 函数一样触发器 ：触发器是存储在数据库中的程序，它在某一特定事件发生时执行。这些程 序可以用PL/SQL和java语言编写，也可以用作c语言的调用，数据库允许用 户定义这些程序，然后在相关的表，视图或者数据库动作执行insert ，update 或 delete 语句

23、时执行。 1. 存储过程：CREATE PROCEDURE Enter_storage_GetMaxIdASDECLARE TempID int SELECT TempID = max( ESID )+ 1 FROM Enter_storage IF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Enter_storage_ExistsESID intASDECLARE TempID intSELECT TempID = count ( 1) FROM Enter_storage WHERE ESID =ESID IF Temp

24、ID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Enter_storage_ADD ESID int output ,product varchar ( 50 ),date smalldatetime ,num varchar ( 6),storageManagerId nvarchar ( 50)ASINSERT INTO Enter_storage ( product , date , num , storageManagerId ) VALUES (product , date , num, storageManagerId)SET ESID = I

25、DENTITY CREATE PROCEDURE Enter_storage_Update ESID int ,product varchar ( 50 ),date smalldatetime , num varchar ( 6), storageManagerId nvarchar ( 50)ASUPDATE Enter_storage SET product = product , date = date , num = num, storageManagerId storageManagerIdWHERE ESID =ESIDCREATE PROCEDURE Enter_storage

26、_DeleteESID intASDELETE Enter_storage WHERE ESID =ESIDCREATE PROCEDURE Enter_storage_GetModelESID intASSELECTESID , product , date , num, storageManagerIdFROM Enter_storage WHERE ESID =ESIDCREATE PROCEDURE Enter_storage_GetListASSELECTESID , product , date , num, storageManagerIdFROM Enter_storage C

27、REATE PROCEDURE Exit_storage_GetMaxId ASDECLARE TempID int Exit_storage WHERESELECT TempID = max( ExitStorageId )+ 1 FROM IF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Exit_storage_ExistsExitStorageId intASDECLARE TempID intSELECT TempID = count ( 1) FROM Exit_storage ExitStorageId =Exi

28、tStorageIdIF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Exit_storage_ADD ExitStorageId int output , product varchar ( 50 ), date smalldatetime , num varchar ( 6),storageManagerId nvarchar ( 50)ASINSERT INTO Exit_storage ( product , date , num , storageManagerId ) VALUES (product , date , num, st

29、orageManagerId)SET ExitStorageId = IDENTITYCREATE PROCEDURE Exit_storage_Update ExitStorageId int , product varchar ( 50 ), date smalldatetime ,num varchar ( 6), storageManagerId nvarchar ( 50)ASUPDATE Exit_storage SET product = product , date = date , num = num, storageManagerIdWHERE ExitStorageId=

30、 ExitStorageIdCREATE PROCEDURE Exit_storage_Delete ExitStorageId intASDELETE Exit_storage WHERE ExitStorageId= ExitStorageIdCREATE PROCEDURE Exit_storage_GetModel ExitStorageId intASSELECTExitStorageId , product , date , num, storageManagerIdFROM Exit_storage WHERE ExitStorageId= ExitStorageIdCREATE

31、 PROCEDURE Exit_storage_GetListASSELECTExitStorageId , product , date , num, storageManagerId FROM Exit_storage CREATE PROCEDURE Login_Exists userId varchar ( 10)ASDECLARE TempID intSELECT TempID = count ( 1) FROM Login WHERE userId IF TempID = 0RETURN 0ELSERETURN 1storageManagerId = userIdCREATE PR

32、OCEDURE Login_ADD userId varchar ( 10), password varchar ( 16), userName varchar ( 20),tel varchar ( 20),birthday smalldatetimeregisterTimesmalldatetime ,type nvarchar( 50)ASINSERT INTO Login( userId ,password, userName , tel , birthday , registerTime , type ) VALUES (userId , password, userName , t

33、el , birthday , registerTime , type)CREATE PROCEDURE Login_Update userId varchar ( 10), password varchar ( 16), userName varchar ( 20), tel varchar ( 20), birthday smalldatetime , registerTime smalldatetime , type nvarchar ( 50)ASUPDATE Login SET password = password , userName = userName , tel = tel

34、 , birthday = birthday , registerTime = registerTime , type = typeWHERE userId = userIdCREATE PROCEDURE Login_DeleteuserId varchar ( 10)ASDELETE Login WHERE userId = userIdCREATE PROCEDURE Login_GetModeluserId varchar ( 10)ASSELECTuserId , password , userName , tel ,FROM Login WHERE userId = userIdb

35、irthday, registerTime, typeCREATE PROCEDURE Login_GetListASSELECTuserId , password , userName , tel ,birthday, registerTime, typeFROM Login CREATE PROCEDURE Product_info_GetMaxId ASDECLARE TempID intSELECT TempID = max( proId )+1 FROM Product_infoIF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROC

36、EDURE Product_info_Exists proId intASDECLARE TempID intSELECT TempID = count ( 1) FROM Product_infoIF TempID = 0 RETURN 0 WHERE proId =proIdELSERETURN 1CREATE PROCEDURE Product_info_ADD proId int output ,name varchar ( 50 ),price varchar ( 6),proDate smalldatetime ,storage varchar ( 4) ASINSERT INTO

37、 Product_info ( name , price , proDate , storage ) VALUES (name, price , proDate , storage)SET proId = IDENTITYCREATE PROCEDURE Product_info_UpdateproId int ,name varchar ( 50 ),price varchar ( 6),proDate smalldatetime ,storage varchar ( 4)ASUPDATE Product_info SET name = name, price = price , proDa

38、te = WHERE proId =proIdCREATE PROCEDURE Product_info_DeleteproId intproDate , storage =storageASDELETE Product_info WHERE proId =proIdCREATE PROCEDURE Product_info_GetModelproId intASSELECTproId , name, price , proDate , storage FROM Product_info WHERE proId =proIdCREATE PROCEDURE Product_info_GetLi

39、stASSELECTproId , name, price , proDate , storage FROM Product_info CREATE PROCEDURE Repair_info_GetMaxId ASDECLARE TempID intSELECT TempID = max( repairId )+ 1 FROM Repair_info IF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Repair_info_ExistsrepairId intASDECLARE TempID intSELECT TempID

40、 = count ( 1) FROM Repair_info WHERE repairId = repairId IF TempID = 0RETURN 0ELSERETURN 1CREATE PROCEDURE Repair_info_ADD repairId int output , repairName nchar ( 10), repairtel nchar ( 10 ), product varchar ( 50 ), customer varchar ( 20), date smalldatetime , customertel nchar ( 10)AS, date , cust

41、omertelINSERT INTO Repair_info repairName , repairtel ) VALUES (, product , customerrepairName , repairtel, product , customer , date , customertelSET repairId= IDENTITYCREATE PROCEDURE Repair_info_Update repairId int , repairName nchar ( 10), repairtel nchar ( 10 ), product varchar ( 50 ), customer

42、 varchar ( 20),date smalldatetimecustomertelASnchar ( 10)UPDATE Repair_info SET repairName = product , customerWHERE repairId CREATE PROCEDURErepairName , repairtel = customer , =repairId Repair_info_Deletedate = = repairteldate , product =customertel = customertelrepairId intASDELETE Repair_info WH

43、ERE repairId =repairIdCREATE PROCEDURE Repair_info_GetModel repairId intASSELECTrepairId , repairName , repairtelFROM Repair_info WHERE repairId =repairId CREATE PROCEDURE Repair_info_GetList AS, product, customer, date, customertelSELECTrepairId , repairName , repairtel FROM Repair_info CREATE PROC

44、EDURE Seller_info_Exists, product, customer, date, customertelname varchar ( 50 )ASDECLARE TempID intSELECT TempID = count ( 1)FROM Seller_infoWHEREname =nameIF TempID = 0 RETURN 0ELSERETURN 1CREATE PROCEDURE Seller_info_ADDname varchar ( 50 ), telephone address product stockNumvarchar ( 20),( 50 ),

45、( 50 ),( 6),varcharvarcharvarcharstockDatestockPriceASsmalldatetimevarchar ( 10 ), product , stockNum ,stockDate , stockPriceINSERT INTO Seller_info ( name , telephone , address ) VALUES (name, telephone , address , product , stockNum , stockDate , stockPrice )CREATE PROCEDURE Seller_info_Updatename varchar ( 50 ),telephonevarchar( 20)addressvarchar( 50 ),productvarchar( 50 ),stockN