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