oa.sql

主要OA系统的表结构

create database OA
--drop database OA
on primary--主文件
(
	name = 'OA_mdat',
	filename = 'd:\OA_M.mdf',
	size = 5,
	maxsize = unlimited,
	filegrowth = 30%
),
filegroup G_owner
(
        name = 'OA_n4dat',
	filename = 'd:\OA_N4.ndf',
	size = 5,
	maxsize = unlimited,
	filegrowth = 30%
),
filegroup G_change--变化文件
(
	name = 'OA_n1dat',
	filename = 'd:\OA_N1.ndf',
	size = 5,
	maxsize = unlimited,
	filegrowth = 30%
),
filegroup G_big--大数据文件
(
	name = 'OA_n2dat',
	filename = 'd:\OA_N2.ndf',
	size = 5,
	maxsize = unlimited,
	filegrowth = 30%
),
filegroup G_Test--文本文件
(
	name = 'OA_n3dat',
	filename = 'd:\OA_N3.ndf',
	size = 5,
	maxsize = unlimited,
	filegrowth = 30%
) 

log on--日志文件
(
	name = 'OA_log1',
	filename = 'd:\OA_L1.ldf',
	size = 5,
	maxsize = unlimited,
	filegrowth = 50%
),
(
	name = 'OA_log2',
	filename = 'd:\OA_L2.ldf',
	size = 5,
	maxsize = unlimited,
	filegrowth = 50%
)
go 

use OA

go
--建立部门表
	--部门编号
	--部门名称
	--部门撤销时间
	--部门职能描述
	
create table Department
(
depId varchar(100) not null constraint pk_Department_depId primary key,
depName varchar(50) not null,
cxTime varchar(20),
depDesc varchar(200)
)
on G_owner

--delete from Department where depId='0000'
-- insert into Department values('0001','人事部门','2002-01-01','asdfasdf')
-- insert into Department values('0002','销售部门','2002-01-02','cccccccc')
-- insert into Department values('0003','财务部门','2002-01-03','aaaaaaaaa')
-- insert into Department values('0004','仓库部门','2002-01-04','afffaffff')

-- select * from Department


--建立员工表

	--员工编号
	--姓名
        --用户名
	--密码(默认111111)
	--性别
	--员工状态(1-在线人员，2-兼职人员,3-试用人员,4-离职人员，5-反聘人员,6-退休人员)
	--联系电话
	--电子邮件
	--员工职能描述
	--出生日期
	--入职时间(默认填表时间)
	--离职时间(默认填表时间)

--drop table employee	
create table Employee
(

empId  int identity(1,1) not null constraint pk_Employee_empId primary key,
xm  varchar(40) ,        
empName varchar(20),
pwd varchar(100),
sex varchar(2),
state tinyint,
phone varchar(20),
email varchar(50),
empDesc varchar(200),
birth varchar(20),
joinTime varchar(20),
leaveTime varchar(20)

)
on G_owner


--delete from Employee

-- insert into Employee values('lxw','lxw','lxw','M',1,'256895','as#@163.com','aaa','sadf','2006-01-01',2006-01-01)
-- insert into Employee values('tmy','tmy','tmy','M',1,'256895','as#@163.com','aaa','sadf','2006-01-01',2006-01-01)
-- --测试人员
-- insert into Employee values('test','test','test','M',2,'256895','as#@163.com','aaa','sadf','2006-01-01',2006-01-04)
-- --离职人员
-- insert into Employee values('lea','lea','lea','M',3,'256895','as#@163.com','aaa','sadf','2006-01-01',2006-01-05)

--update Employee set empName='lxw11' where empId=1
-- select * from Employee
-- 
-- select * from Employee 
--建立部门员工表(多对多关系)
	--编号(自动增长)
	--部门编号 
	--职务(Employee表)
	--是否领导(0-否，1-是)
	--离开部门时间
	--员工编号 (Employee表)

--delete DepEmp
create table DepEmp
(
deId int identity(1,1) not null constraint pk_DepEmp_deId primary key,
depId varchar(100),
duty varchar(20),
leader bit,
depleaveTime varchar(20),
empId int 
)
on G_change

--select * from DepEmp 
--delete from DepEmp where empId=1 and depId>1
-- insert into DepEmp values('0001','经理',1,'',1)
-- insert into DepEmp values('0002','助理',0,'',2)
-- insert into DepEmp values('0003','销售员',1,'',3)
-- insert into DepEmp values('0004','会计师',0,'',4)
-- insert into DepEmp values('0002','销售员',1,'',2)
--PurView(权限)表
	--编号(自动增长)
	--员工编号(Employee表)
	--用户权限类型（0,1,2…）
create table PurView
(
purId int identity(1,1) not null constraint pk_PurView_purId primary key,
empId int,
userType tinyint
) on G_owner


--建立外键关系

	--权限表中的员工编号
	alter table PurView
	add constraint fk_PurView_Employee_empId foreign key(empId) references Employee(empId)
	
	--部门员工表中的部门编号
	alter table DepEmp
	add constraint fk_DepEmp_Department_depId foreign key(depId) references Department(depId)
	
	
	--部门员工表中员工编号
	alter table DepEmp
	add constraint fk_DepEmp_Employee_empId foreign key(empId) references Employee(empId)


--select * from PurView
--delete from PurView
-- insert into PurView values(1,1)
-- insert into PurView values(2,1)
-- insert into PurView values(3,2)
-- insert into PurView values(4,3)



create table Car
(
num varchar(8) not null constraint pk_Car_num primary key,
type varchar(8),
state varchar(8)
)
on G_owner

--建立申请表
	--申请表编号 主键
	--申请人 员工表外键 不为空
	--车辆类型 车辆表外键 不为空
	--预定取车时间 
	--预定还车时间
	--车辆用途
	--领导审批
	--领导签字 员工表外键
	--车牌号 车辆表外键
	--实际取车人 员工表外键
	--实际取车时间
	--实际还车人
	--实际还车时间
	--申请单状态
	
create table Apply
(
id int identity(1,1) not null constraint pk_Apply_id primary key,--编号
ause int ,--申请人与员工表关联
cartype varchar(8),--车辆类型
gagebreakout datetime,--预定取车时间
gagegiveback datetime,--预定还车时间
excuse varchar(100),--车辆用途
examine varchar(100),--领导审批
luse int ,--领导签字与员工表关联
carnum varchar(8),--车牌号
ouser int ,--实际取车人
factbreakout datetime,--实际取车时间与员工表关联
buser int ,--实际还车人与员工表关联
factgiveback datetime,--实际还车时间
state varchar(3) not null--状态
)
on G_change


--建立申请表外键关系

	
	
	--申请人
	alter table Apply add constraint fk_Apply_Employee_ause foreign key(ause) references Employee(empId)            
													--员工表(员工编号)				
	--领导
	alter table Apply add constraint fk_Apply_Employee_luse foreign key(luse) references Employee(empId)

	--实际取车人
	alter table Apply add constraint fk_Apply_Employee_ouser foreign key(ouser) references Employee(empId)

	--实际还车人
	alter table Apply add constraint fk_Apply_Employee_buser foreign key(buser) references Employee(empId)

	--车辆类型
	alter table Apply
	add constraint fk_Apply_Car_cartype foreign key(cartype) references Car(num)

	alter table Apply
	drop fk_Apply_Car_cartype
	--车牌号码
	alter table Apply
	add constraint fk_Apply_Car_carnum foreign key(carnum) references Car(num)

--文档管理
--drop table DoucumentFolder
create table DoucumentFolder --文件夹文档表(文件夹和文档共用的表)
(
	[id] varchar(40) not null constraint pk_DoucumentFolder_id primary key,--主健文件夹(文档)的编号 注:ID的产生--规则参考文档最后的说明
	[name] varchar(250) not null,--文件夹（文档）名
	ownerId int ,--外键，拥有者(创建人)编号 引用员工表
	typeDocument bit,--文件类型(文档0还是文件夹1) true=1 false=0
	createDate smalldatetime, --文档和文件夹的创建时间
	state bit,--文件现在的状态（如：签入0，签出1等）注：文件夹签出是修改文件夹名，文档签出是编辑文档
	editeId int --外键,最新文档的编辑人编号
)  on G_change
alter table DoucumentFolder
--建立拥有者有员工表的关联
	add constraint fk_DoucumentFolder_Employee_ownerId foreign key(ownerId) references  Employee(empId)
--建立文档编辑人与员工表的关联
alter table DoucumentFolder
	add constraint fk_DoucumentFolder_Employee_editeId foreign key(editeId) references  Employee(empId)
go

--delete from DoucumentFolder

--select * from DoucumentFolder
-- insert DoucumentFolder values('0001','技术文档',1,1,'2006-01-01',0,1)
-- 
-- insert DoucumentFolder values('00010001','Java',1,1,'2006-01-01',0,1)
-- insert DoucumentFolder values('000100010001','什么是反射',1,0,'2006-01-01',0,1)
-- insert DoucumentFolder values('000100010002','什么是集合',1,0,'2006-01-01',0,1)
-- 
-- insert DoucumentFolder values('00010002','.Net',1,1,'2006-01-01',0,0001)
-- insert DoucumentFolder values('000100020001','.Net的优点',1,0,'2006-01-01',0,1)
-- insert DoucumentFolder values('000100020002','.Net的前景',1,0,'2006-01-01',0,1)
-- 
-- insert DoucumentFolder values('0002','制度文档',1,1,'2006-01-01',0,1)
-- insert DoucumentFolder values('00020001','人事制度',1,0,'2006-01-01',0,1)
-- insert DoucumentFolder values('00020002','财务制度',1,0,'2006-01-01',0,1)
-- 
-- 
-- insert DoucumentFolder values('0003','市场计划',1,1,'2006-01-01',0,1)
-- insert DoucumentFolder values('00030001','国内计划',1,0,'2006-01-01',0,1)
-- insert DoucumentFolder values('00030002','国外计划',1,0,'2006-01-01',0,1)
-- 
-- insert DoucumentFolder values('0004','日记文档',1,1,'2006-01-01',0,1)
-- 
-- insert DoucumentFolder values('00040001','日记1',1,0,'2006-01-01',0,1)
-- insert DoucumentFolder values('00040002','日记2',1,0,'2006-01-01',0,1)
-- 
-- insert DoucumentFolder values('0005','test',1,1,'2006-01-01',0,1)
-- 
-- insert DoucumentFolder values('00050001','test1',1,0,'2006-01-01',0,1)
-- insert DoucumentFolder values('00050002','test2',1,0,'2006-01-01',0,1)
-- 
-- insert DoucumentFolder values('0006','学校',2,1,'2006-01-01',0,2)
-- 
-- insert DoucumentFolder values('00060001','班级1',2,1,'2006-01-01',0,2)
-- insert DoucumentFolder values('000600010001','xxxx1',2,0,'2006-01-01',0,2)
-- insert DoucumentFolder values('000600010002','xxxx2',2,0,'2006-01-01',0,2)
-- insert DoucumentFolder values('000600010003','xxxx3',2,0,'2006-01-01',0,2)
-- insert DoucumentFolder values('000600010004','xxxx4',2,0,'2006-01-01',0,2)
-- insert DoucumentFolder values('0005','生产部文档',0001,1,'2006-01-01',0,0001)