myoffice.sql

will let you know the result once they replied will let you know the result once they replied

if exists(select * from sysdatabases where name = 'MyOffice')
drop database MyOffice
go
create database MyOffice
on
(
	name = 'MyOffice_data',
	filename = 'e:\sql\MyOffice.mdf',
	size = 5,
	maxsize = 10
)
log on
(
	name = 'MyOffice_log',
	filename = 'e:\sql\MyOffice.ldf',
	size = 1,
	maxsize = 2
)
go
use MyOffice
go
create table UserInfo										--用户信息表
(
	userID varchar(50) primary key not null,				--用户唯一标识
	userName varchar(50) not null,							--真实姓名
	password varchar(50) not null,							--密码
	departid int not null,									--所在部门
	gender int not null,									--性别
	roleid int not null,									--用户角色
	userState int not null									--用户状态								
)
go
create table UserState										--用户状态表
(
	userStateID int identity(1,1) primary key not null,		--自增长
	userStateName varchar(50) not null						--1 正常状态 0 被屏蔽
)
go
create table RoleInfo										--角色基本信息
(
	roleID int identity(1,1) primary key not null,			
	roleName varchar(50) not null,							--角色名称
	roleDesc varchar(50)									--角色描述
)	
go
create table SysFun											--菜单功能基本信息
(
	nodeID int primary key not null,									--菜单节点ID
	desplayName varchar(50) not null,						--菜单名称
	nodeURL varchar(50),									--菜单连接地址
	displayOrder int not null,								--菜单显示顺序
	parentNodeID int not null								--父节点id
)	
go
create table RoleRight										--所有角色权限
(
	roleRightID int identity(1,1) not null,					
	roleID int not null,									--角色ID
	nodeID int not null										--菜单节点ID
)
go
create table BranchInfo										--机构信息表
(
	branchID int identity(1,1) primary key not null,		
	branchName varchar(50) not null,						--机构名称
	branchShortName varchar(50) not null					--机构简称
)
go
create table DepartInfo										--部门表
(
	departID int identity(1,1) primary key not null,		
	departName varchar(50)not null,							--部门名称
	principalUser varchar(50) not null,						--部门负责人
	connectTelNo bigint,									--联系电话
	connectMobileTelNo bigint,								--移动电话
	faxes bigint,											--传真
	branchid int not null									--所属机构
)	
go
create table FileInfo
(
	fileID int identity(1,1) primary key not null,			
	fileName varchar(50) not null,							--文件名称
	fileType int not null,									--文件类型
	remark varchar(50),										--备注
	fileOwner varchar(50) not null,							--创建者
	createDate datetime not null,							--创建日期
	parentid int not null,									--父节点ID
	filePath varchar(200) not null,							--文件路径
	ifDelete int not null									--是否已经删除 1 已删除 2 未删除
)
go
create table FileTypeInfo
(
	fileTypeID int identity(1,1) primary key not null,
	fileTypeName varchar(50) not null,						--文件类型名
	fileTypeImage varchar(50) not null,						--文件类型对应图标
	fileTypeSuffix varchar(50)								--文件后缀
)
go
create table AccessoryFile									--附件文件表
(
	accessrotyid int identity(1,1) primary key not null,
	fileid int not null,
	accessrotName varchar(50) not null,
	accessrotySize int not null,
	accessoryType int not null,
	createdate datetime not null,
	accessoryPath varchar(200) not null
)
go
create table ManualSign
(
	signId int identity(1,1) primary key not null,
	userId varchar(50) not null,
	signTime datetime not null,
	signDesc varchar(200) not null,
	signTag int not null
)
go
create table WorkTime
(
	workTimeID int identity(1,1) primary key not null,
	onDutyTime datetime not null,
	offDutyTime datetime not null
)
go
create table Message
(
	messageID int identity(1,1)primary key not null,
	title varchar(100) not null,
	content varchar(5000)not null,
	type int not null,
	beginTime datetime not null ,
	endTime datetime not null,
	fromUserId int not null,
	ifPublish int not null,
	recordTime datetime not null
)
go
create table MessageType 
(
	messageTypeID int identity(1,1) primary key not null,
	messageTypeName varchar(50) not null,
	messageDesc varchar(50)
)
go
create table MessageToUser
(
	id int identity(1,1) primary key not null,
	messageId int not null,
	toUserId varchar(50) not null,
	ifRead int not null 
)
go
create table ReadCommonMessage
(
	readId int identity(1,1) primary key not null,
	messageid int not null,
	userId varchar(50) not null
)
go
create table Schedule
(
	scheduleId int identity(1,1) primary key not null,
	title varchar(50) not null,
	address varchar(500) not null,
	meetingId int not null,
	beginTime datetime not null,
	endTime datetime not null,
	schContent varchar(500) not null,
	createUser varchar(50) not null,
	createTime datetime not null,
	ifPrivate int not null
)
go
create table MeetingInfo
(
	meetingId int identity(1,1) primary key not null,
	meeetingName varchar(50) not null
)
go
create table PreContract
(
	preContractid int identity(1,1) primary key not null,
	scheduleid int not null,
	userId varchar(50) not null
)
go
create table MyNote
(
	noteId int identity(1,1) primary key not null,
	noteTitle varchar(50) not null,
	noteContent varchar(500),
	createTime datetime not null,
	createUser varchar(50) not null
)
go
create table LoginLog
(
	loginId int identity(1,1)primary key not null,
	userId varchar(50) not null,
	loginTime datetime not null,
	ifSuccess int not null,
	loginUserIp varchar(100) not null,
	loginDesc varchar(100)
)
go
create table OperateLog
(
	operateId int identity(1,1) primary key not null,
	userId varchar(50) not null,
	operateName varchar(50) not null,
	bojectId varchar(50) not null,
	operateDesc varchar(200) not null,
	operateTime datetime not null
)
go
alter table userInfo add constraint fk_userstateid foreign key(userState) references userState(userStateid)
alter table roleright add constraint fk_roleid foreign key(roleid)references roleinfo(roleid)
alter table roleright add constraint fk_nodeid foreign key(nodeid)references sysfun(nodeid)
alter table departInfo add constraint fk_branchid foreign key(branchid)references branchinfo(branchid)
alter table fileinfo add constraint fk_filetype foreign key(filetype)references filetypeinfo(filetypeid)
alter table accessoryfile add constraint fk_fileid foreign key(fileid)references fileinfo(fileid)
alter table accessoryfile add constraint fk_accessorytype foreign key(accessorytype)references filetypeinfo(filetypeid)
alter table manualsign add constraint fk_userid foreign key(userid)references userinfo(userid)
alter table messagetouser add constraint fk_messageid foreign key(messageid)references message(messageid)
alter table messagetouser add constraint fk_touserid foreign key(touserid)references userinfo(userid)
alter table readcommonmessage add constraint fk_messageid2 foreign key(messageid)references message(messageid)
alter table readcommonmessage add constraint fk_userid2 foreign key(userid)references userinfo(userid)
alter table schedule add constraint fk_meetingid foreign key(meetingid)references meetinginfo(meetingid)
alter table precontract add constraint fk_scheduleid foreign key(scheduleid)references schedule(scheduleid)
alter table precontract add constraint fk_userid3 foreign key(userid)references userinfo(userid)
alter table loginlog add constraint fk_userid4 foreign key(userid)references userinfo(userid)
alter table userinfo add constraint fk_roleid2 foreign key(roleid) references roleinfo(roleid)
alter table userinfo add constraint fk_departid foreign key(departid) references departinfo(departid)
go
insert sysfun values(101,'人事管理','',1,0)
insert sysfun values(102,'日程管理','',1,0)
insert sysfun values(103,'文档管理','',1,0)
insert sysfun values(104,'消息传递','',1,0)
insert sysfun values(105,'系统管理','',1,0)
insert sysfun values(106,'考勤管理','',1,0)
insert sysfun values(101001,'机构信息','',2,101)
insert sysfun values(101002,'部门信息','',2,101)
insert sysfun values(101003,'员工管理','',2,101)
insert sysfun values(102001,'我的日程','',2,102)
insert sysfun values(102002,'部门日程','',2,102)
insert sysfun values(102003,'我的便签','',2,102)
insert sysfun values(103001,'文档管理','',3,103)
insert sysfun values(103002,'回收站','',3,103)
insert sysfun values(103003,'文件搜索','',3,103)
insert sysfun values(104001,'消息管理','',4,104)
insert sysfun values(104002,'信箱','',4,104)
insert sysfun values(105001,'角色管理','',5,105)
insert sysfun values(105002,'登陆日志','',5,105)
insert sysfun values(105003,'操作日志','',5,105)
insert sysfun values(105004,'菜单排序','',5,105)
insert sysfun values(106002,'考勤历史记录查询','',6,106)
insert sysfun values(106001,'员工签到，签退','',6,106)
insert sysfun values(106003,'考勤统计','',6,106)

insert meetinginfo values('公司年会')
insert meetinginfo values('机构会议')
insert meetinginfo values('部门会议')
insert meetinginfo values('小组会议')
insert meetinginfo values('外部合作会议')