HumanResources.EmployeeDepartmentHistory Table

Description

Employee department transfers.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	296
Data Space Used	16.00 KB
Index Space Used	48.00 KB

Columns

Column Name	Description	Datatype	Length	Default
BusinessEntityID	Employee identification number. Foreign key to Employee.BusinessEntityID.	Integer	4
DepartmentID	Department in which the employee worked including currently. Foreign key to Department.DepartmentID.	SmallInt	2
ShiftID	Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.	UnsignedTinyInt	1
StartDate	Date the employee started work in the department.	DBDate	8
EndDate	Date the employee left the department. NULL = Current department.	DBDate	8
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
IX_EmployeeDepartmentHistory_DepartmentID	Nonclustered index.
IX_EmployeeDepartmentHistory_ShiftID	Nonclustered index.
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_EmployeeDepartmentHistory_EndDate	Check constraint [EndDate] >= [StartDate] OR [EndDate] IS NUL	([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Relationships

Relationship	Description
FK_EmployeeDepartmentHistory_Department_DepartmentID	Foreign key constraint referencing Department.DepartmentID.
FK_EmployeeDepartmentHistory_Employee_BusinessEntityID	Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeDepartmentHistory_Shift_ShiftID	Foreign key constraint referencing Shift.ShiftID

Objects that depend on HumanResources.EmployeeDepartmentHistory

	Database Object	Object Type	Description	Dep Level
	HumanResources.vEmployeeDepartment	View	Returns employee name, title, and current department.	1
	HumanResources.vEmployeeDepartmentHistory	View	Returns employee name and current and previous departments.	1

Objects that HumanResources.EmployeeDepartmentHistory depends on

Database Object	Object Type	Description	Dep Level
Person.BusinessEntity	Table	Source of the ID that connects vendors, customers, and employees with address and contact information.	3
HumanResources.Department	Table	Lookup table containing the departments within the Adventure Works Cycles company.	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	1
dbo.Flag	User Defined Data Type		2
dbo.NameStyle	User Defined Data Type		3
Person.Person	Table	Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.	2
HumanResources.Shift	Table	Work 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]

Related Objects