Jacob Pressures (4/17/2014)
Hi, Thanks for the response.In the relationship between Payment and Payment_Type tables, aren't the crows feet backwards?
Yes, I missed the typo:w00t:
I see your design i think between Payment and CCD_Details. That is basically a subtype/SuperType. I tried something like that but assumed since i could not create a table with cash maybe that would not be the best design. I like what you did with Payment_Type. I thought of that too. But i never thought of mixing the two.
With Payment and CCD_Details the PaymentID is the primary key in Payment and the primary key in CCD_Details.
It seems as I think this through that CustomerCreditCard will have to have a relationship with CCD_Details with an additional CustomerCreditCardID in the CCD_Details table. So CCD_Details will have CustomerCreditCardID and PaymentID.
CustomerCreditCard and CCD_Details will be one to many. a CreditCard have have many Details or Payments but the Details can refer to only one Credit Card.
Could the CCD_Details table be changed to CCD_Payments?
Let me know. But i think this will work.
Think of the CCD_Detail as an attribute of a Payment, if the Payment is of a type Credit Card, then it holds the relevant details for this instance, such as Authorization details.
To further on the schema, look at these questions:
1. Are all Customers registered prior placing an Order?
2. Are there many Methods of Payment? (Payment_Type)
3. Can a Customer have more than one active Method of Payment?
4. Do Customers register a method of payment once and use it many times?
5. Are AdHoc payments allowed?
6. Are all payments for a single Order in the same Currency?
7. Does a Customer hold one or many Credit Cards?
8. Do Customers share Credit Cards?
9. Can an Order have more than on payment?
10. Can Order Payments be of a different type for a single Order?
11. Does an Order contain more than one Product?
12. Can a Customer pay another Customer's Order?
Here is another ERD, slightly more elaborate than the first on, still somewhat speculative 😎
+-------------+ +--------------------+ +---------------+
| Customer |-||-,, | CustomerCreditCard | | CreditCard |
+-------------+ || +--------------------+ +---------------+
| Customer_ID | || | CreditCard_ID |>-|-----||-| CreditCard_ID |
|(Details) | |'---o<| Customer_ID | | (Details) |
+-------------+ | | (Details) | +---------------+
| +--------------------+
|
| +----------------+ +----------------+ +----------------+
| | Order (Header) | | Payment | | Payment_Type |
| +----------------+ +----------------+ +----------------+
| | Order_ID |-||-,, |Payment_ID | ,-||-| PaymentType_ID |
'----o<| Customer_ID | || |Payment_Type_ID |>|--' | (details) |
| (Details) | |'---o<|Order_Id | +----------------+
+----------------+ | |(details) |-|--, +---------------+
| +----------------+ | | CCD_Detail |
| | +---------------+
| +----------------+ '--o-| Payment_ID |
| | OrderDetail | | CreditCard_ID |
| +----------------+ | (details) |
'--|-<| Order_ID | +---------------+
| OrderDetail_ID |
| (Details) |
+----------------+
Notes on the ERD:
The CustomerCreditCard table is redundant if:
1. Customers do not share Credit Cards or
2. Customer_ID and Credit Card Number form a combined key in the CreditCard table.
The Order table has the header type information, such as Order Number and Dates.
The Payment table has the monetary details of all payments towards each Order.
The OrderDetail has all products or items of the Order, including Price and Quantity.
The Payment_Type is the method used for each payment.
Hope this helps and that I'm not confusing the issue 😀