The AdventureWorks Database Diagram Sucks!
Hi Architects and Data Modelers,
Although this database may be big leap forward from the Northwind database, how much thought (do you think) was put into the schema
1. Who was the consulting company that created this
2. Does it follow all of the rules of normalization
3. Which normalization rules does it break
4. Do you like how the natural keys were derived
5. Do you wish they used more surrogate or natural keys
6. Do you like their choice of modeling notation
7. Have you found any differences in the downloadable HTML schema and the real database
While perusing the diagram, I noticed many possible design flaws.
The diagram is available at:
http://www.microsoft.com/downloads/details.aspx familyid=0f6e0bcf-a1b5-4760-8d79-67970f93d5ff&displaylang=en
I picked four examples in which you can share your thoughts below:
Example 1:
The diagram shows a many-to-many relationship between [Person.Address] and [Sales.Customer] resolved by [Sales.CustomerAddress].
But the data only shows a one-to-many relationship between [Sales.Customer] and [Person.Address], respectively.
Considering the data, is the junction table [Sales.CustomerAddress] necessary
Example 2:
The diagram shows a many-to-many relationship between [Person.Address] and [Purchasing.Vendor] resolved by [Purchasing.VendorAddress].
But the data only shows a one-to-one relationship between [Purchasing.Vendor] and [Person.Address].
Considering the data, is the junction table [Purchasing.VendorAddress] necessary
Example 3:
The [Person.AddressType] table is a lookup table shared between [Sales.CustomerAddress] and [Purchasing.VendorAddress]. If all address types are not mutually inclusive, is there a problem with this design
Example 4: - ouch!
The diagram shows a many-to-many relationship between [Sales.SalesTerritory] and [Sales.SalesPerson] resolved by the junction table [Sales.SalesTerritoryHistory].
But then it also displays a one-to-many relationship between [Sales.SalesTerritory] and [Sales.SalesPerson], respectively.
Note there is a record in the [Sales.SalesPerson] table that shows SalesPersonID = 285 belongs to TerritoryID = 10, but the [Sales.SalesTerritoryHistory] table shows no record of this.
Is this suppose to be an example of a real-life database including data integrity issues
The AdventureWorks Database Diagram Sucks!
Stefaan Meeuws
This is not a question about modeling really...
rather a SQL/dbms one.
Please use this forum instead.
Hope this helps.
Regards
Mauro Regio