8

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?

6

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)    |
+----+---------------+--------------------+
7

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 – Reinstate Monica May 31 '12 at 0:38
  • 1
    @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
4

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.