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.

Lox

What you are referring to is double entry bookkeeping using a series of credit and debit tx's against an accounting ledger. Generally one tx is always is positive and the other is always negative. – Argentina

I'm struggling to find any database implications (or principles touched) in this question or the reference question. I could represent, store, or present the business logic in either way. However, I would probably want to follow accounting principles. As long as the relational model is maintained I see no reason to go with anything other than the business (accounting model). – Moriahmoriarty

I suppose it depends on how you want to do it. As a developer the automatic sum is neat but ask an accountant and they might say the other way is correct. – Abstruse

In 20+ years of building applications, I've seen a few approaches but by far the most common and sustainable one, as stated by @RossBush, is to record credits as positive and debits as negative. This greatly simplifies the calculation of balances versus the use of case logic based on the type column. If you have many transaction types, such case logic makes your code unnecessarily complex and less efficient. – Shields

It even gets a little more complicated than that. Generally in accounting they have Accounts Receivable (AR) and Accounts Payable (AP). Both of these will be displayed as positive values. However, if an incoming payment bounces then you may see a negative offset value in AR as it's still part of Accounts Receivable and not part of Payable. In any event this should not affect how you choose to store it in the database as business logic can present it to the accountant any way they like. – Hume

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.

Lox

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.

Pergrim

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.

Unsuitable