April 16, 2014 at 9:37 pm
I have the following tables:
Customer,
Customer Credit Card,
Credit Card
Order
But a customer can also pay with cash.
Should I have a payment or transaction table which allow null IDs for (credit Card ID) foreign key in that table?
Would i need a Customer ID in the Transaction table to ensure an association between the payment and the customer especially when no credit card is used for cash transactions? Or is there a better design?
Transaction table
TransactionID
OrderID
Credit Card ID
Cash (bit)
Amount
April 17, 2014 at 12:31 am
Jacob Pressures (4/16/2014)
I have the following tables:Customer,
Customer Credit Card,
Credit Card
Order
But a customer can also pay with cash.
Should I have a payment or transaction table which allow null IDs for (credit Card ID) foreign key in that table?
Would i need a Customer ID in the Transaction table to ensure an association between the payment and the customer especially when no credit card is used for cash transactions? Or is there a better design?
Transaction table
TransactionID
OrderID
Credit Card ID
Cash (bit)
Amount
A fairly standard schema, which allows for multiple payments of different types for each transaction, would probably do the job.
😎
+---------------+ +----------------+ +---------------+
|Transaction | |Payment | |Payment_Type |
+---------------+ +----------------+ +---------------+
|Transaction_ID |-|--, |Payment_ID | ,--|<|PaymentType_ID |
|(details) | | |Payment_Type_ID |-|--' |(details) |
+---------------+ | |(details) | +---------------+
'--o<|Transaction_ID |-|--, +------------+
+----------------+ | |CCD_Details |
| +------------+
'--o-|Payment_ID |
|(details) |
+------------+
April 17, 2014 at 5:18 pm
Hi, Thanks for the response.
In the relationship between Payment and Payment_Type tables, aren't the crows feet backwards?
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.
April 19, 2014 at 4:01 am
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 😀
May 13, 2014 at 1:42 pm
This is great! Thanks so much!
By the way, how do you create those ERDs with plain text? I'm assuming its generated. Seems like a lot of work to do by hand.
June 1, 2019 at 10:18 am
It's mysql command line shell output.
mysql -u USERNAME -pPASSWORD
use mydb;
select * from TABLENAME;
January 19, 2022 at 12:22 pm
Personally, I would inquire the following.
What if the individual wishes to pay in cash and on credit?
What if you need to delete the card?
What if the card belongs to multiple people, such as a brother and sister who share the same credit card?
What if your account is hacked? Using credit card numbers as primary is extremely risky. I'm assuming you encrypted the credit card number.
Is it possible to avoid saving the credit card number? It would be far more straightforward to say, Type: Credit Card/Cash. Unless you are required to keep the credit card on file.
Sorry, I can't be of much assistance on this one because the information is so limited.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply