What database scheme can I use to save different types of payment data?

Question

I have a system that creates an order, and this order can be billed at home, sent by COD or charged to a credit card. I created the following tables:

ORDERS
order_id
billingoption_id

BILLINGOPTIONS
billingoption_id

I am not sure how the following table should be built for invoicing data. Should I create a separate table for each type of billing option (e.g. COD, Credit Cards and House Account)? Then I will have another foreign key column in the Orders table that will refer to the record for the billing data?

Answer 1

You can do this anyway: a large billingoptions tracking table that has fields covering all types, with NULL for fields that are not of this type, or a bunch of children’s tables that are “star-shaped” off the parent billingoptions table. have their advantages and disadvantages.

For a large honing table

• It is good that all data can be easily referenced in one table.
• Tracking foreign key dependencies and performing updates or inserts is efficient.
• BUT, you need to change the table structure to add new payment parameters in the future, and there is a possibility of invalid combinations of stored data (for example, both the type of credit card and the COD flag set in one record).

For small children's tables

• It’s good that the data is divided into sections and closely reflect the structure of your program.
• It's nice that you can add new payment options or change existing ones without worrying about the impact on others.
• Relations are very VERY explicit. You cannot accidentally associate a deposit with another deposit, as the foreign key will require that it be associated with approval.
• BUT you end up introducing many tables into the design that require a lot of JOINs, can be a pain to navigate, and not so effective when it comes to inserts and updates.

At work, we ended up driving with small children’s desks. It looks something like this:

  Table Orders:
 -> OrderId PK
 -> (Lots of Other Fields)

 Table Payments:
 -> PaymentId PK
 -> OrderId (FK) [There may be more than one payment per order]
 -> PaymentType [Restricted field contains values ​​like 
        'PAYPAL' or 'CREDIT', you use this to know which 
        baby table to look up that can contain additional 
        information]

 Table PaymentsPayPal:
 -> PaymentPayPalId PK
 -> PaymentId FK points to Table Payments
 -> TransactionNo
 -> (Other PayPal specific fields)

 Table PaymentsCheck:
 -> PaymentCheckId PK
 -> PaymentId FK points to Table Payments
 -> RoutingNo
 -> (Other e-check specific fields)

 + other tables for remaining payment types ....

All types of payments have three tables related to transactions:

  Table PaymentApprovals:
 -> PaymentApprovalId PK
 -> PaymentId FK points to Table Payments
 -> Status [Some flag meaning 'Succeeded', 'Failed', 'Reversed', etc]
 -> ProcessorMessage [Something the service sent back, like '(M) CVV2 Matched']
 -> Amount
 -> (Other administrative fields)

 Table PaymentDeposits:
 -> PaymentDepositId PK
 -> PaymentApprovalId FK points to Table PaymentApprovals
 -> Status
 -> ProcessorMessage
 -> Amount
 -> (Other administrative fields)

 Table PaymentRefunds:
 -> PaymentRefundId PK
 -> PaymentDepositId FK points to Table PaymentDeposits
 -> Status
 -> ProcessorMessage
 -> Amount
 -> (Other administrative fields)

All our payment methods (Credit card, PayPal, Google Checkout, Check, Cash, Store Credit and Money Order) are abstracted to fit into this approval → Deposit → Return metaphor, and the user interface calls the same methods on the IPayment and IPaymentProcessor with different implementations ( CybersourcePaymentProcessor , PayPalPaymentProcessor , etc.). Abstraction worked very well over the past year and a half on these disparate methods, although sometimes the GUI will display different words for the user (for example, he will say "Authorize" and "Charge" instead of "Approve" and "Deposit" for credit card payments, and the screen for entering money performs the step "Approval / Deposit" in one fell swoop).

Hope this makes sense. It seems like you don’t actually store billing information, but it’s useful to think about where these things can end.

Answer 2

Focus on things. Actual stuff. Try to describe things simply, directly and in natural language.

Then, when you request design guidance, you can provide definitions. In some cases, the act of writing definitions will make the design crystals.

Orders are things. What are the attributes of the order? Customer, Product, Payment / Payment.

Payment options are (almost) things. You can apparently identify and identify them. (I'm not sure I can. From your question, it looks like you could do it. But without a sentence with one sentence, I'm not sure what is happening with Billion Options.

What is billing information? What it is? What are its attributes (or properties)?

How do invoice data relate to the order? How does this compare with the invoice option?

Feel free to update the question with definitions for each thing.