Best way to handle refunds or/ store credits in a database?

Question

Let's say I've got a web application that is a store of some kind. I have a table that holds all of the monetary transactions.

custid, orderid,amount paid...etc

And this table is being used for sales reports and what have you.

Now we want to give a customer a credit of some kind, either a gift certificate or a refund.

is it a bad idea to just enter it in the same table with a -amount? Or is it better to put these in another table?

Is there a major flaw in setting the site up with this table structure to begin with?

I've set up a few systems like this but haven't had much feedback from others, how do you guys usually set up your tables for store like db's.

thanks, Ken

Answer 1

Typically there would be a reason why you'd give a refund, so already the schema for this use case is different than that of a purchase.

So now your choice is should you store the refund in both places? It always makes me uncomfortable having multiple sources of the truth.

You will need to decide how you are going to work out the overall balance of a customer, storing the in/out in multiple places is going to make this harder than it should be. So you're back to having a single store for money in/out and a separate store for meta-data about a refund.

Purchase
--------
PurchaseId
ItemId
CustomerId
Payment

Refund
------
PurchaseId
Reason

Obviously there are other fields, as you say -ve values for refunds

As it happens this is nearer a real world paper ledger and separate 'refunds' book.

I've never had to do this , this is just me thinking out loud :-)

Answer 2

There are a hundred ways to skin a cat, but here are a few "food for thought" points:

• You could potentially add a "Refund" column that would contain a "1" if it is a refund, or a "0" for a sale. You then have to decide whether to keep the amounts all as positive values (and just subtract if there is a "1" in the refund column) or if you want the amounts to be positive and negative and just look at the refund column as more of an indicator (possibly for reporting purposes)
• You should consider your purchaseID! Do you consider it more of a "transaction ID" or an "order number". It may seem like there is no difference at first, but a transaction ID would have a unique ID for every entry that would mean a purchase would be 0000, and the refund would be 0001 (for exmaple). If you treat it as an order number, the purchase would be 0000 AND the return would also be 0000 so that you know the refund is related to that specific purpose.
• Expanding on my previous point, I would suggest considering a separate Refund table that would contain a unique RefundID, CustomerID, OriginalPurchaseID, ItemID, Amount, and Reason column (and perhaps PaymentMethod). Your Sales table would remain pretty much the same: (unique) PurchaseID, CustomerID, ItemID, Amount, PaymentMethod. Also, be careful with your ItemID as the current setup would require a separate entry (with repeated purchaseID) for EACH itemID.

Hopefully this helps a little bit and can guide you to a better structure. Don't be afraid of having multiple tables, just make sure that you have a good method of relating them!