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.

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?)

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.

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.

You can find a very clear explanation of normalisation here:

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

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