HumanResources.EmployeeDepartmentHistory

Table: 'HumanResources.EmployeeDepartmentHistory'

6 fields. 55 bytes.

Employee department transfers.

Field NameData TypeSizeOriginal TypeDefaultNullableDescriptionCheck
EmployeeIDint   Not nullEmployee identification number. Foreign key to Employee.EmployeeID. 
DepartmentIDsmallint   Not nullDepartment in which the employee worked including currently. Foreign key to Department.DepartmentID. 
ShiftIDtinyint   Not nullIdentifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. 
StartDatedatetime   Not nullDate the employee started work in the department. 
EndDatedatetime   NullDate the employee left the department. NULL = Current department. 
ModifiedDatedatetime  getdate()Not nullDate and time the record was last updated. 

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.EmployeeDepartmentHistory'

Primary Key NameField Names
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentIDEmployeeID, StartDate, DepartmentID, ShiftID

Foreign keys for table 'HumanResources.EmployeeDepartmentHistory', 3 items

ForeignPrimaryKey Name
EmployeeDepartmentHistory.DepartmentIDDepartment.DepartmentIDFK_EmployeeDepartmentHistory_Department_DepartmentID
EmployeeDepartmentHistory.EmployeeIDEmployee.EmployeeIDFK_EmployeeDepartmentHistory_Employee_EmployeeID
EmployeeDepartmentHistory.ShiftIDShift.ShiftIDFK_EmployeeDepartmentHistory_Shift_ShiftID

Indexes for table 'HumanResources.EmployeeDepartmentHistory', 3 items

Index NameDescriptionClusteredUniqueFields
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentIDClustered index created by a primary key constraint.YesYesEmployeeID, StartDate, DepartmentID, ShiftID
IX_EmployeeDepartmentHistory_DepartmentIDNonclustered index.NoNoDepartmentID
IX_EmployeeDepartmentHistory_ShiftIDNonclustered index.NoNoShiftID

Triggers for table 'HumanResources.EmployeeDepartmentHistory', 1 item

NameDescriptionTypeEnabled
uEmployeeDepartmentHistoryAFTER UPDATE trigger setting the ModifiedDate column in the EmployeeDepartmentHistory table to the current date.after Update Yes

Dependencies for table 'HumanResources.EmployeeDepartmentHistory', 4 items

Object NameTypeField Name
HumanResources.CK_EmployeeDepartmentHistory_EndDatecheck cnsN/A
HumanResources.uEmployeeDepartmentHistorytriggerN/A
HumanResources.vEmployeeDepartmentviewN/A
HumanResources.vEmployeeDepartmentHistoryviewN/A