考勤建模

 

 

Scripts

if object_id(N'companydepartment','U') is not null

drop table companydepartment;

create table companydepartment

(depart_id int identity(1,1) not null,

 depart_emid int  default(0) not null,

 depart_name varchar(100) not null,

 constraint pk_depart_id primary key (depart_id)

);

create nonclustered index cindex_companydepartment on companydepartment(depart_emid,depart_name,depart_number)

 

if object_id(N'companyemployee','U') is not null

drop table companyemployee;

create table companyemployee

(id int identity(1,1),

 depart_id int not null,

 employee_id varchar(50) not null,

 employee_name varchar(50) not null,

 intodate smalldatetime not null

 constraint pk_employee_id primary key (employee_id),

 constraint fk_depart_id foreign key (depart_id) references companydepartment(depart_id) on delete cascade

);

create nonclustered index cindex_companyemployee on companyemployee(intodate,employee_name)

 

if object_id(N'date_timecard','U') is not null

drop table date_timecard

create table date_timecard

([year] smallint  not null,

 [month] char(2) not null,

 constraint date_timecard_yearcheck check([year]between 1799 and 3000),

 constraint date_timecard_monthcheck check([month] in('01','02','03','04','05','06','07','08','09','10','11','12'))

);

if object_id(N'employee_timecard','U') is not null

drop table employee_timecard;

create table employee_timecard

(id int identity(1,1),

 employee_id varchar(50) not null,

 depart_id  int not null,

 date smalldatetime null,

 constraint pk_employee_timecard primary key (id),

              constraint fk_employee_timecard foreign key (depart_id) references companydepartment(depart_id)

)

create nonclustered index nonindex_employee_timecard on employee_timecard(depart_id,date)

 

if object_id(N'employee_arrange','U') is not null

drop table employee_arrange;

create table employee_arrange

(id int identity(1,1),

 employee_id varchar(50) not null,

 [year] int not null,

 [month] char(2) not null,

 [day] char(2) not null,

 [arrange] varchar(10) not null,

 constraint ck_arrange_year check([year] between 1799 and 3000),

 constraint ck_arrange_month check([month] in('01','02','03','04','05','06','07','08','09','10','11','12')),

 constraint ck_arrange_day check([day] in('30','31')  or [day] like '[0-2][0-9]' ),

 constraint fk_arrange_employee_id foreign key (employee_id) references companyemployee(employee_id) on delete cascade

)

create clustered index cl_employee_arrange_index on employee_arrange(employee_id);

create nonclustered index noncl_employee_arrange_index on employee_arrange([year],[month],[day]);

 

if object_id(N'arrange','U') is not null

drop table arrange;

create table arrange

(arrange varchar(10) not null,

 bdate char(5) check(bdate like '__:__' and bdate like '[0-2][0-9]_[0-6][0-9]') not null,

 edate char(5) check(edate like '__:__' and edate like '[0-2][0-9]_[0-6][0-9]') not null

)