AdventureWorks
| Table Name: | HumanResources.EmployeeDepartmentHistory |
| Description: | Employee department transfers. |
| Primary Keys: | EmployeeID StartDate DepartmentID ShiftID |
| Field | Type | Defaults | Nulls? | Comments |
|---|---|---|---|---|
| EmployeeID | int | None | No | Employee identification number. Foreign key to Employee.EmployeeID. |
| DepartmentID | smallint | None | No | Department in which the employee worked including currently. Foreign key to Department.DepartmentID. |
| ShiftID | tinyint | None | No | Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID. |
| StartDate | datetime | None | No | Date the employee started work in the department. |
| EndDate | datetime | None | Yes | Date the employee left the department. NULL = Current department. |
| ModifiedDate | datetime | (getdate()) | No | Date and time the record was last updated. |
| Index | Clustered? | Unique? | Fields |
|---|---|---|---|
| PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID | Yes | Yes | EmployeeID StartDate DepartmentID ShiftID |
| IX_EmployeeDepartmentHistory_DepartmentID | No | No | DepartmentID |
| IX_EmployeeDepartmentHistory_ShiftID | No | No | ShiftID |
| Check Constraint | Text |
|---|---|
| CK_EmployeeDepartmentHistory_EndDate | ([EndDate]>=[StartDate] OR [EndDate] IS NULL) |
| Internal Foreign Key Constraint | Affected Field | Source Table |
|---|---|---|
| FK_EmployeeDepartmentHistory_Department_DepartmentID | DepartmentID | HumanResources.Department |
| FK_EmployeeDepartmentHistory_Employee_EmployeeID | EmployeeID | HumanResources.Employee |
| FK_EmployeeDepartmentHistory_Shift_ShiftID | ShiftID | HumanResources.Shift |