What is the correct way to store payments and refunds in a transaction table?

Question

I have an issue that touches both accounting and database principles. I have a table called Payments that, among other columns has this structure:

-------------------------------
id   | amount | type
-------------------------------
1    | 100.00 | payment
2    | 200.00 | payment
3    |  50.00 | refund
4    | 130.00 | refund
5    | 500.00 | payment

So currently, I am storing payments and refunds as positive amounts and indicating a transaction type in a different column. When we pull up reports, I check for the type and put in parentheses the amount [for e.g, (50.00) ]

However, the question now is, one could also potentially store the same information as :

-------------------------------
id   | amount  | type
-------------------------------
1    |  100.00 | payment
2    |  200.00 | payment
3    | -50.00  | refund
4    | -130.00 | refund
5    |  500.00 | payment

The main argument for which is that we can run a SUM() on this column to quickly get a balance, and also a question of 'Why store a positive amount when technically money is going out.'

My question is:

What is the correct way of storing payments and refunds in a database? The first implementation was there because I went with my gut - storing it as negative to indicate a refund felt wrong to me. The idea behind it being that, as this answer put it:

Accounting values are not scalars -- they are vectors which contain an enum (debit or credit) and a fixed-point decimal number (which can be positive or negative).

( The original stack overflow question can be found here )

This answer also claims that

Using one column for everything and then using negative numbers for either debits or credits doesn't work ...

But I'm struggling to see why the case in the question is wrong. I'm also trying to find a case where it would not work here, and also to see if storing negative amounts is wrong, or whether storing it both positive is also wrong.

To re-iterate, what is the correct way of storing payments and refunds in a database? Since this involves financial transactions, I want to make sure that I am doing it the correct way, if such a thing exists.

Answer 1

I went with the second implementation, taking the approach that @Raidex and @indiri suggested. No reason to unnecessarily complicate this record keeping, especially since we're talking just a payments/refunds table.

Answer 2

the short answer is that there are always multiple ways to model a system. There is no one "correct"way, so stop searching. I agree with @indiri generally.

You have confused what you store with the presentation of that information. Worse you have taken what appears to be an irrelevant offramp into accounting mumbo jumbo. You did not create an accounting system (because that is very complicated) so worrying about debits, credits, assets, liabilities, etc. is not particularly useful. How you differentiate a payment and a refund is correct if you can retrieve and represent the information accurately. Some ways might be better than others, but there is nothing to indicate a change is needed.

And that "doesn't work" quote is over-stating things - but that was your fault. As I said, you did not create an accounting system. Do you have a chart of accounts? Do you track the flow of credits and debits generated by the various types of transactions your system supports. Not ;ikely. You probably created a much simpler version that handles the transactions only. I'll guess that you can't easily generate common financial statements (e.g. balance sheet) - at least not without assumptions.

One thing you may have overlooked or worked around is the fact that the value of any transaction can increase or decrease a balance. When you correct a payment, should you do that by entering a negative amount for payment transaction? Or by entering a different type of transaction? Should you change the original transaction or add a new row as an "offset"? This sort of thing can go on and on.

So stop searching for the holy grail. As long as you can generate accurate information, the you can conclude that your design is correct.

Answer 3

What is the correct way of storing payments and refunds in a database?

From my point of view it is positive. Because when you print Refunds Numbers are positive on paper.