XXXX数据库设计V0.1
拟制：王 鹏 2014-12-19
引言
目的
本文为XXX公司内控管理系统的数据库设计规范说明书。本说明书将： 述数据库设计的目的
说明数据库设计中的主要组成部分 说明数据库设计中各功能的实现
内容
本文档主要内容包括对数据库设计结构的总体描述，对数据库中各种对象的描述（包括对象的名称、对象的属性、对象和其他对象直接的关系）。涵盖了内控管理中的三大业务模块：预算管理、立项管理、合同管理。
本文档中包含对以下数据库内容的描述： 数据表 视图 约束
存储过程（预留） 触发器（预留）
命名规范
➢ 数据存储表：以t_开头； ➢ 基础信息维护表：以t_bs_开头； ➢ 自定义函数：以fn_开头；
➢ 触发器：tr_表名_[后面---添加时加I,修改时加U,删除时加D]； ➢ 视图：在表命名的基础上加前缀改为V_
数据表设计
预算管理
根据设计的系统功能，数据库将按照预算业务场景，实现预算收集和预算下发等流程。 数据库设计将以存储预算信息表为基础，连接多张相关表以实现对以下关系的支持： 总部收集记录 分公司调整、审核记录 总部汇总记录 总部下发业务线记录 业务线调整记录
预算收集信息表（t_budget） 概述
预算收集信息表用于记录预算收集流程的基本信息，并作为基础表与其他表连接。
表定义
预算收集信息表定义如下图所示：
预算收集信息表
字段 budgetId captialTypeId yearTag totalCaptial 类型 varchar(40) INT varchar(16) Number 可否为空 N N N N 备注 预算ID，主键 资金类别 年度 预算总额 comment createTime status deptCode bizLineId
varchar(2048) Timestamp INT varchar(32) varchar(32) Y Y Y Y Y 备注 创建时间 预算状态 部门编码 业务线编码 主键
预算ID字段budgetId为主键，由系统生成唯一不重复字符串。
约束
资金类别字段captialTypeId与资金类型基础信息表进行ID关联。
消息通知表（t_notification） 概述
在预算收集及下发过程中，通知总部、分公司和业务线的消息记录表料。表中都记录了消息的类别、接收人、发送人、发送时间等。
表定义
消息通知表定义如下图所示。
消息通知表
字段 id receiveUser title 类型 varchar(40) varchar(40) varchar(255) 可否为空 N N N 备注 主键ID 接收者 消息标题 description sendTime status orderId typeid varchar(2048) Timestamp INT varchar(40) INT Y Y Y Y Y 详细描述 发送时间 消息状态 流程ID序列号 消息类别 主键
消息id为主键，由系统生成唯一不重复字符串。。
约束
➢ 消息类别ID（typeid）和消息类别基础信息表ID外关联； ➢ 流程ID号（orderId），是根据typeid所对应的流程单号；
审核记录表（t_approveRecord） 概述
审核记录表记录内控管理中所有工作流中的审核信息，通过流程类别和流程单号进行关联。
表定义
审核记录表定义如下图所示。
审核记录表
字段 类型 可否为空 备注 approveId flowType orderid approver operateTime approveRes approveComment varchar(40) varchar(255) Varchar(40) varchar(255) timestamp varchar(255) varchar(2048) N N N N N Y Y 主键ID 流程类别 流程单号ID 审核人 操作时间 审核结论 审核说明 主键
审核id（approveId）为主键，由系统生成唯一不重复字符串。
约束
➢ 流程ID号（orderId），是根据flowType所对应的流程单号；
预算下发表（t_ budgetIssue） 概述
表定义
预算下发表定义如下图所示。
预算下发表
字段 id captialTypeId 类型 varchar(40) INT 可否为空 N N 备注 主键ID 资金类别 yearTag totalCaptial comment createTime issueStatus budgetId varchar(255) Number varchar(2048) Timestamp INT N N Y Y Y Y 预算年度 预算总额 备注 创建时间 下发状态 预算信息表ID Varchar(40) 主键
下发id为主键，由系统生成唯一不重复字符串。
约束
➢ 预算信息ID（budgetId）和预算信息表ID关联；
➢ 资金类别字段captialTypeId与资金类型基础信息表进行ID关联
立项管理
立项信息表（t_project） 概述
该表记录项目基本信息。
表定义
消息通知表
字段 projectId porjectCode projectType bizLineCode deptCode totalAmount contact createTime status checkLists 类型 varchar(40) varchar(40) varchar(40) varchar(40) varchar(40) Number 可否为空 N Y N Y Y Y Y N Y Y 备注 项目ID主键 项目编码 项目类型 业务线编码 部门编码 项目金额 项目负责人 创建时间 项目状态 审核CheckList列表，数组格式 varchar(40) Timestamp INT varchar(1024) 主键
projectId为主键，由系统生成唯一不重复字符串。
约束
➢ 业务线条编码（bizLineCode）关联自业务线条基础信息表的ID； ➢ 部门编码（deptCode），关联自部门基本信息表的ID；
➢ checkLists是checkList信息维护表ID的数组格式，如：12,23,35,38,41;
checkList审核记录表（t_ checkListRecord） 概述
。CREATE TABLE `checkList记录表` ( `recordId` int NULL COMMENT '主键ID', `checklistId`
varchar(255) NULL COMMENT '对应CheckListID', `projectId` varchar(255) NULL COMMENT '项目ID', `operater` varchar(255) NULL COMMENT '操作人', `operatertime` varchar(255) NULL COMMENT '操作时间', `score` int NULL COMMENT '分数', PRIMARY KEY (`recordId`) ) COMMENT='t_checkListRecord\\r\\ncheckList记录表' ;
表定义
消息通知表
字段 recordId checklistId projectId operater operatertime score 类型 varchar(40) INT varchar(40) varchar(255) Timestamp INT 可否为空 N N Y N N Y 备注 记录ID 审核项ID 项目ID 操作人 操作时间 分数 主键
recordId为主键，由系统生成唯一不重复字符串。
约束
➢ checklistId关联自检查项基本信息维护表的ID；
合同管理
XXXX表（t_xxxx） 概述
。
表定义
消息通知表
字段 类型 可否为空 备注 主键
为主键，由系统生成唯一不重复字符串。。
约束
➢ 消息类别ID（typeid）和消息类别基础信息表ID外关联； ➢ 流程ID号（orderId），是根据typeid所对应的流程单号；
XXXX表（t_xxxx） 概述
。
表定义
消息通知表
字段 类型 可否为空 备注 主键
为主键，由系统生成唯一不重复字符串。。
约束
➢ 消息类别ID（typeid）和消息类别基础信息表ID外关联； ➢ 流程ID号（orderId），是根据typeid所对应的流程单号； 基础信息表
消息类型表（t_bs _noticeType） 概述
该表存放系统的消息类别，例如系统各审核流程均属于不同的消息类型。
表定义
消息通知表
字段 typeId typeName comment 类型 INT varchar(255) varchar(255) 可否为空 N N Y 备注 消息类型ID 类型名称 备注说明 主键
typeId为主键，整数类型，自增。
约束
➢ 消息类别ID（typeId）和消息通知表ID关联；
组织机构表（t_bs_deptTree） 概述
XXX公司组织机构表，为树形结构，作为基础信息表维护。
表定义
消息通知表
字段 deptId deptName fatherId comment 类型 INT varchar(255) INT varchar(255) 可否为空 N N N Y 备注 机构ID 机构名称 父节点ID 备注说明 主键
deptId为主键，整型类别。
约束
整个系统中所有流程和业务设计的机构均通过deptId关联该表。
业务线条信息表（t_bs_bizLineTree） 概述
。CREATE TABLE `业务线条表` ( `bizLineId` varchar(32) NOT NULL COMMENT '业务线ID，主键', `bizLineName` varchar(255) NULL COMMENT '业务线名称', `fatherId` varchar NULL COMMENT '父节点ID', `bizLineCode` varchar(255) NULL COMMENT '编码', `comment` varchar(255)
NULL
COMMENT
'备
注
',
PRIMARY
KEY
(`bizLineId`)
)
COMMENT='t_bs_bizLineTree\\r\\n基本信息-业务线条目录管理表' ;
表定义
消息通知表
字段 bizLineId bizLineName fatherId bizLineCode comment 类型 varchar(40) varchar(255) varchar(40) varchar(40) varchar(1024) 可否为空 N N N Y Y 备注 业务线ID 业务线名称 父节点ID 业务线编码 备注 主键
bizLineId为主键，由系统生成唯一不重复字符串。。
约束
无
审核checkList信息维护表（t_bs_checkList） 概述
该表是系统审核项Checklist的基本信息维护表，通过类别（typeTag），对审核项进行分类。
表定义
消息通知表
字段 itemId itemTitle typeTag description score 类型 Int varchar(255) varchar(255) varchar(2048) INT 可否为空 N N Y Y Y 备注 审核项ID 标题 审核类别 详细描述 分数 主键
itemId为主键，整型格式。
约束
无
XXXX表（t_xxxx） 概述
。
表定义
消息通知表
字段 类型 可否为空 备注 主键
为主键，由系统生成唯一不重复字符串。。
约束
➢ 消息类别ID（typeid）和消息类别基础信息表ID外关联； ➢ 流程ID号（orderId），是根据typeid所对应的流程单号；
XXXX表（t_xxxx） 概述
。
表定义
消息通知表
字段 类型 可否为空 备注 主键
为主键，由系统生成唯一不重复字符串。。
约束
➢ 消息类别ID（typeid）和消息类别基础信息表ID外关联； ➢ 流程ID号（orderId），是根据typeid所对应的流程单号；
XXXX表（t_xxxx） 概述
。
表定义
消息通知表
字段 类型 可否为空 备注 主键
为主键，由系统生成唯一不重复字符串。。
约束
➢ 消息类别ID（typeid）和消息类别基础信息表ID外关联； ➢ 流程ID号（orderId），是根据typeid所对应的流程单号；
系统设置表(SystemSet)
概述
系统设置表用于系统各详细设置信息。系统设置表包含的主要内容有：用户权限设置、赔偿价格参照、公告、证件相关各信息和备份恢复等。
表定义
系统设置表定义如错误!未找到引用源。所示。
表 0-1
名称 ID 类型 int 可否为空 否 否 否 可 可 说明 备注 UserPermissionSet nvarchar(10) Overdatepoint lostPrice RenewTime BackupSet Announce CardReCost Cardfillprice money money datetime datetime nvarchar(50) money money CardReTime ModifyPerson Modifytime datetime nvarchar(10) datetime 主键
系统设置表编号。
约束
无其他约束。
索引
主键字段系统设置表编号具有自动创建的聚集索引。
各表SQL代码示例
管理员用户信息表（UserInfo）
create table UserInfo (
ID Numeric identity(1001,1) not null, Username nvarchar(10) not null, Passwd char(8) not null,
Type nvarchar(14) check(Type ='花卉管理员' or Type ='系统管理员') not null,
constraint pk_UserInfo_id primary key(ID) )
用户用户信息表（ReaderInfo）（普通用户）
create table ReaderInfo
(
Username nvarchar(10) not null, ID int not null,
Passwd char(8) not null, Name nvarchar(10) not null,
Sex char(2) check(Sex ='男' or Sex ='女')not null, Age int ,
StuNum Numeric ,
PlaceStrand nvarchar(50), PlaceClass nvarchar(50), ValidCard nvarchar(50), BookCard nvarchar(10),
CreateTime datetime not null, ExpiredTime datetime not null,
constraint pk_ReaderInfo_id primary key (ID) )
顾客类别表（ReaderType）
Create table ReaderType (
ID int ,
Name nvarchar(10), ReaderID int not null, Canborrow int,
constraint pk_ReaderType_id primary key (ID) )
alter table ReaderType
add constraint fk_ReaderType_ReaderInfo foreign key (ReaderID) references ReaderInfo (ID);
*花卉信息表(BookInfo)
create table BookInfo (
ID nvarchar(20) not null, Name nvarchar(50) , Typeid int, State binary , Creattime datetime , Price money ,
Press nvarchar(50) , IsCancellation binary , IDCancellation int , Note nvarchar(50) , Author nvarchar(10) , Page int ,
constraint pk_BookInfo_id primary key (ID) )
alter table BookInfo
add constraint fk_BookInfo_BookType foreign key (Typeid) references BookType (Typeid);
花卉类别表（BookType)
create table BookType (
Typeid int ,
Typename nvarchar(50),
constraint pk_BookType_id primary key(Typeid) )
花卉库存表(BookStock)
Create table BookStock
(
ID int ,
BookID nvarchar(20) not null,
constraint pk_BookStock_id primary key (ID) )
alter table BookStock
add constraint fk_BookStock_BookInfo foreign key (BookID) references BookInfo (ID);
花卉订购表（BookBorrow)
create table BookBorrow (
ID Numeric,
BookID nvarchar(20), CardID Numeric, BorrowTime datetime, ReturnTime datetime, IsReturn nvarchar(10),
constraint pk_BookBorrow_id primary key (ID)
)
alter table BookBorrow
add constraint fk_BookBorrow_BookInfo foreign key (BookID) references BookInfo (ID);
花卉订购表LendRoom ( ID int ,
Name nvarchar(10),
LendRoomBookID nvarchar(20) ,
constraint pk_LendRoom_id primary key (ID)
)
alter table LendRoom
add constraint fk_LendRoom_LendRoomBook foreign key (BookID) references BookInfo (BookID);
花卉支付籍表LendRoomBook
Create table LendRoomBook (
ID nvarchar(20) not null, BookID int not null, BookCount int , BookState binary ,
constraint pk_LendRoomBook_id primary key (ID) )
alter table LendRoomBook
add constraint fk_LendRoomBook_BookInfo foreign key (BookID) references BookInfo (ID);
用户验证表BookCard
Create table BookCard (
ID Numeric ,
ReaderID int not null, Borrowed int ,
constraint pk_BookCard_id primary key (ID) )
alter table BookCard
add constraint fk_BookCard_ReaderInfo foreign key (ReaderID) references ReaderInfo (ID);
留言表Message
create table Msssage (
ID int ,
ReaderID nvarchar(20) not null, details nvarchar(50) not null,
constraint pk_Msssage_id primary key (ID) )
alter table Msssage
add constraint fk_Msssage_ReaderInfo foreign key (ReaderID) references ReaderInfo (ID);
系统设置表SystemSet Create table SystemSet (
ID int
UserPermissionSet nvarchar(10) Overdatepoint money lostPrice money RenewTime datetime BackupSet datetime Announce nvarchar(50) CardReCost money Cardfillprice money CardReTime datetime ModifyPerson nvarchar(10) Modifytime datetime
constraint pk_SystemSet_id primary key (ID)
)
视图
订单合同视图(v_contractOrder)
概述
此视图通过左外连接员工和部门表得到了员工的详细信息，其中包括员工的基本信息、
员工的部门信息和员工经理信息。 Web用户具有查看的权限。
基表
BookInfo LendRoomBook
视图包含字段
dbo.BookInfo.ID dbo.BookInfo.Name dbo.LendRoomBook.Times dbo.BookInfo.Typeid dbo.BookInfo.Price dbo.BookInfo.Press dbo.BookInfo.Note dbo.BookInfo.Author dbo.BookInfo.State
dbo.BookInfo.Page dbo.BookInfo.Creattime 代码示例:
SELECT dbo.BookInfo.ID, dbo.BookInfo.Name, dbo.LendRoomBook.Times, dbo.BookInfo.Typeid, dbo.BookInfo.Price, dbo.BookInfo.Press, dbo.BookInfo.Note, dbo.BookInfo.Author, dbo.BookInfo.Creattime
FROM dbo.BookInfo INNER JOIN dbo.LendRoomBook dbo.LendRoomBook.BookID
ON
dbo.BookInfo.ID
=
dbo.BookInfo.State,
dbo.BookInfo.Page,
系统设置表(SystemSet)
概述
此视图通过左外连接员工和部门表得到了员工的详细信息，其中包括员工的基本信息、
员工的部门信息和员工经理信息。 Web用户具有查看的权限。
基表
BookInfo LendRoomBook
视图包含字段
dbo.BookInfo.ID dbo.BookInfo.Name dbo.LendRoomBook.Times dbo.BookInfo.Typeid
dbo.BookInfo.Price dbo.BookInfo.Press dbo.BookInfo.Note dbo.BookInfo.Author dbo.BookInfo.State dbo.BookInfo.Page dbo.BookInfo.Creattime 代码示例:
SELECT dbo.BookInfo.ID, dbo.BookInfo.Name, dbo.LendRoomBook.Times, dbo.BookInfo.Typeid, dbo.BookInfo.Price, dbo.BookInfo.Press, dbo.BookInfo.Note, dbo.BookInfo.Author, dbo.BookInfo.Creattime
FROM dbo.BookInfo INNER JOIN dbo.LendRoomBook dbo.LendRoomBook.BookID
ON
dbo.BookInfo.ID
=
dbo.BookInfo.State,
dbo.BookInfo.Page,
数据库安全和备份
数据库安全
基于项目特点，教学案例项目的安全控制主要由程序代码控制。Mysql端不特殊地设定安全设置和检查。项目的用户身份验证、用户授权管理都由应用程序代码实现。
数据库备份恢复策略
本节内容与数据库功能无关，将从内控管理系统的数据库运行角度出发，对本数据库的属性设置和备份恢复策略提出指导性说明
数据库的大小和数据修改的频率决定了采用何种数据库备份恢复策略。如果数据库较小或修改的频度非常低，则可以只实施完全数据库备份；否则当数据库比较大时则需要使用差量备份。根据目前数据库规模不大，建议每周做一次完全备份。
因篇幅问题不能全部显示，请点此查看更多更全内容