hey guys i have to create 3 tables for a hardware store db. i came up with this layout for the tables. can you look over them to see what im doing wrong and what i can change to make the db better.

customers:
customerID
customerName
customerAddress
etc.

inventory
productID
productName
productType
productSize
etc.

(wasnt to sure how to set this table up Normalized)
purchaces
orderID
productID
CustomerID

thanks for your time

is this a school project or a live project ?

are you just trying to build something, or are you trying to build something that is completely robust and is going to really be used ?

your "purchases" table
are you planning to stick in all of the times that were purchased, or is that just a cross reference to a 4th table with all of the individual items contained in each sale ?

and.. if you really want a critique.. then include everyting.. not "etc, etc." 's

help with my database

--------------------------------------------------------------------------------

hey guys i have to create 3 tables for a hardware store db. i came up with this layout for the tables. can you look over them to see what im doing wrong and what i can change to make the db better.

customers:
customerID
customerName
customerAddress
etc.

inventory
productID
productName
productType
productSize
etc.

(wasnt to sure how to set this table up Normalized)
purchaces
orderID
productID
CustomerID

thanks for your time

One thing I may suggest is trying to make a "Purchase Table" that contains just the Purchase Header Information, then I would set up another table for the Purchase Details that contains all the items associated with the purchase but contains a forgien key relating it to the "Purchase Table" record.

Something that may be useful for you is to look at the Northwind Sample Database that comes with SQL Server as well as Access I believe. Hope this helps.

this is for a school project nothing major. for the purchase table, im not to sure what to do guys. you both seem to be leaning towards making a 4th table. what would this 4th table contain? all the purchases?

Maybe you can make you two extra Purchase tables look something like this:

"Purchase Header"

OrderID
CustomerID
OrderDate
ShippedDate
etc....


"Purchase Detail"

OrderID
ProductID
UnitPrice
Quantity
etc....

This format of laying out the tables allows you to have as little redundant data as possible, which is what normalization of db tables is used for. What the fourth table contains is just the details of the purchase. The reason for the fourth table is so you do not have to repeat the order heading information for every product that is sold in the order.

Sorry for the repost

ok so i see what your saying with adding the 2 tables u have shown me. should i remove the original purchase table that i drew up and insert the 2 that you gave me. or should i leave mine and add the other 2?
by the way, thanks for the help

I would get rid of yours and put the other two in place. With the two I gave you you do not need the one you have.

thanks a lot for the help its appreciated

yep
thats what i was leaning towards... what Trent showed.

something i would add though.
"Purchase Detail"

OrderID
OrderItemCounter
ProductID
UnitPrice
Quantity
etc....

OrderItemCounter is the original order they appear in on the receipt or order. it may be important if you need to recal the 5th item in a list of 30 and do something with it.