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:
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.
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.
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:
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.