Trying to understand Northwind Traders' db schema but I cannot comprehend the relationship between:

  • Inventory Transactions<--->Purchase Order details
  • Inventory Transactions<--->Order details

I'm using this PDF as reference Adapting the Access Northwind Database to Support a Database Course

here is the original ERD from MS Access

 original ERD from MS Access

I've used a MySQL port from https://github.com/dalers/mywind in order to highlight the relationships in MySQL Workbench

In specific what blocks my mind are the following questions:

a) Why is there a one-to-many relationship between Inventory Transactions and Purchase Order Details(Fig.1) but not between Inventory Transactions and Order Details(Fig.2), despite the fact that both Purchase Order Details and Order Details tables have an Inventory ID FK that points to Transaction ID field of Inventory Transactions table.

Inventory Transactions<->Purchase Order Details (Fig.1 Inventory Transactions<->Purchase Order Details)

Inventory Transactions<->Order Details (Fig.2 Inventory Transactions<->Order Details)

b) Conceptually how does that make sense? How can we narrate this relationship?

Inventory Transactions have many Purchase Order Details and a Purchase Order Detail belongs to Inventory Transactions?

I cant write a logical sentence using this relationship.

c) It would make more sense if this was a One-to-one relationship. Is it?

d) Why are Inventory Transactions associated with either Purchase Orders or Orders? It seems to me it would make more sense to be associated with Purchase Order Details or Order Details, since a transaction acts on a subset of the order tables (conceptually)

Thank you for your time and if you have more material I can read regarding Northwind Traders db, please share it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.