From: Robert Edwards <bob@cs.anu.edu.au>
To: Medi Montaseri <montaseri@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: accounting schema
Date: Thu, 07 Feb 2008 14:02:57 +1100
Message-ID: <47AA74E1.1000403@cs.anu.edu.au> (raw)
In-Reply-To: <8078a1730802061708q17c72234xe61be822459a24a8@mail.gmail.com>
References: <8078a1730802061708q17c72234xe61be822459a24a8@mail.gmail.com>
Medi Montaseri wrote:
> Hi,
>
> I am learning my way into Accounting and was wondering how Accounting
> applications are designed. perhaps you could point the way....
>
> On one hand, accountants talk about a sacret equation A = L + OE (Asset
> = Libility + Owner Equity) and then under each categories there are one
> or many account. On the other hand a DBA thinks in terms of tables and
> relations. Instead of getting theoritical, allow me to setup an example
>
> Say you have have construction project (like a room addition) or one of
> those flip this house deals
>
> Owner brings the land (equity) of say worth $100K
> Expenses begin to mount ( that is a minus against OE)
> Account Payble begins to mount (that is a liability)
> And one day you experience a sale
>
> As a DBA, (and keeping it simple) I am thinking I need a table for every
> account which migh look like
>
> id, description, credit, debit, validated, created_on, created_by,
> modified_on, modified_by
>
> Is that pretty match it ?
> Please let me know if you have seen some accounting or DB book that
> addresses this problem domain.
>
> Thanks
> Medi
>
My home-grown system uses three key tables: account, transaction and
split.
The split joins a transaction to an account and an amount. All the
splits for a single transaction must sum to zero (checked by a PL/pgSQL
function triggered on insert, update and delete on the split table).
For example, my pay is a single transaction with typically 8 splits
reflecting what my pay-master does with my pay (tax, superannuation,
health contrib, etc.)
I also have other tables for managing reconciliations - each split has
a reconciliation ID that indicates if/when that split was reconciled.
Once reconciled, the split becomes, effectively, immutable (by the same
PL/pgSQL function).
Transactions contain date, description, who etc. (all from the top of
my head - I should check what I really did many years ago).
Most of the rest of it is then just mapping the accounts to the various
ledgers and bank accounts etc.
This model is very simple, for very simple people like me.
Cheers,
Bob Edwards.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bob@cs.anu.edu.au, montaseri@gmail.com
Subject: Re: accounting schema
In-Reply-To: <47AA74E1.1000403@cs.anu.edu.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox