ERD for Credit Card and Cash Payment Types

  • Jacob Pressures

    SSCertifiable

    Points: 5635

    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

    SSC Guru

    Points: 182347

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

    +------------+

  • Jacob Pressures

    SSCertifiable

    Points: 5635

    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

    SSC Guru

    Points: 182347

    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 😀

  • Jacob Pressures

    SSCertifiable

    Points: 5635

    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.

  • AmirhoseinMorteza

    Newbie

    Points: 1

    It's mysql command line shell output.

    mysql -u USERNAME -pPASSWORD

    use mydb;

    select * from TABLENAME;

     

  • Eirikur Eiriksson

    SSC Guru

    Points: 182347

    AmirhoseinMorteza wrote:

    It's mysql command line shell output. mysql -u USERNAME -pPASSWORD use mydb; select * from TABLENAME;  

     

    Your post is a little baffling, no relation to the OP!

    😎

    Further, for any purpose, that code does not make any sense!

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply