/************************************************************************/
/*
	程序编写： Jeff
	版    本:  V 1.0
	建立日期： 2015-05-21
	功能说明： 人力资源系列表;
	备    注： 
			  部门表,物理表;
			  职位表,物理表;  
			  员工表,物理表; 
			  员工职务表,记录员工职务相关信息(一个员工,可有多个职位);
			  员工待遇福利表,记录员工的各种待遇和福利信息(一个员工,可以有多种待遇福利); 
			  员工值班表;

	修改日期：
	修改说明：
*/
/************************************************************************/ 

/************************************************************************/
/*
	表  名:Department
	表描述:部门表(物理表);
	表架构:	
			1.一个部门可以有多种职位,如影楼门市部有:主门市,副门市,巴台收银,接线员....;
*/
/************************************************************************/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go

--判断表是否存在,不存在则创建;
if not exists(select * from dbo.sysobjects where id = object_id(N'Department') and objectproperty(id,'IsTable') = 1)
begin
create table [dbo].[Department](	
	[dep_id]							[nvarchar](16) not null,					-- 部门id(唯一);
	[dep_name]							[nvarchar](24) not null,					-- 部门名称;
	-- 部门权利;
	
	-- 主键;
	constraint [PK_Department] primary key clustered
	(
		[dep_id] asc
	)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary],

	-- 设置UNIQUE约束;
	constraint [UK_Department] unique nonclustered
	(
		[dep_name] asc
	)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary]

) on [primary]
end

go 
set ansi_padding off



/************************************************************************/
/*
	表  名:Position
	表描述:职位表(物理表);
	表架构:	
			每个员工,可以身兼多职,所以职位表是必要的;
*/
/************************************************************************/
-- 职位表Position;
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go

--判断表是否存在,不存在则创建;
if not exists(select * from dbo.sysobjects where id = object_id(N'Position') and objectproperty(id,'IsTable') = 1)
begin
create table [dbo].[Position](	
	[pos_id]							[nvarchar](36) not null,					-- 职位id;
	[pos_name]							[nvarchar](24) not null,					-- 职位名称;	
	[department]						[nvarchar](16) not null,					-- 职位所属部门;
	-- 职位学历要求;
	-- 职位经验要求;
	-- 职位年龄要求;
	-- 职位性别要求;
	-- 职位基本薪资(有些公司薪资与职位不挂勾,可空);
	-- 职位职责;
	
	-- 主键;
	constraint [PK_Position] primary key clustered
	(
		[posid] asc
	)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary],

	-- 设置UNIQUE约束;
	constraint [UK_Position] unique nonclustered
	(
		[posname] asc,
		[department] asc,
	)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary]

) on [primary]
end

go 
set ansi_padding off


/************************************************************************/
/*
	表  名:Personnel
	表描述:员工表(物理表);
	表架构:	
			;
*/
/************************************************************************/
set ansi_nulls on
go
set quoted_identifier on
go
set ansi_padding on
go

--判断表是否存在,不存在则创建;
if not exists(select * from dbo.sysobjects where id = object_id(N'Personnel') and objectproperty(id,'IsTable') = 1)
begin
create table [dbo].[Personnel](	
	[per_id]							[nvarchar](36) not null,					-- 员工工号(唯一);
	[per_name]							[nvarchar](24) not null,					-- 员工姓名;	
	[per_sex]							[nvarchar](4) not null,						-- 员工性别;	
	[per_hometown]						[nvarchar](48) not null,					-- 员工籍贯;
	[per_nation]						[nvarchar](16) not null,					-- 员工民族;
	[per_birthday]						[nvarchar](16) not null,					-- 员工生日;
	[per_identity]						[nvarchar](64) not null,					-- 员工身份证号(唯一);		
	[per_address]						[nvarchar](64) not null,					-- 员工现居地;
	[per_married]						[nvarchar](64) not null,					-- 员工婚否;
	[per_Graduated]						[nvarchar](64) not null,					-- 员工毕业院校;
	[per_Education]						[nvarchar](16) not null,					-- 员工学历;
	[per_special]						[nvarhcar](64) not null,					-- 员工专业;
	[per_Email]							[nvarchar](64) not null,					-- 员工Email;
	[per_phone]							[nvarchar](16) not null,					-- 员工手机;
	[per_QQ]							[nvarchar](24) not null,					-- 员工QQ;
	[per_WX]							[nvarchar](36) not null,					-- 员工微信;
	[per_ecperson1]						[nvarchar](24) not null,					-- 员工紧急联系人1		emergency contact person;	
	[per_ecphone1]						[nvarchar](16) not null,					-- 员工紧急联系人1手机	emergency contact phone;	
	[per_ecperson2]						[nvarchar](24) not null,					-- 员工紧急联系人2;	
	[per_ecphone2]						[nvarchar](16) not null,					-- 员工紧急联系人2手机;	
	[per_Image]							[image] null,								-- 员工工作照;
	[per_identityfrontphoto]			[image] null,								-- 员工身份照-正面;
	[per_identitybackphoto]				[image] null,								-- 员工身份照-背面;
	
	-- 主键;
	constraint [PK_Personnel] primary key clustered
	(
		[perid] asc
	)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary],

	-- 设置UNIQUE约束;
	constraint [UK_Personnel] unique nonclustered
	(
		[peridentity] asc
	)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on,allow_page_locks = on) on [primary]

) on [primary]
end

go 
set ansi_padding off