none
Adventureworks Database Design Queries RRS feed

  • Question

  • Hi ,

    This was raised by someone in another forum but not answered.
    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?

    Friday, March 18, 2011 5:31 AM

All replies

  • Sorry for the lazy reply, but I'm not studying all that! But...

    1. Who was the consulting company that created this? - I doubt there was, this example started from humble beginnings to show off web technology but perhaps it was handed over
    2. Does it follow all of the rules of normalization? - Sorry I'm not checking, but how would you know if it's, "broken the rule" on purpose or not?
    3. Which normalization rules does it break? - (see 2)
    4. Do you like how the natural keys were derived? Perhaps I've misunderstood, but are there any natural keys there?
    5. Do you wish they used more surrogate or natural keys? They're all surrogate keys aren't they?
    6. Do you like their choice of modeling notation? I like the partitioning, that's quite nice. Hate the arrow, I kept reading that the wrong way around...ah they're the direction of the relationship not the multiplicity, ok.
    7. Have you found any differences in the downloadable HTML schema and the real database? Sorry, lazy excuse but not checked.

    As for the specific reasons for the schema I can't really comment because I don't know what it's supposed to do. This is one of the major drawbacks of just producing an entity diagram and hoping it conveys what the system should do.


    http://pauliom.wordpress.com
    Friday, March 18, 2011 9:51 AM
  • I think it's just supposed to be a database that MS can build example code against.

    I've never seen anything which claimed it was supposed to be a shining example of database design.

    It could well be that parts ( or indeeed all ) are deliberately designed in such a way to allow someone to write an example easily.

    Friday, March 18, 2011 1:57 PM