HumanResources EmployeeDepartmentHistory (table)

wikibot

TableHumanResources.EmployeeDepartmentHistory
DescriptionEmployee department transfers.

Columns

ColumnData TypeNullableDefaultDescription
EmployeeIDintnot null Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentIDsmallintnot null Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftIDtinyintnot null Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDatedatetimenot null Date the employee started work in the department.
EndDatedatetimenull Date the employee left the department. NULL = Current department.
ModifiedDatedatetimenot null(getdate())Date and time the record was last updated.

Primary Key

Primary KeyColumns
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentIDEmployeeID, DepartmentID, ShiftID, StartDate

Indexes

IndexTypeColumns
IX_EmployeeDepartmentHistory_DepartmentID DepartmentID
IX_EmployeeDepartmentHistory_ShiftID ShiftID

Check Constraints

Check ConstraintExpressionDescription
CK_EmployeeDepartmentHistory_EndDate(EndDate>=StartDate OR EndDate IS NULL)Check constraint EndDate >= StartDate OR EndDate IS NUL

Foreign Keys

RelationColumnReferenced Column
HumanResources.DepartmentDepartmentIDDepartmentID
HumanResources.EmployeeEmployeeIDEmployeeID
HumanResources.ShiftShiftIDShiftID

References

Dependency TypeObject TypeReferenced Object
SchemaSchemaHumanResources

Dependencies

Reference TypeObject TypeReferencing Object
SelectViewHumanResources.vEmployeeDepartment
SelectViewHumanResources.vEmployeeDepartmentHistory