HumanResources.EmployeeDepartmentHistory (table)

wikibot

Table	HumanResources.EmployeeDepartmentHistory
Description	Employee department transfers.

Columns

Column	Data Type	Nullable	Default	Description
EmployeeID	int	not null		Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentID	smallint	not null		Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftID	tinyint	not null		Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDate	datetime	not null		Date the employee started work in the department.
EndDate	datetime	null		Date the employee left the department. NULL = Current department.
ModifiedDate	datetime	not null	(getdate())	Date and time the record was last updated.

Primary Key

Primary Key	Columns
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	EmployeeID, DepartmentID, ShiftID, StartDate

Indexes

Index	Type	Columns
IX_EmployeeDepartmentHistory_DepartmentID		DepartmentID
IX_EmployeeDepartmentHistory_ShiftID		ShiftID

Check Constraints

Check Constraint	Expression	Description
CK_EmployeeDepartmentHistory_EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)	Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NUL

Foreign Keys

Relation	Column	Referenced Column
HumanResources.Department	DepartmentID	DepartmentID
HumanResources.Employee	EmployeeID	EmployeeID
HumanResources.Shift	ShiftID	ShiftID

References

Dependency Type	Object Type	Referenced Object
Schema	Schema	HumanResources

Dependencies

Reference Type	Object Type	Referencing Object
Select	View	HumanResources.vEmployeeDepartment
Select	View	HumanResources.vEmployeeDepartmentHistory

automatically generated

Table	HumanResources.EmployeeDepartmentHistory
Description	Employee department transfers.

Column	Data Type	Nullable	Default	Description / PK / Index
EmployeeID	int	not null		Employee identification number. Foreign key to Employee.EmployeeID. PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID
DepartmentID	smallint	not null		Department in which the employee worked including currently. Foreign key to Department.DepartmentID. PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentIDIX_EmployeeDepartmentHistory_DepartmentID
ShiftID	tinyint	not null		Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentIDIX_EmployeeDepartmentHistory_ShiftID
StartDate	datetime	not null		Date the employee started work in the department. PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID
EndDate	datetime	null		Date the employee left the department. NULL = Current department.
ModifiedDate	datetime	not null	(GETDATE())	Date and time the record was last updated.

Relation	Column	Referenced Column
HumanResources.Department	DepartmentID	DepartmentID
HumanResources.Employee	EmployeeID	EmployeeID
HumanResources.Shift	ShiftID	ShiftID

Dependency Type	Object Type	Referenced Object
Schema	Schema	HumanResources

Reference Type	Object Type	Referencing Object
Select	View	HumanResources.vEmployeeDepartment
Select	View	HumanResources.vEmployeeDepartmentHistory