Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

CUSTOMER:

  • CustomerID (PK)
  • Name
  • Email

PAYMENT:

  • PaymentID (PK)
  • Amount
  • Date

PRODUCT:

  • ProductID (PK)
  • Type
  • Name
  • Price

PURCHASE:

  • CustomerID (FK)
  • PaymentID (FK)
  • ProductID (FK)
  • Quantity

Or should I move CustomerID in PURCHASE to PAYMENT or else?

share|improve this question

2 Answers

up vote 4 down vote accepted

If it were me, I would make two changes:

1) Create a "PurchaseID" field on the "Purchase" table (which can double as an order number for your customers to reference).

2) Move "Quantity" to a new table called "Purchase_Line" for each line (item) on the order (purchase). Otherwise, you're going to repeat the CustomerID and PaymentID for each ProductID included on every order. This way your customers can order as many products as they want, but their order only appears in the "Purchase" table once.

Otherwise, I think your normalization looks very complete.

share|improve this answer
Well you suggest I shoudl have PURCHASE: PurchaseID, PaymentID, ProductID? PURCHASE_LINE: PurchaseID, Quantity? – Modular Dec 25 '11 at 4:17
Well I managed to move ClientID from PURCHASE to PAYMENT table by the way. Then only 2 FKs left in PURCHASE: PaymentID and ProductID – Modular Dec 25 '11 at 4:18
I work on an e-commerce system and this is (kind of) how we've designed our database as well. Seems like The Right Way(tm) of doing it. +1 – Ryan Bigg Dec 25 '11 at 4:38
Why would you create a surrogate key (purchaseID) when you have a sufficient natural key (all 3 foreign key references on the table)??? – Thomas Stringer Dec 25 '11 at 15:11
@Shark- From a data modeling perspective you are correct in that the key could be a composite of all 3 FK's. But when problems happen, which would you rather have your customer reading back to you over the phone? A 24-digit number made up of three 8-digit numbers? Or single 8 digit number? That's why (I feel) a surrogate key makes sense here. – BryceAtNetwork23 Dec 25 '11 at 16:39
show 1 more comment
up vote 1 down vote

If you were to use the key as a verbal reference then yes, I agree with you. But I had assumed that it would just be a unique identifier for the data.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.