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) |
+------------+