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

Question

my question is:

i have a member that can pay in 3 different way

1. credit card

2. check

3. transfer from bank account

how design the table that contain the history of payment.

the fields of every way is different .

if i save all in one table , i have lot of blank field ?

what the right way to deal it ?

Answer 1

you can create 3 table for each payment and one table for payment and make 3 fk in payment table it has blank field yet but it's not so much important brcause just 2 field,

or you can do in inverse mode make 3 table for each payment and one table for payment and make fk for each of 3 table that referrence to payment table but you must consider unique ley for each table

Answer 2

Main payment table, to which main information from all successful transaction stored.

each payment method will have:
• order information table, to store all information that we passed to payment gateway.
• - response information table, to which the response from payment gateway stored.

Answer 3

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!

Answer 4

I'm with Jason on structure.

And by all means don't forget to encrypt all that creditcard and bank account data.

Answer 5

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 6

Store all income in a Payments table. Each Payment specifies a payment method from the PaymentMethods table and is related to an order in the Orders table.

In the past I stored payment methods in a table, but after some thinking I conclude that an IPaymentMethod interface with derived classes (eg. class CreditCard : IPaymentMethod), would be easier to extend and maintain.

Database Schema

1. Partial PaymentMethods Table

• Method ID (primary key)
• Name (eg. CreditCard, Check, WireTransfer)

2. Partial Orders Table

• Order ID (primary key)
• User ID (relates to Users table)
• Status (Pending or Completed)
• Total

3. Partial Payments Table

• Payment ID (primary key)
• Order ID (related to Orders table)
• Method ID (relates to PaymentMethods table)
• Status (Pending or Completed)
• Date (for record-keeping)
• Amount

Order Saldo Query

To determine the total value of payments received for any given Order, simply sum the Amount of all Completed payments in the Payments table, grouped by Order ID:

SELECT
    OrderID,
    SUM(Amount) as Saldo
FROM Payments
WHERE
    Payments.Status = 2 -- where Pending: 1, Completed: 2.
GROUP BY
    Payments.OrderID