Adventure work Explanation

Question

I looked at the Microsoft Adventure Works 2012 database. I would be very interested if there is information explaining why the tables were created the way they are. It seems to me something like a circuit.

For example:

Why did they decide to create a BusinessEntity table as a kind of base class for Person, Employee, etc.

Most of the data is normalized, so they decided to put the CountryRegionCode field in the CountryRegionCode table instead of the identifier in a separate table.

In any case, I am very interested in learning more about the solutions that were part of the database design. Does anyone know the resource that goes into this thing?

Answer 1

I don't know any official design documentation for AdventureWorks, but I use trainers and extensively use AdventureWorks databases for demos and labs, so I am familiar with it.

The BusinessEntity table is a classic example of a SuperType / SubType design that reduces data redundancy because customers can also become suppliers, employees can become customers and any other combination. In addition, this means that you do not save the details associated with all objects several times in separate tables, minimizing the effort in case of code changes.

CountryRegionCode Code I'm not sure, but I suspect one of three reasons:

• There are not enough individual combinations to provide an additional table due to reporting performance (this could be verified using some simple COUNT (*) GROUP BY statements)
• They wanted it in the same table, so that in the future they had the flexibility to model the hierarchical structure using a hierarchical identifier (this is the least similar option)
• It was a normalization mistake! (My money for this option!)

Answer 2

This image helped me, although technically this is for 2008.

Answer 3

Here is the link to fill the bit in the background:

https://technet.microsoft.com/en-us/library/ms124825(v=sql.100).aspx