What database schema can I use to save different types of billing data?

Question

I have a system that creates an order and that order can be billed to a house account, sent Cash on Delivery (COD), or charged to a credit card. I've created the following tables:

ORDERS
order_id
billingoption_id

BILLINGOPTIONS
billingoption_id

I'm unsure of how the next table should be built for the billing data. Should I build a separate table for each type of billing option (ie. COD, Credit Cards, and House Account)? Then would I have another foreign key column on the Orders table that would refer to a record for the billing data?

Answer 1

You can do it either way: a big honking billingoptions table that has fields that encompasses all of the types, with NULLs for fields that don't apply to a given type, or a bunch of baby tables that "star off" of a parent billingoptions table. Both have their advantages and disadvantages.

For the big honking table,

• It's nice that all data can easily be referenced in a single table.
• Tracking foreign key dependencies and performing updates or inserts is efficent.
• BUT you need to alter the table structure to add new billing options in the future, and there's the possibility of invalid combinations of data being stored (for example, both a credit card type and a COD flag being set in the same record).

For the small baby tables,

• It's nice that the data is partitioned and reflects your program's object structure closely.
• It's nice that you can add new payment options or alter existing ones without worrying about affecting the others.
• The relationships are VERY explicit. You can't accidentally link a deposit with another deposit, since the foreign key will require that it be linked with an approval.
• BUT you end up introducing a lot of tables into the design, which require lots of JOINs, can be a pain to navigate, and aren't as efficient when it comes to inserts and updates.

At work, we ended up going with small baby tables. 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 of the payment types share three transaction related tables:

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 of our payment methods (Credit Card, PayPal, Google Checkout, Check, Cash, Store Credit, and Money Order) are abstracted to fit into this Approval --> Deposit --> Refund metaphor, and the UI calls the same methods on an IPayment and IPaymentProcessor interfaces with different implementations (CybersourcePaymentProcessor, PayPalPaymentProcessor, etc). The abstraction has worked pretty well over the past year and a half across these disparate methods, although sometimes the GUI will display different verbiage to the user (for example, it'll say "Authorize" and "Charge" instead of "Approve" and "Deposit" for credit card payments, and the screen for entering cash performs the Approve/Deposit step in one fell swoop.)

Hope that makes sense. It sounds like you're not actually storing payment information, but it's useful to think about where these things can end up.