Background
I've built an e-commerce application that connects to Authorize.net's payment gateway. Administrators can process credit cards in the following ways:
- "Capture" transaction where the admin charges the customer's credit card immediately.
- "Auth-Only" transaction where the admin authorizes $X from the credit card and then actually captures the amount later (see number 3 below)
- "Prior-Auth-Capture" transaction where the admin captures an amount equal to or less than an amount previously captured from an "Auth-Only" transaction.
This question pertains to how these types of transactions should be stored in a database. I have the following schema in my "Payments" table:
When a new payment is made, I store information about the payment method in the PaymentAmount field (e.g. Credit Card, Check, Cash).
If the PaymentType is "Credit Card" I store the TransactionType (e.g. Capture, Auth-Only, Prior-Auth-Capture").
PaymentAmount column holds the amount of the payment (if TransactionType is "capture" or "prior-auth-capture". If the TrandactionType is "Auth-Only" I store the amount in the AuthorizedAmount column and keep track of the date the auth'd amount is good for in AuthExpDate.
Fields related to Authorize.net's transaction/reference data are stored in ApprovalCode and TransactionID columns.
Back to the Question
Now that you have a little background, lets dig into the details of my question: Should every transaction get it's own record in this table? Or should I update an existing transactions for "Auth-Only" transactions when I capture funds from them?
Examples:
New Record For Every Action: If a user makes an "auth-only" transaction I'll create a new record in the Payments table. If the user later runs a "prior-auth-capture" transaction on the initial transaction should I create a second row in the database or should I update the original transaction and add the amount captured to the PaymentAmount column?
PaymentsID TransactionID PaymentType PaymentAmount AuthorizedAmount ------------------------------------------------------------------------ 1 ABC123 Auth 0.00 100.00 2 ABC123 Auth-Capture 100.00 0.00Single row for each TransactionID and updating original transaction with each aut-capture.
PaymentsID TransactionID PaymentType PaymentAmount AuthorizedAmount ------------------------------------------------------------------------ 1 ABC123 Auth 100.00 100.00
Logically I can see arguments for both cases. On one hand, if I create a new row with each transaction I can see a nice history of each hit against every "auth-only" transaction. The downside is under circumstances where you make multiple "prior-auth-capture" transactions against a single "auth-only" transaction because to figure out how much you have left you have to write a slightly more complex query (joining the table to itself and grouping by TransactionID to get the sum of PaymentAmount).
If I create a single row for each TransactionID then I could easily update the PaymentAmount and calculate the amount remaining in the auth using a simple calculation (AuthorizedAmount - PaymentAmount = AuthorizedBalance). This approach also makes things cleaner since the TransactionID, ApprovalCode, Payment Info, and pretty much everything else would remain the same so you have less redundant data in the table. Additionally, from a reporting point of view most users simply want to see a single row for each transaction for simplicity sake.
I'd love to hear your thoughts on the right way to approach this problem.
