Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Question Please help me normalize!

    Hello all,

    I am having a very hard time understanding the concept of normalization and would like some help.

    I would like to create a database to store information about employees, customers, suppliers and products. This database will be used for a retail computer store.

    Pretty much these are the attributes I would like to have:

    Employees - number, name, address, telephone, date of birth, sex, date of employment, department, payrate.

    Customers - number, name, address, telephone, fax, email

    Suppliers - number, name, address, telephone, fax, email

    Products - number, category, description, unit price, quantity

    Can you help me to show this data in it's stages from Un normalized up to 3rd normal form?

    I would like to also show that when an invoice is made, a table store that info. as well, so any help as to how i can incorporate this will be appreciated.

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    A good start is to look for repeating groups. Can an employee have more than one department? Can a product have more than one unit price? If so, these would be candidates for separate tables. 2nd normal form would require identification of primary keys on all of your tables. If there are any compound(multi-column) keys, then ask yourself if the values of all of the non-key attributes are dependent upon the ENTIRE key. If there are no compound keys, then 2nd normal form requires no further analysis. 3rd normal form: Ask yourself if there are any attributes that depend upon non-key attributes for their value.(product quantity dependent upon product number?)
    Last edited by urquel; 10-21-05 at 13:33.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Following on from Urquel's excellent advice:-

    consider your table Customers
    currently you have:
    Customers - number, name, address, telephone, fax, email
    can a customer have mor ethan one address
    can a customer have more than one phone number, or fax or email
    if a custmer has multiple contacts, can one contact cover many sub branches
    how would you store details of contacts at that company.
    Is a customer ever also a supplier? Ie do you instead need a single table that contains both customers and suppliers (called say ExternalOrgs)
    do you need to store contracts for that company. if so how many contacts per company address

    i'd suggest you may need the following entities
    Customer
    Customer Addresses
    Customer Contacts

    Take Employees - number, name, address, telephone, date of birth, sex, date of employment, department, payrate.
    Can an enployee have multiple phone numbers or addresses?

    Suppliers - number, name, address, telephone, fax, email

    Take Products - number, category, description, unit price, quantity

    I'm guessing you proabbly need category as a separate table.
    Y0ou may need anothe table to handle discounts applicable (eg case discounts)
    You may need another table to handle units of measuremnts
    You may need to consider packing quantity (eg is the produnct sold singly or by case , pallet, tank etc.

    Rudy (R937), a contributor to these forums has a good example of relational theory which may be wortha look.

  4. #4
    Join Date
    Oct 2005
    Posts
    2
    Thanks for the help guys.

    I think i'll leave out the employee table alltogether since it may just end up confusing my project.

    This will leave me with suppliers, customers and products.

    Thanks alot for the link to Rudy's website, it looks pretty good i'll give it a detailed look tonite.

  5. #5
    Join Date
    May 2005
    Posts
    39
    You can find a very clear explanation of normalisation here:

    http://www.bkent.net/Doc/simple5.htm

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the kind words, healdem

    it may perhaps be worthwhile to mention that
    1. the relational theory article on my site was not written by me
    (it's the only one that isn't)
    2. i'm a former contributor to these forums

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •