Search code examples
sqlsql-serverdatabasedata-modelingadventureworks

Historical Data Modeling

In AdventureWorks2008R2 the Sales.SalesPerson table contains a TerritoryID which creates an easy reference to the current Territory assigned to a SalesPerson. The Sales.SalesTerritoryHistory table is also available to analyze past assignments.

I noticed the HumanResources.EmployeeDepartmentHistory follows a similar pattern; however, the HumanResources.Employee table does not have a direct reference to the current Department. In other words, there is no DepartmentID on the HumanResources.Employee table.

Is there a good reason why they wouldn't follow the same pattern?

Solution

  • It's most likely the employee can work in multiple departments at the same time and that's why it is set up that way