Normalisation

[djkanebo]

I have 2 main tables in my database, seeds and users. There attributes are as follows:

seeds
productID,name,type,organic,colour,moisture,plantingTime,
exposure,season,seedsApprox,price,picture,numOrdered.

users
username,password,userFunctions,firstName,lastName,
emailAddress,secretQuestion,secretAnswer

I have a sales table for every month whose attributes are:
username,(who bought the product),productID,name,dateBought and numBought.

Are any of these tables normalised?

This may sound stupid but I really don't have a clue!

Cheers for any help!

[ahundiak]

Pretty much. Here is a good introduction to normalization.
http://www.phpbuilder.com/columns/barry20000731.php3

[djkanebo]

I have a sales table for every month, does that matter?

[ahundiak]

It really depends on the design goals of the system.
If you have some documentation you can email it and I'll look over it.

Normally, one would just have a sales table. You can select all the entries for a given month quite easily. But having summary tables for individual months can be useful as well.

One question is what happens as the seeds table changes over time? Prices will change. Some products will be added. Some will be dropped. You may need to copy some of that data (price for example) into the sales table if you want to track revenue per product.

[djkanebo]

I have a total of 12 sales tables, one for every month of the year (sale is customer transactions for January and sale12 is transactions for December). This is to compare and contrast the customers buying transactions for every month. If the current month is April, then sale4 is the recent data. All customer transactions before this will be the historic data. So sale, sale2, sale3 is the historic data

So I need 12 Sales tables !!
so are all my tables, the way they stand at the moment normalised????

Thanks

[ahundiak]

No, having multiple sales tables does not violate normalization.

The only "violation" I can see here is that the product name field is duplicated in your sales table.

The userFunctions field is a bit suspicious.

[djkanebo]

So the name of the product does not need to be in the sales tables as the productID is a foreign key?

The userFunctions is for my password system. This can be Normal or Administrator. If the user is normal, they can view the site, process an order etc if the user is of userFunctions Administrator, they can modify the database, view sales etc

So is that o.k?

Cheers

[ahundiak]

Having the name in the sales table violates the 3rd normal form since the product id can be used to pull it from the seeds table.

One reason it can be difficult to give a simple "yes/no" answer to the normalization question is that there are 5 normal forms. A database does not necessarliy have to meet all forms for it to be considered nornalized.

The userFunctions field could be considered a violation of the 4th order of normalization which deals with many to many relationships. A user could probably be either admin, normal or both. The 4th order normal form would require using another table to determine the user type.

[djkanebo]

Is productID called a foreign key?

[ahundiak]

Correct, productID is a foriegn key for the seeds table.

However, productID is also the primary key for the seeds table. Each record in the seed table will have a unique productID? At least that is how I assumed it was being used.

[djkanebo]

Thanks for all your help! I understand now what I have to do!

(At Last)

[djkanebo]

Can I create an ER Model from The tables I have?