Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Multiple Editable Options, Payments

    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?

  2. #2
    Join Date
    Jan 2006
    Posts
    18
    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.
    Last edited by rehack; 01-08-06 at 15:49.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •