# 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?

• I changed my answer to a comment, as it's not really an answer: Accounting systems are incredibly complicated, depending how deep down the rabbit hole you want to go. You should definitely look at Hay's Enterprise Model Patterns and/or Fowler's Analysis Patterns – Neil McGuigan Apr 29 '12 at 0:10
• Thanks Neil, at the end this was the most useful 'answer'. I'm studying Martin Fowler's articles as we speak. – user May 31 '12 at 0:36
• Glad to help. You might wanna look into Postgres over MySQL as well, as it has some features that might be better for an accounting system: full ACID, insert-only database option, check constraints. – Neil McGuigan May 31 '12 at 4:48

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 | TransactionID         | int        |
| PK | TransactionSequenceID | smallint   |
+----+-----------------------+------------+
|    | Amount                | decimal    |
|    | CreditDebitFlag       | char(1)    |
+----+---------------+--------------------+


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.

• Thanks, but this is also heading towards negative/positive ideas, whereas we really need to distinguish between debit/credit – user May 31 '12 at 0:38
• @user I'm surprised by your comment. As I stated in the answer, all amounts are positive. 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. – Matt Fenwick Aug 10 '14 at 18:28

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.

• Thanks but for complex accounting systems "positive" and "negative" numbers are not convenient. I really prefer to work with debit and credit identifications... – user May 31 '12 at 0:37