DB design : save different details of payment (credit or check)

Question

I have a member that can pay in three different ways:

1. credit card
2. check
3. transfer from bank account

How can I design a table to record their payment type?

For each payment type the required fields would be different, so how can I design a structure that would eliminate the blank fields?

Answer 1

I think the wrong answer is to have 3 tables. Then the common data -- like "amount paid" -- is repeated across multiple tables, and a simple query like "what is the total that has been paid this month" requires a 3-table union or join. Plus if a fourth payment type gets added, any queries that worked on the 3 tables have to be modified, and someone will surely miss one.

So there are two possible right answers: a single table with some fields that are unused for some payment types; or 4 tables, a generic "payment" table with the common information and then 3 auxiliary tables with the information specific to each payment type.

Some people will say that you must create 4 tables for dogmatic reasons. Personally I'd be more practical: How much different data do you have? What queries will you be doing? I've had a few systems where I've let "account_number" be a bank account number for checks and a credit card number for credit cards and that hasn't gotten me into any obvious trouble, though I feel a little guilty about it.

When the number of data fields gets large, I tend to break it up just because it otherwise gets messy and confusing. Again, pragmatism: one unused field doesn't seem too ugly, but twenty unused fields definately is.

Answer 2

The way to do it is to make four tables, if the three ways have all different fields. Make one table for each of the different methods and one table for the order itself. In the order table, you will store the orderID and an enumerated value of which method the user used to pay (in addition to any other fields you need). For instance, if you were programming in .NET, you could create an enum:

Public Enum PaymentMethod
    CreditCard
    Check
    WireTransfer
End Enum

This would effectively set CreditCard equal to 0, Check to 1 and WireTransfer to 2. Have a field in your main order table for what type this is and save this integer there. In your payment tables, have a foreign key for the OrderID from your main table. If you wanted, you could keep the ID of the record in the payment table, but it's unnecessary because you can find the records based on the OrderID:

SELECT o.*, cc.* FROM Orders o 
INNER JOIN CreditCardPayments cc ON o.OrderID=cc.OrderID
WHERE cc.OrderID=[some number]

Of course if you have three different methods, you'll have to check first to see which kind of payment it is first and then use the appropriate SELECT statement:

SELECT PaymentMethod FROM Orders WHERE OrderID=[some number]

Once you determine with method, you can use pre-made SELECT statements that call from your different payment method tables.

Hope this helps!