数据库设计文档模板27847

1、*中心开发部文档编号2021-6-14版本A1密级商密A工程名称时代集团产品跟踪平台工程来源时代集团DR-RD-020(V1.1)数据库设计说明书内部资料请勿外传期: 写：日期: 査：n 期:核：H 期:H版权所有不得复制时代集团产品跟踪平台 错误!未定义书签。数据库设计说明书 11 引言 21.1 编写目的 21.2 术语表 21.3 参考资料 32 数据库环境说明 33 数据库的命名规那么 34 逻辑设计 35 物理设计 45.1 表汇总 55.2 表X : XXX 表 55.3 视图的设计 115.4 存储过程、函数及触发器的设计 126 平安性设计 236.1 防止用户直接操作数据库的

2、方法 236.2 用户帐号密码的加密方法 236.3 角色与权限 237 优化 248 数据库管理与维护说明 241引言1.1 编写目的本文档是时代集团产品跟踪平台概要设计文档的组成局部， 编写数据库设计文档的目的是：明确数据库的表名、 字段名 等数据信息， 用来指导后期的数据库脚本的开发，本文档遵循 ?SQL数据库设计和开发标准? 本文档的读者对象是需求人员、系统设计人员、开发人员、测试人员。1.2 术语表序号术语或缩略语说明性定义1ESIDEn terStorageld 入库编号2ProldProductId产品编号3PMIDProductMa nagerld产品管理员编号1.3参考资料资

3、料名称作者文件编号、版本资料存放地点2数据库环境说明数据库实 例数据库系统数据库部 署环境数据库设计工具数据库存放位 置说明3数据库的命名规那么数据库名称：时代集团的英文名称time-group表名：英文表的用途+下划线+英文 字段名：相关属性的英文名4逻辑设计提示：数据库设计人员根据需求文档，创立与数据库相关的那局部实体关系图 ERD如果采用面向对象方法OOAD ，这里实体相当于类class 。产品信息r产品管理员1_ 运营商梢售管理员合作伙伴结算规那么in hr_part ner_spcoop_rels、partner_e|t_Qrde£ ."I"t_re|S

4、斗Xin hr_part ner_cp 、合同模板I -t I.-'-fa效劳提供商内容提供商1第算帐单1;0sp_coop_relscp_coop_relssettle or5物理设计提示：1 主要是设计表结构。一般地，实体对应于表，实体的属性对应于表的列，实体之间的关系成为表的约束。 逻辑设计中的实体大局部可以转换成物理设计中的表，但是它们并 不一定是对应的。2对表结构进行标准化处理第三范式5.1 表汇总表名功能说明En ter storage产品入库新产品的登记、修改、查询Exit storage产品出库登记产品出库信息Seller info销售商信息维护表对销售商信息的添加、修

5、改和查询Product info产品信息表对新产品的发布，修改，查询等维护Repair info维修表对已出售产品的维修进行登记，修改L管理员表包含五种身份，销售人员，库存管理人员，售后效劳人员，产品 信息管理人员，系统管理员52 表：表的索引：索引是否建立要根据具体的业务需求来确定。允许为空：不填的表示为“是。唯一：不填的表示为“是。表的记录数和增长量：根据具体的业务需求确定。增长量应确定单位时间如果量大可以按每天，如果不大可以按每月。表字段的区别度：主要是考虑到将来在此字段上建立索引类型选择时作为参考，当字段值唯一时可以不考虑，当字段值不唯一时，估算一个区别度，近似即 可。 例如：如果一个

6、表的 NAME字段有共2000个值,其中有1999个 不同 值,1999/2000=0.99越接近1区别度越高,反之区别度越低。表的并发：根据具体的业务需求预测表的并发。1 .表名En ter storage数据库用户库存管理人员主键ESID入库产品编号其他排序字段Product , date ,num,storageManagerld索引字段序号字段名称数据类型精度范 围允许为空Y/N唯一Y/N区别度默认值约束条件/说明1ESIDintNY2ProductVarchar(50)NN3dateSmallDateTimeNN4numVarchar(50)NN5storageMa nagerldV

