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

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?

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.

StackExchange Database Administrators Q#17197, answer score: 7