How to resolve convoluted Entity Relations in Adventureworks 2019

Question

The SalesTerritory table has a one-to-many to SalesOrderHeader using the TerritoryID.

But, also has a one-to-many to SalesPerson.

SalesPerson has a one-to-many, also to SalesOrderHeader using TerritoryID, yet at first glance, that would create a many-to-many relationship between SalesOrderHeader and SalesTerritory, using the same key as the one-to-many from SalesTerritory to SalesOrderHeader.

This seems confusing and convoluted to me, especially when trying to create a select query, with joins.!?

Do these relationships make sense to anyone else ?

Answer 1

I don't know why it's been set up this way, but I can speak to my experience working with real life data that looks very similar to this.

An important thing to keep in mind is that an order happens at a point-in-time, but a master data table like SalesTerritory and SalesPerson, in this case, have no temporal attribute to them. They are current cuts of Territory and SalesPerson.

Also, the relationship between a SalesPerson and a SalesTerritory changes over time as well. People leave the company and new people join, taking over a Territory.

Also, the relationship between a customer and its territory changes over time. Customers are realigned to different territories as the company checks-and-adjusts its coverage.

---

So a relationship between an order and the person responsible for the territory for the customer that placed the order has three relationships that change over time, yet the master data tables only hold the current relationship. Given this, where would one hold the point in time relationship when the order was placed? We have two options:

1. Master data tables that hold a temporal element, perhaps in the form of validFrom and validTo dates.

2. Stamp the Territory and SalesPerson onto the order at the time it's placed.

Number 1 is more elegant, in my opinion, BUT it has a HUGE downside, especially for a reporting platform: The Joins are difficult and expensive to run. They are also confusing for end users that might be running ad-hoc analytics on this data.

Number 2, on the other hand, allows a simple join to current master data tables to retrieve the current attributes about that entity (customer, territory, salesperson). BUT it has a downside as well: Those attributes are current, and may have not been true at the time the order was placed.

---

Because these relationships are temporal and the master data tables are current only, you'll likely find different SalesPerson and SalesTerritories when you do:

SalesOrderHeader -> SalesPerson

vs

SalesOrderHeader -> SalesTerritory -> SalesPerson

vs

SalesOrderHeader -> Customer -> SalesTerritory -> SalesPerson

vs

SalesOrderHeader -> Customer -> SalesTerritory -> SalesTerritoryHistory -> SalesPerson

Especially as you query older and older SalesOrderHeader records.

Each of these are correct ways to query the data, but they answer different questions.