7、archar(50)NYMysql脚本CREATE TABLE dbo.Enter storage( ESID int IDENTIT'Y(1,1) NOT NULL, product varchar(50) NOT NULL, date smalldatetime NOT NULL, num varchar(6) NOT NULL, storageMa nagerld nvarchar(50) NOT NULL,CONSTRAINT PK_addStorage PRIMARY KEY CLUSTERED(ESID ASC)WITH (PADN DEX = OFF, STATISTIC

8、S_NORECOMPUTE = OFF, IGNORE_DUP_KE Y 二 OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LO ON) ON PRIMARY)ON PRIMARY GOSET ANSI_PADDING OFF GO记录数增长量表的并发补充说明2.表名Exit storage数据库用户库存管理人员主键ExitStorageId出库产品编号其他排序字段Product ， date ，num,storageManagerld索引字段序号字段名称数据类型精度范 围允许为空Y/N唯一Y/N区别度默认值约束条件/说明1ExitStorageIdintNY2P

9、roductVarchar(50)NN3dateSmallDateTimeNN4numVarchar(50)NN5storageMa nagerldVarchar(50)NYMysql脚本CREATE TABLE dbo.Exit storage( ExitStorageld i ntIDENTITY丫(1,1)not NULL, product varchar(50) NOT NULL, date smalldatetime NOT NULL, n um varchar(6) NOT NULL, CONSTRAINT PK_subStorage PRIMARY KEY CLUSTERED(E

10、xitStorageId ASC)WITH (PADNDEX = OFF, STATISTICS_NORECOMPUTE = OFF IGNORE_DUP_KE Y = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMAR Y)ON PRIMAR Y GOSET ANSI_PADDING OFFGO/* Object: Table dbo.Enter_storage ScriptDate: 01/17/2021 16:00:59 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO SE

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

12、SmallDateTimeNN7stockPriceVarchar(50)NNMysql脚本CREATE TABLE dbo.Seller info( name varchar(50) NOT NULL, telepho ne varchar(20) NOT NULL, address varchar(50) NOT NULL, product varchar(50) NOT NULL, stockNum varchar(6) NOT NULL, stockDate smalldatetime NOT NULL, stockPrice varchar(10) NOT NULL,CONSTRAI

13、NT PK_maketer_l nfo PRIMARY KEY CLUSTERED(n ame ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE= OFF, IGNORE_DUP_KE=Y OFF, ALLOW_ROW_LOCKS O =N , ALLOW_PAGE_LOC = ON) ON PRIMAR Y)ON PRIMAR Y GOSET ANSI_PADDING OFFGO/* Object: Table dbo.Repair_info Script Date: 12/16/2021 09:59:13*/SET ANSI_NULLS O

14、N GOSET QUOTED_IDENTIFIER ONGO SET ANSI_PADDING ON GO记录数增长量表的并发补充说明4.表名Product info数据库用户产品信息管理人员主键proId其他排序字段name， price ，proDate, storage索引字段序号字段名称数据类型精度范 围允许为空Y/N唯一Y/N区别度默认值约束条件/说明1proIdintNY2n ameVarchar(50)NN3priceVarchar(50)NN4proDateSmallDateTimeNN5 storageVarchar(50) N YMysql脚本CREATE TABLE db

15、o.Product info( prold int IDENTITY(1,1) NOT NULL, n ame varchar(50) NOT NULL, price varchar(6) NOT NULL, proDate smalldatetime NOT NULL, storage varchar(4) NOT NULL,CONSTRAINT PK_product_l nfo PRIMARY KEY CLUSTERED(proId ASC)WITH (PADNDEX = OFF, STATISTICS_NORECOMPUTE O =F F, IGNORE_DUP_KE Y = OFF,

16、ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMAR Y)ON PRIMAR Y GOSET ANSI_PADDING OFFGO/* Object: Table dbo.Login Script Date: 01/17/2021 16:00:59*/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO SET ANSI_PADDING ONGO记录数增长量表的并发补充说明5.表名Repair info数据库用户售后效劳人员主键repairId产品维修编号其他排序字段repairName ， repa

17、irtel ， product, customer ， date ， customertel索引字段序号字段名称数据类型精度范 围允许为空Y/N唯一Y/N区别度默认值约束条件/说明1repairIdintNY2repairNameVarchar(50)NN3repairtelSmallDateTimeNN4productVarchar(50)NN5customerVarchar(50)NNdateSmallDateTimeNNcustomertelVarchar(50) N NMysql脚本CREATE TABLE dbo.Repair info( repairld int IDENTITY(

18、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 PRIMAR Y KEY CLUSTERED(repairId ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE= OFF, IGNOR

19、E_DUP_KE=Y 0 FF, ALLOW_ROW_LOCKSO =N , ALLOW_PAGE_LOC = ON) ON PRIMAR Y)ON PRIMAR Y GOSET ANSI_PADDING OFFGO/* Object: Table dbo.Product_i nfo Script Date: 01/17/2021 16:00:59*/SET ANSI_NULLS ON GOSET QUOTED_IDENTIFIER ONGO SET ANSI_PADDING ON GO记录数增长量表的并发补充说明6.表名Log in数据库用户管理人员主键userId用户编号其他排序字段pas

20、sword ， userName ， tel, birthday ， registerTime ，type索引字段序号字段名称数据类型精度范 围允许为空Y/N唯 Y/N区别度默认值约束条件/说明1userIdintNY2passwordVarchar(50)NN3userNameSmallDateTimeNN4telVarchar(50)NN5birthdayVarchar(50)NN6registerTimeSmallDateTimeNN7typeVarchar(50)NNMysql脚本CREATE TABLE dbo.Logi n( userid varchar(10) NOT NULL,

21、 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_ma nagernfo PRIMARY KEY CLUSTERED(userid ASC)WITH (PADN DEX = OFF, STATISTICS_NORECOMPUTE = OFF,IGNO

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

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

24、1.存储过程：CREATE PROCEDURE En ter_storage_GetMaxldASDECLARE TemplD intSELECT TempID = max( ESID )+ 1 FROM En ter_storage IF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE En ter_storage_ExistsESID intASDECLARE TempID intSELECT TempID = count ( 1) FROM En ter_storage WHERE ESID =ESIDIF TempID =

25、 0RETURN 0ELSERETURN 1CREATE PROCEDURE En ter_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 = IDE

26、NTITYCREATE PROCEDURE Enter_storage_UpdateESID int ,product varchar ( 50 ),date smalldatetime ,num varchar ( 6),storageManagerId nvarchar ( 50)ASUPDATE Enter_storage SET product = product , date = date , num = num, storageManagerId = storageManagerId WHERE ESID =ESIDCREATE PROCEDURE Enter_storage_De

27、leteESID 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 CREA

28、TE PROCEDURE Exit_storage_GetMaxIdASDECLARE TempID intSELECT TempID = max( ExitStorageId )+ 1 FROM IF TempID Exit_storage IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Exit_storage_ExistsExitStorageId intASDECLARE TempID intSELECT TempID = count ( 1) FROM Exit_storage ExitStorageId= ExitStorageId

29、IF 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, storageManag

30、erId)SET ExitStorageId = IDENTITYCREATE PROCEDURE Exit_storage_Update ExitStorageId int ,product varchar ( 50 ),date smalldatetime ,num varchar ( 6),storageMa nagerld nv archar ( 50) ASUPDATE Exit_storage product = product =storageMa nagerldWHERE ExitStorageIdSET=ExitStorageIddate = date , num二 nu m

31、, storageMa nagerldCREATE PROCEDURE Exit_storage_DeleteExitStorageId intASDELETE Exit_storageWHERE ExitStorageId= ExitStorageIdCREATE PROCEDURE Exit_storage_GetModel ExitStorageId intASSELECTExitStorageId , product , date , num, storageMa nagerld FROM Exit_storage WHERE ExitStorageId = ExitStorageId

32、CREATE PROCEDURE Exit_storage_GetListASSELECTExitStorageId , product , date , num, storageManagerld FROMExit_storage CREATE PROCEDURE Login_Existsuserld varchar ( 10)ASDECLARE TempID intSELECT TempID = cou nt ( 1) FROM Login WHERE userld = userld IF TempID =RETURN 0ELSERETURN 1CREATE PROCEDURE Login

33、_ADD userld varchar ( 10), password varchar ( 16), userName varchar ( 20),tel varchar ( 20), birthday smalldatetime ,ASn varcharsmalldatetime ,(50)INSERT INTO Logi n (userId , password , userName)VALUES(userId , password , userName ,tel , birthday , registerTime , ttel , birthday , registerTime , ty

34、peCREATE PROCEDURE Login_Update userId varchar ( 10), password varchar ( 16), userName varchar(20), tel varchar ( 20), birthday smalldatetime , registerTime smalldatetime , type nv archar ( 50)ASUPDATE Logi n SETpassword = password , userName = userName , tel = tel , birthday = birthday , registerTi

35、me =registerTime , type = typeWHERE userId = userIdCREATE PROCEDURE Login_Delete userId varchar ( 10)ASDELETE Logi n WHERE userId = userIdCREATE PROCEDURE Login_GetModel userId varchar ( 10)ASSELECTuserId , password , userName , tel , birthday , registerTime , type FROM Logi n WHERE userId = userIdC

36、REATE PROCEDURE Login_GetListAS,typeuserid , password , userName , tel , FROM birthday Login CREATE PROCEDURE Productnfo_GetMaxldASDECLARE TemplD intSELECT TemplD = max( prold )+1 FROM Product_i nfoIF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Product_i nfo_ExistsproId intASDECLARE Temp

37、ID intSELECT TempID = count ( 1) FROM Product_i nfo WHERE proId= proIdIF TempID = 0 RETURN 0ELSE RETURN 1CREATE PROCEDURE Product_i nfo_ADD proId int output ,n ame varchar ( 50),price varchar ( 6),proDate smalldatetime , storage varchar ( 4)ASINSERT INTO Product_i nfo (name , price , proDate , stora

38、ge ) VALUES (n ame, price , proDate , storage)SET proId = IDENTIT YCREATE PROCEDURE Product_i nfo_Update proId int ,n ame varchar ( 50),price varchar ( 6),proDate smalldatetime ,storage varchar ( 4)ASUPDATE Product_info SET name = name, price = price , proDate = proDate , storage = storage WHERE pro

39、Id =proIdCREATE PROCEDURE Product_info_DeleteproId intASDELETE Product_info WHERE proId =proIdCREATE PROCEDURE Product_info_GetModel proId intASSELECTproId , name, price , proDate , storageFROM Product_info WHERE proId =proIdCREATE PROCEDURE Product_info_GetList AS= repairIdcustomertelSELECTproId ,

40、name, price , proDate , storage FROM Product_info CREATE PROCEDURE Repair_info_GetMaxIdASDECLARE TempID intSELECT TempID = max( repairId )+ 1 FROM Repair_info IF TempID IS NULLRETURN 1ELSERETURN TempIDCREATE PROCEDURE Repair_info_ExistsrepairId intASDECLARE TempID intSELECT TempID = count ( 1) FROM

41、Repair_info WHERE repairId IF TempID = 0 RETURN 0ELSE RETURN 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)ASINSERT INTO Repair_info ( repairName , re

42、pairtel , product , customer , date ,) VALUES ( repairName , repairtel , product , customer , date , customertel )SET repairId = IDENTITYCREATE PROCEDURE Repair_info_UpdaterepairId int , repairName nchar ( 10), repairtel nchar ( 10 ),product varchar ( 50 ), customer varchar ( 20), date smalldatetime

43、 , customertel nchar ( 10)ASUPDATE Repair_info SET = repairtel , product = repairName = repairName , repairtelproduct , customer = customer , date = date , customertel = customertel WHERE repairId =repairIdCREATE PROCEDURE Repair_info_Delete repairId int ASDELETE Repair_info WHERE repairId =repairId

44、CREATE PROCEDURE Repair_info_GetModel repairId intASSELECTrepairId , repairName , repairtel , product , customer , date , customertel FROM Repair_info WHERE repairId =repairIdCREATE PROCEDURE Repair_info_GetListASSELECT repairld , repairName , repairtel , product , customer , date , customertel FROM

45、 Repairnfo CREATE PROCEDURE Seller_i nfo_Existsn ame varchar ( 50)ASDECLARE TemplD intSELECT TempID = cou nt ( 1) FROM Seller_i nfo WHERE name =nameIF TempID = 0 RETURN 0ELSE RETURN 1CREATE PROCEDURE Seller_i nfo_ADD n ame varchar ( 50),teleph onevarchar(20),addressvarchar(50 ),productvarchar(50 ),s

46、tockNumvarchar(6),stock smalldatetimeDatestbckINSERT INTO Seller_i nfo (n ame , teleph one , address , product ockPrice ,st,stockNum , stockDatevarchar ( 10 )VALUES(n ame, teleph one , address , product , stockNum , stockDate , stockPri ceCREATE PROCEDURE Seller_i nfo_Update n ame varchar ( 50), teleph one varchar ( 20),address varchar ( 50 ),product varchar (