HumanResources |
04/26/2006 |
PRIMARY |
16 |
48 |
296 |
Employee department transfers. |
|
|
EmployeeID |
int |
4 |
|
|
|
|
Employee identification number. Foreign key to Employee.EmployeeID. |
|
|
DepartmentID |
smallint |
2 |
|
|
|
|
Department in which the employee worked including currently. Foreign key to Department.DepartmentID. |
|
|
ShiftID |
tinyint |
1 |
|
|
|
|
Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. |
|
|
StartDate |
datetime |
8 |
|
|
|
|
Date the employee started work in the department. |
|
|
EndDate |
datetime |
8 |
|
|
|
|
Date the employee left the department. NULL = Current department. |
|
|
ModifiedDate |
datetime |
8 |
|
(getdate()) |
|
|
Date and time the record was last updated. |
Total: 6 column(s)
Total: 3 index(es)
|
CK_EmployeeDepartmentHistory_EndDate |
([EndDate]>=[StartDate] OR [EndDate] IS NULL) |
Total: 1 constraint(s)
Total: 3 table(s)
Total: 8 object(s)
Total: 2 object(s)
CREATE TABLE [EmployeeDepartmentHistory] (
[EmployeeID] [int] NOT NULL ,
[DepartmentID] [smallint] NOT NULL ,
[ShiftID] [tinyint] NOT NULL ,
[StartDate] [datetime] NOT NULL ,
[EndDate] [datetime] NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeeDepartmentHistory_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID] PRIMARY KEY CLUSTERED
(
[EmployeeID],
[StartDate],
[DepartmentID],
[ShiftID]
) ON [PRIMARY] ,
CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID] FOREIGN KEY
(
[DepartmentID]
) REFERENCES [Department] (
[DepartmentID]
),
CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_EmployeeID] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [Employee] (
[EmployeeID]
),
CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID] FOREIGN KEY
(
[ShiftID]
) REFERENCES [Shift] (
[ShiftID]
),
CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
) ON [PRIMARY]
GO
List of tables