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


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


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 
  Name  dbo  User Defined type 
  NameStyle  dbo  User Defined type 
  Phone  dbo  User Defined type 
  Contact  Person  Table 
  Department  HumanResources  Table 
  Shift  HumanResources  Table 
  Employee  HumanResources  Table 
Total: 8 object(s)

Objects that depend on [HumanResources].[EmployeeDepartmentHistory]

Object Name Owner Object Type Dep Level
  vEmployeeDepartment  HumanResources  View 
  vEmployeeDepartmentHistory  HumanResources  View 
Total: 2 object(s)


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
    )  ON [PRIMARY] ,
    CONSTRAINT [FK_EmployeeDepartmentHistory_Department_DepartmentID] FOREIGN KEY
    ) REFERENCES [Department] (
    CONSTRAINT [FK_EmployeeDepartmentHistory_Employee_EmployeeID] FOREIGN KEY
    ) REFERENCES [Employee] (
    CONSTRAINT [FK_EmployeeDepartmentHistory_Shift_ShiftID] FOREIGN KEY
    ) REFERENCES [Shift] (
    CONSTRAINT [CK_EmployeeDepartmentHistory_EndDate] CHECK ([EndDate]>=[StartDate] OR [EndDate] IS NULL)

See Also

List of tables