SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ERD for Credit Card and Cash Payment Types


ERD for Credit Card and Cash Payment Types

Author
Message
Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 410
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
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14906 Visits: 18591
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.
Cool


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

Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 410
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.
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14906 Visits: 18591
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 typow00t


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 Cool


  +-------------+           +--------------------+           +---------------+
| 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 :-D
Jacob Pressures
Jacob Pressures
SSC-Addicted
SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)SSC-Addicted (481 reputation)

Group: General Forum Members
Points: 481 Visits: 410
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search