none
WideWorldImporters - design criteria? RRS feed

  • Question

  • The schema in WideWorldImporters does not seem to be normalised.

    What is the purpose in having a sample DB that is not normalised?


    Ken Evans

    Friday, August 24, 2018 8:28 PM

All replies

  • Perhaps you could provide some evidence for your opinion? 
    Friday, August 24, 2018 11:35 PM
  • Take one example: The "Suppliers" table in the "Purchasing" schema.

    It has:
    * two sets of columns for address data.
    * bank account data mixed in with address data.

    Other parts of the DB have similar issues.

    Do you need any more evidence?

     



    Ken Evans

    Tuesday, August 28, 2018 1:48 PM
  • I assumed that by "evidence" you meant "evidence that the DB is not normalised".

    What you have called my "opinion" is easy to check by anyone who understands normalisation.

    So, as a first step, do you agree that the database is not normalised?


    Ken Evans

    Tuesday, August 28, 2018 2:32 PM
  • While I have might have arranged the columns in differnt order, column order has absolutely nothing to do with normalisation. One reason for the disorder, is that some columns have been added later.

    You have a slightly better case for the addresses, but only slightly. If a supplier could have many addresses, it would be better to have these in a subtable, and then use flags for "main delivery address" and "main postal address". However, as long as there can only be two of them, it is certainly easier to have two set of columns for each.

    By the way, the schema you reproduce above is quite different from how the table looks like in my copy of WideWorldImporters. Don't know if there are different versions, but decimal for the ids looks funny.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, August 28, 2018 9:54 PM
  • Yes - I was not referring to column order - which as you say is not relevant to normalisation.

    And yes, I'm using a different tool to view the tables and some datatypes were changed in the import process.   However, using different datatypes does not affect normalisation.

    As far as I know, the concept of "subtable" is not within the scope of normalisation theory.
    A DB is either normalised to a specific form (1,2,3,4 or5) or it isn't.

    Here is an example that is based on part of the suppliers table. (5NF) 


    Ken Evans

    Wednesday, August 29, 2018 12:09 PM
  • Yes, you can put addresses in a separate table. This is particularly a good idea if addresses be shared by several entities and a change in the address should affect all entities. In fact, we do this in a system I have worked with for many years.

    But if the business rules says that a Supplier can have at most two addresses, and the address is not shared with other suppliers, there is breach against normalisation to have them as columns in the Suppliers tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 29, 2018 10:03 PM
  • Normalization is not about whether you "can" put things in different tables and whether it is a "good idea" or not.

    The problems of unnormalized data schemas relate to risks such as the risk of creating an inconsistent database.

    My example relates to just one table.
    However, if you look across the complete database, there are many other examples that make it easy to see that the database is not normalised.

    So my question remains: What is the purpose in publishing a sample database that is not normalised?

    =====

    Here is one of the many references to normalization.


     

    Ken Evans

    Thursday, August 30, 2018 6:52 PM