Table: HumanResources.EmployeeDepartmentHistory

Properties

Owner:	HumanResources
Creation Date:	04/26/2006
Located On:	PRIMARY
Data Size KB:	16
Index Size KB:	48
Rows:	296
Description:	Employee department transfers.

Columns

Name	Data Type	Length	NULL	Default	IsIdentity	IsGUID	Description
EmployeeID	int	4					Employee identification number. Foreign key to Employee.EmployeeID.
DepartmentID	smallint	2					Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
ShiftID	tinyint	1					Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
StartDate	datetime	8					Date the employee started work in the department.
EndDate	datetime	8					Date the employee left the department. NULL = Current department.
ModifiedDate	datetime	8		(getdate())			Date and time the record was last updated.

Total: 6 column(s)

Indexes

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

Total: 3 index(es)

Check Constraints

	Name	Expression
	CK_EmployeeDepartmentHistory_EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Total: 1 constraint(s)

Referenced Tables

Table	Foreign Key	Primary Key or Unique Constraint
HumanResources.Department	FK_EmployeeDepartmentHistory_Department_DepartmentID	PK_Department_DepartmentID
HumanResources.Employee	FK_EmployeeDepartmentHistory_Employee_EmployeeID	PK_Employee_EmployeeID
HumanResources.Shift	FK_EmployeeDepartmentHistory_Shift_ShiftID	PK_Shift_ShiftID

Total: 3 table(s)

Objects that [HumanResources].[EmployeeDepartmentHistory] depends on

Object Name	Owner	Object Type	Dep Level
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
NameStyle	dbo	User Defined type	1
Phone	dbo	User Defined type	1
Contact	Person	Table	2
Department	HumanResources	Table	2
Shift	HumanResources	Table	2
Employee	HumanResources	Table	3

Total: 8 object(s)

Objects that depend on [HumanResources].[EmployeeDepartmentHistory]

Object Name	Owner	Object Type	Dep Level
vEmployeeDepartment	HumanResources	View	1
vEmployeeDepartmentHistory	HumanResources	View	1

Total: 2 object(s)

SQL

CREATE TABLE [EmployeeDepartmentHistory] (
    [EmployeeID] [int] NOT NULL ,
    [DepartmentID] [smallint] NOT NULL ,
    [ShiftID] [tinyint] NOT NULL ,
    [StartDate] [datetime] NOT NULL ,
    [EndDate] [datetime] NULL ,
    [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeeDepartmentHistory_ModifiedDate] DEFAULT (getdate()),
    CONSTRAINT [PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID] PRIMARY KEY  CLUSTERED
    (
        [EmployeeID],
        [StartDate],
        [DepartmentID],
        [ShiftID]
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID] FOREIGN KEY
    (
        [DepartmentID]
    ) REFERENCES [Department] (
        [DepartmentID]
    ),
    CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_EmployeeID] FOREIGN KEY
    (
        [EmployeeID]
    ) REFERENCES [Employee] (
        [EmployeeID]
    ),
    CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID] FOREIGN KEY
    (
        [ShiftID]
    ) REFERENCES [Shift] (
        [ShiftID]
    ),
    CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL)
) ON [PRIMARY]
GO