principleMinor
Is there any better approach in designing this database?
Viewed 0 times
thisanydesigningbetterdatabasethereapproach
Problem
CUSTOMER:
PAYMENT:
PRODUCT:
PURCHASE:
Or should I move CustomerID in PURCHASE to PAYMENT or else?
- CustomerID (PK)
- Name
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#9713, answer score: 4
Revisions (0)
No revisions yet.