What is a common way to save 'debit' and 'credit' information?

Question

I'm working on an accounting system, and for each transaction I need to save if this is either debit or credit. I can think of two ways (MySQL database):

METHOD 1

• Amount (decimal)
• Type (enum, debit/credit)

METHOD 2

• Debit (decimal)
• Credit

In the first setup, I save the type of transaction, but in the second way I rather save the amounts in the debit or credit column. Pros of this method are that I can more easily sum both debit and credit totals than in method 1. But I am wondering if there is a common way to do this?

Answer 1

Your Method 1 is correct. Here is how I implemented in an accounting system. This model is from "The Data Model Resource Book". How you store the data is very different from how accounting terminology models the double entry method. Credit can mean positive or negative depending on whether you're working on an asset or liability account. This schema will allow you to persist the data no matter what type of account you're posting to.

PK	TransactionID	int
	Description	varchar
	Date	datetime

PK PK	TransactionID TransactionSequenceID	int smallint
	Amount	decimal
	CreditDebitFlag	char(1)

Answer 2

Another way to do this (no idea if this is common or not!):

create table `transactions` (
  ... some columns ...
  amount decimal(10,2),
  ttype int,
  foreign key (ttype) references transactiontypes (id)
  ...
);

create table transactiontypes (
  id int primary key,
  description varchar(30),
  multiplier int
);

And then in transactiontypes you have:

select * from transactiontypes;

+----+---------------+------------+
| id | description   | multiplier |
+----+---------------+------------+
|  1 | deposit       |          1 |
|  2 | withdrawal    |         -1 |
+----+---------------+------------+

And possibly more rows.

Then all amounts in transactions will be positive, and a join to transactiontypes + multiplication by multiplier gets the "real" (positive/negative) amount.

It's only when you run particular queries that there are negatives as well. If you don't want negatives, then you just don't run those particular queries. If you don't like the extra transactiontypes.multiplier column, you drop it; it's not the core of the model, so no harm done.

Answer 3

The most common way to do it would be to use positive amounts for debits and negative amounts for credits (or the other way around, depending on whether your amounts are generally considered to be assets or liabilities).

This is easier than either of your methods when it comes to aggregating a total since you only have to sum over one column.