Multiple Editable Options, Payments

LostInRecursion · #1 (**permalink**) 01-07-06, 14:11

Hi all,

This is a pretty simple question I think, but I cannot solve this properly in my head. Let's say I have an orders table in its simplest form which holds nothing more than the following:

ORDERS
OrderID (PK)
CustomerID (FK from Customers)
OrderAmount (Total Amount in Money of Order)

Now the organization needs to accept multiple payment types for a single order. Not only that, but they need to be able to add payment types at will. For example, they may hook up with a third party vendor who sells their "admissions" to customers and the customers use them at the organization.

Make sense?

So how do I represent this effectively and still keep redundancy to a minimum in my design?

I could easily create tables for CreditCards, Checks, GiftCertificates, ThirdPartyPayments and then with some elbow grease and WHERE statements get the application to understand the payment type.

But that just sounds "hacky"

Any ideas?

rehack · #2 (**permalink**) 01-08-06, 14:44

i haven't really thought of this before. this is not optimal imho, but it's the first thing that popped into my mind:

give each payment method a name ("visa", "coupon", "paypal", etc.) and how many arguments it takes (coupons only need a coupon# so 1 argument, visa would need say 6 or 7)

Code:

table payment_methods
 method_id (primary key)
 name
 how_many_arguments

for each column of a payment method, what is the heading (cc number, coupon number, etc.)

Code:

table payment_columns
 method_id (foreign key)
 argument_number
 argument_name
 primary key (method_id and argument_number)

an individual payment requires a unique id (for multiple payment arguments to reference), a payment method and an amount

Code:

table order_payments
 payment_id (primary key)
 order_id (foreign key)
 method_id (foreign_key)
 payment_amount

for each payment, we need 1 or more arguments

Code:

table payment_arguments
 payment_id (foreign key)
 argument_number
 argument_content
 primary key (payment_id and argument_number)

thus: for each order, you have multiple payments. for each payment, you have one method and multiple arguments. these arguments (numbered 1, 2, 3, etc.) are named differently depending on the method_id. some sample data:

Code:

*order*:
order_id 100
cust_id 200
order_amount $500

*payment_methods*
method_id 1
method_name paypal
method_arguments 2

*payment_columns*
method_id 1
argument_number 1
argument_name "email"
,
method_id 1
argument_number 2
argument_name "reference no."

*order_payments*
payment_id 1000
order_id 100
method_id 1
payment_amount $500

*payment_arguments*
payment_id 1
argument_number 1
argument_content "ed@ed.com"
,
payment_id 1
argument_number 2
argument_content "431251234124av134"

so now on querying, we can find that there is 1 payment for the order with order_id 100, that payment's method is paypal for $500, paypal methods have 2 arguments, and those arguments are the customer's email address and whatever reference number paypal gives merchants when payments occur.

not the best example i know, but i got tired of typing

i'm looking forward to other people's input, i just started hacking on databases again 4 years after a single college course in them. it's good to exercise the brain again.

Internet Services

Web Development

Digital Marketing

Consumer Tech