Northwind Database (Document! X Sample)
HumanResources.EmployeeDepartmentHistory Table
AdventureWorks Database > HumanResources Schema : HumanResources.EmployeeDepartmentHistory Table
Description
Employee department transfers.
Properties
Creation Date27/10/2017 14:33
File GroupPRIMARY
Text File Group
System Object
Published for Replication
Rows296
Data Space Used16.00 KB
Index Space Used48.00 KB
Columns
 Column NameDescriptionDatatypeLengthAllow NullsDefaultFormula
Primary Key Employee identification number. Foreign key to Employee.BusinessEntityID.Integer4   
Primary Key Department in which the employee worked including currently. Foreign key to Department.DepartmentID.SmallInt2   
Primary Key Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.UnsignedTinyInt1   
Primary Key Date the employee started work in the department.DBDate8   
 Date the employee left the department. NULL = Current department.DBDate8  
 Date and time the record was last updated.DBTimeStamp4 
(getdate())
 
Indexes
IndexDescriptionPrimaryUnique
Nonclustered index.  
Nonclustered index.  
Primary key (clustered) constraint
Check Constraints
NameDescriptionExpression
Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NUL
([EndDate]>=[StartDate] OR [EndDate] IS NULL)
Relationships
RelationshipDescription
Foreign key constraint referencing Department.DepartmentID.
Foreign key constraint referencing Employee.EmployeeID.
Foreign key constraint referencing Shift.ShiftID
Objects that depend on HumanResources.EmployeeDepartmentHistory
 Database ObjectObject TypeDescriptionDep Level
HumanResources.vEmployeeDepartment viewHumanResources.vEmployeeDepartmentViewReturns employee name, title, and current department.1
HumanResources.vEmployeeDepartmentHistory viewHumanResources.vEmployeeDepartmentHistoryViewReturns employee name and current and previous departments.1
Objects that HumanResources.EmployeeDepartmentHistory depends on
 Database ObjectObject TypeDescriptionDep Level
Person.BusinessEntity tablePerson.BusinessEntityTableSource of the ID that connects vendors, customers, and employees with address and contact information.3
HumanResources.Department tableHumanResources.DepartmentTableLookup table containing the departments within the Adventure Works Cycles company.1
HumanResources.Employee tableHumanResources.EmployeeTableEmployee information such as salary, department, and title.1
dbo.Flag datatypedbo.FlagUser Defined Data Type 2
dbo.NameStyle datatypedbo.NameStyleUser Defined Data Type 3
Person.Person tablePerson.PersonTableHuman beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.2
HumanResources.Shift tableHumanResources.ShiftTableWork shift lookup table.1
SQL
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[EmployeeDepartmentHistory](
    [BusinessEntityID] [int] NOT NULL,
    [DepartmentID] [smallint] NOT NULL,
    [ShiftID] [tinyint] NOT NULL,
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] PRIMARY KEY CLUSTERED 
(
    [BusinessEntityID] ASC,
    [StartDate] ASC,
    [DepartmentID] ASC,
    [ShiftID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] ADD  CONSTRAINT [DF_EmployeeDepartmentHistory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID] FOREIGN KEY([DepartmentID])
REFERENCES [HumanResources].[Department] ([DepartmentID])
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [HumanResources].[Employee] ([BusinessEntityID])
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_BusinessEntityID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]  WITH CHECK ADD  CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID] FOREIGN KEY([ShiftID])
REFERENCES [HumanResources].[Shift] ([ShiftID])
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID]
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory]  WITH CHECK ADD  CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK  (([EndDate]>=[StartDate] OR [EndDate] IS NULL))
ALTER TABLE [HumanResources].[EmployeeDepartmentHistory] CHECK CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate]
See Also

Related Objects

HumanResources Schema
AdventureWorks Database