3

I am developing a small system where I have to record incomes and expenses of the company, and even I can not determine if the income and expense table should be in one table or in separate tables.

The design of the database must allow the system to generate a report of income and expenses by date (in column incomes and other expenses).

I would like to explain me what would be the advantage or disadvantage of having them together or separately

2
  • They HAVE to be separate. Google for T-based accounting or accrual vs accounting
    – Philᵀᴹ
    Commented Jul 1, 2014 at 21:50
  • @Phil - they certainly do not have to be separate, in fact the standard ledger always combines them into one table to facilitate fast trial balancing, etc.
    – Hannah Vernon
    Commented Jul 2, 2014 at 4:45

1 Answer 1

3

If you consider income as a negative expense, and have categorised it sufficiently, a single table is just fine. The columns describing an income transaction are essentially identical to the columns describing an expense.

You can easily have indexes that allow you to quickly find your income or expenses, based on your categories (because negative income is not necessarily an expense, you should have things categorised appropriately). And with an index on time you can easily pull up your change over time.

If you're doing double-entry accounting, you should be able to easily add up your transactions and see they always add to zero. It's a useful test for consistency. Then some reports will leave out "equity" lines to compare Profit and Loss, etc.

If you use separate tables, you can use UNION ALL queries to query across the lot, but I would consider this extra effort you don't really need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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