Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ERD for Credit Card and Cash Payment Types Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2014 9:37 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
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
Post #1562510
Posted Thursday, April 17, 2014 12:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,901, Visits: 5,226
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) |
+------------+
Post #1562531
Posted Thursday, April 17, 2014 5:18 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
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.

Post #1562882
Posted Saturday, April 19, 2014 4:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:29 AM
Points: 1,901, Visits: 5,226
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


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
Post #1563228
Posted Tuesday, May 13, 2014 1:42 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 16, 2014 9:24 AM
Points: 328, Visits: 395
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.
Post #1570528
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse