Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
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
    
Will do! Thanks for the tip. –  user May 31 '12 at 12:12

3 Answers 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.

share|improve this answer
    
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

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.

share|improve this answer
    
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

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)    |
+----+---------------+--------------------+
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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