Hi, I am new to the whole database and normalizing thing and I am working on an assignment. I was wondering if someone could take a look at my tables and let me know if I am missing fields, records, more tables, or have errors or if my primary key is wrong
I also wanted to know if this database is in true 3NF, it looks good to me, but being new to it I might be missing something

Customer Table
customerID (Primary Key)
customerFirstName
customerLastName
customerAddress
zipCode

Zip Code Table
zipCode (Primary Key)
city
state

Customer Telephone Number Table
customerID (Primary Key)
custTelephoneNumber (Primary Key)
custTelephoneNumberDescription

Product Table
productID (Primary Key)
productName
productPrice
productQuantityAvailable
productDescription

Product Re-Order Table
productID (Primary Key)
productQuantityAvailable (Primary Key)
reorderLevel
reorderQuantity

Supplier Table
supplierID (Primary Key)
supplierName
supplierAddress
zipCode

Supplier Telephone Number Table
supplierID (Primary Key)
suppTelephoneNumber (Primary Key)
suppTelephoneNumberDescription

Employee Table
employeeID (Primary Key)
employeeFirstName
employeeLastName
employeeAddress
zipCode

Employee Telephone Number Table
employeeID (Primary Key)
empTelephoneNumber (Primary Key)
empTelephoneNumberDescription

I put separate telephone number tables in, because my professor said that if a customer, etc. has more than one telephone number (work, home, cell) i couldn't list all of them in the customer table.

I have three more tables that I am trying to figure out, a purchase table, a payment table, and a payment method table
but I am getting a little confused trying to get them together. I am not sure if they are correct. The purchase table has so much information in it.
And what if a customer buys more than one product, how do I fit that into the purchase table?
I can't put more than one productID, so I am confused on that.
I am trying to work out the payment table and I am having trouble with it. Like what if the customer pays with half cash and half credit card?
any input would be very helpful

Purchase Table
purchaseID (Primary Key)
customerID
employeeID
productID
paymentID
datePurchased
quantityPurchased

Payment Method Table
paymentMethodID (Primary Key)
paymentMethodDescription

Payment Table
paymentID (Primary Key)
customerID
purchaseID
paymentMethodID
amount

Any help would be greatly appreciated
Thanks a lot,
Brian

Telephone-numbers are often a problem and there's no easy answer to them. Many systems only store two or three, using separate columns. But inevitably some customer comes along who's sprouted cell-phones from every extremity and demands to be called on each one.



Usually a "purchase" is done with two tables: one for the order itself and one for the individual line-items that make up the order or invoice. Each line-item refers to the order-ID and contains a second, sequential line-number field as part of its key. This also allows the items to appear, on a printed order, in the same sequence they were entered.

In an accounting system, payments are handled by "credit memos" that are created when the customer pays the invoice or is issued a credit. These credit-memos are applied to the open invoices. It can, ahh, get messy.

But you're thinking along the right lines. I suggest strongly that you discuss your issues and concerns with your professor now, because he/she will be delighted to have a student who's obviously thinking about and working on the problem. You're very likely to see something related to your comments brought up by the instructor in a future class. (That's a breath of fresh air to an instructor.)

I changed a few things and still want to know if this database looks good. Does anyone notice any mistakes or things missing like primary keys and more fields? Did I do 3rd normal form correctly?
I have a few fields in two of the tables that I am unsure of whether or not they go there, so any suggestions with those would be great

here is what i've changed, any help would be appreciated

Product Status Table
productID (Primary Key)
productQuantityAvailable (Primary Key)
reorderLevel
reorderQuantity
supplierID (not sure if i should put this here)
productUnitsOnOrder (not sure if i should put this here)

Purchase Table
purchaseID (Primary Key)
customerID
employeeID
datePurchased

Purchase Detail Table
purchaseID (Primary Key)
purchaseDetailID (not sure about this)
productID
quantityPurchased
productPrice

Payment Method Table
paymentMethodID (Primary Key)
paymentMethodDescription

Payment Table
paymentID (Primary Key)
purchaseID
paymentMethodID
paymentDate
amountPaid

do these tables along with other tables look ok (3nf, proper keys, etc.)

thanks again
brian

LOL
I just put all of those telephone tables in, because you are so right about what you said.

Ok, i see what you're saying...i split the purchase into 2 tables, see my above post
I have one for the actual purchase and the other one for the purchase details. It looks ok, but I am still not 100% sure. The second line-number field for the other part of the key you talked about, is that just another field i should put in like a purchaseDetailID or something?

yuck
In my post above I thought I might of had something with the payment method table and payment table
I will definitely see the professor about that then as to avoid going crazy

Thank you for your time and help sundialsvcs
Brian