HumanResources.EmployeeDepartmentHistory

Table: 'HumanResources.EmployeeDepartmentHistory'

6 fields. 55 bytes.

Employee department transfers.

Field Name Data Type Size Original Type Default Nullable Description Check

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 getdate() Not null Date and time the record was last updated.

Field Name	Data Type	Default	Nullable	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	getdate()	Not null	Date and time the record was last updated.

Extended Properties

The object has no extended properties.

Primary key for table 'HumanResources.EmployeeDepartmentHistory'

Primary Key Name Field Names

PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID EmployeeID, StartDate, DepartmentID, ShiftID

Primary Key Name	Field Names
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	EmployeeID, StartDate, DepartmentID, ShiftID

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

Foreign Primary Key Name

EmployeeDepartmentHistory.DepartmentID Department.DepartmentID FK_EmployeeDepartmentHistory_Department_DepartmentID

EmployeeDepartmentHistory.EmployeeID Employee.EmployeeID FK_EmployeeDepartmentHistory_Employee_EmployeeID

EmployeeDepartmentHistory.ShiftID Shift.ShiftID FK_EmployeeDepartmentHistory_Shift_ShiftID

Foreign	Primary	Key Name
EmployeeDepartmentHistory.DepartmentID	Department.DepartmentID	FK_EmployeeDepartmentHistory_Department_DepartmentID
EmployeeDepartmentHistory.EmployeeID	Employee.EmployeeID	FK_EmployeeDepartmentHistory_Employee_EmployeeID
EmployeeDepartmentHistory.ShiftID	Shift.ShiftID	FK_EmployeeDepartmentHistory_Shift_ShiftID

Indexes for table 'HumanResources.EmployeeDepartmentHistory', 3 items

Index Name Description Clustered Unique Fields

PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID Clustered index created by a primary key constraint. Yes Yes EmployeeID, StartDate, DepartmentID, ShiftID

IX_EmployeeDepartmentHistory_DepartmentID Nonclustered index. No No DepartmentID

IX_EmployeeDepartmentHistory_ShiftID Nonclustered index. No No ShiftID

Index Name	Description	Clustered	Unique	Fields
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	Clustered index created by a primary key constraint.	Yes	Yes	EmployeeID, StartDate, DepartmentID, ShiftID
IX_EmployeeDepartmentHistory_DepartmentID	Nonclustered index.	No	No	DepartmentID
IX_EmployeeDepartmentHistory_ShiftID	Nonclustered index.	No	No	ShiftID

Triggers for table 'HumanResources.EmployeeDepartmentHistory', 1 item

Name Description Type Enabled

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

Name	Description	Type	Enabled
uEmployeeDepartmentHistory	AFTER 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 Name Type Field Name

HumanResources.CK_EmployeeDepartmentHistory_EndDate check cns N/A

HumanResources.uEmployeeDepartmentHistory trigger N/A

HumanResources.vEmployeeDepartment view N/A

HumanResources.vEmployeeDepartmentHistory view N/A

Object Name	Type	Field Name
HumanResources.CK_EmployeeDepartmentHistory_EndDate	check cns	N/A
HumanResources.uEmployeeDepartmentHistory	trigger	N/A
HumanResources.vEmployeeDepartment	view	N/A
HumanResources.vEmployeeDepartmentHistory	view	N/A