pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaccounting schema
8+ messages / 6 participants
[nested] [flat]
* accounting schema
@ 2008-02-07 01:08 Medi Montaseri <montaseri@gmail.com>
2008-02-07 03:02 ` Re: accounting schema Robert Edwards <bob@cs.anu.edu.au>
2008-02-07 14:08 ` Re: accounting schema John Hasler <john@dhh.gt.org>
2008-02-07 15:52 ` Re: accounting schema Tony Wasson <ajwasson@gmail.com>
0 siblings, 3 replies; 8+ messages in thread
From: Medi Montaseri @ 2008-02-07 01:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: accounting schema
2008-02-07 01:08 accounting schema Medi Montaseri <montaseri@gmail.com>
@ 2008-02-07 03:02 ` Robert Edwards <bob@cs.anu.edu.au>
2 siblings, 0 replies; 8+ messages in thread
From: Robert Edwards @ 2008-02-07 03:02 UTC (permalink / raw)
To: Medi Montaseri <montaseri@gmail.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: accounting schema
2008-02-07 01:08 accounting schema Medi Montaseri <montaseri@gmail.com>
@ 2008-02-07 14:08 ` John Hasler <john@dhh.gt.org>
2 siblings, 0 replies; 8+ messages in thread
From: John Hasler @ 2008-02-07 14:08 UTC (permalink / raw)
To: pgsql-sql
Look at LedgerSMB at <www.ledgersmb.org>. It uses Postgresql.
--
John Hasler
john@dhh.gt.org
Elmwood, WI USA
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: accounting schema
2008-02-07 01:08 accounting schema Medi Montaseri <montaseri@gmail.com>
@ 2008-02-07 15:52 ` Tony Wasson <ajwasson@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Tony Wasson @ 2008-02-07 15:52 UTC (permalink / raw)
To: Medi Montaseri <montaseri@gmail.com>; +Cc: pgsql-sql
On Feb 6, 2008 6:08 PM, Medi Montaseri <montaseri@gmail.com> wrote:
> I am learning my way into Accounting and was wondering how Accounting
> applications are designed. perhaps you could point the way....
<SNIP>
> 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.
Another codebase to look at is http://www.sql-ledger.org/. It uses postgresql.
Regards,
Tony
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: accounting schema
@ 2008-02-07 02:35 ` Steve Midgley <public@misuse.org>
1 sibling, 0 replies; 8+ messages in thread
From: Steve Midgley @ 2008-02-07 02:35 UTC (permalink / raw)
To: pgsql-sql; +Cc: Medi Montaseri <montaseri@gmail.com>
At 05:09 PM 2/6/2008, pgsql-sql-owner@postgresql.org wrote:
>Date: Wed, 6 Feb 2008 17:08:54 -0800
>From: "Medi Montaseri" <montaseri@gmail.com>
>To: pgsql-sql@postgresql.org
>Subject: accounting schema
>Message-ID:
><8078a1730802061708q17c72234xe61be822459a24a8@mail.gmail.com>
>
>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
Hi Medi,
You might read some source code and docs for open source accounting
software and see "how it's done." Here's one example that might be
interesting and simple enough to follow:
http://www.gnucash.org/
In general, I think there are many different accounting methods, so you
have to get clear about which one you're using. "Double-entry"
accounting is common. Cash vs. accrual posting methods matter (i.e.
when does an expense or receivable "charge" against the assets
balance?)
My most basic understanding is that in general you track assets as they
come in, to an "Accounts Receivable" ledger (i.e. table) and
liabilities to an "Accounts Payable" ledger. Then you reconcile these
two "books" into a "General Ledger" table which gives you something
like an audit trail of all activity (and a running balance). I'm sure
Wikipedia will define these three terms and lots more with greater
clarity.
But my (limited) experience with accounting schema is that they often
involve these three tables (AR/AP/GL) at their core.
As you add bank accounts, complex investment instruments, depreciation
etc, things get considerably more complex of course.
I'll readily admit my limited experience, and I'm sure others on this
list have far better information. I hope this gets you started anyway.
Sincerely,
Steve
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: accounting schema
@ 2008-02-07 02:54 ` Medi Montaseri <montaseri@gmail.com>
2008-02-07 03:35 ` Re: accounting schema Steve Midgley <public@misuse.org>
2008-02-07 16:09 ` Re: accounting schema johnf <jfabiani@yolo.com>
1 sibling, 2 replies; 8+ messages in thread
From: Medi Montaseri @ 2008-02-07 02:54 UTC (permalink / raw)
To: Steve Midgley <public@misuse.org>; +Cc: pgsql-sql
Thanks Steve...
This is all well and good...I am getting it...but I need to chew on it
more....gnucash was a good one...didn't think of that....got to get passed
all the GUI stuff...but...excellent ref...
I suppose instead of AR and AP tables, I can just have one table called
Entry (thanks Joe....) with an attribute indicating AR vs AP.
I am also in favor of Double Entry and accrual posting....what do I know, I
hear corporations do it this way...
I also like the "audit trail" built-in feature...actually this was something
I was baffled about...I was originally thinking about editing capability,
but what you are suggesting is that in practice, you don't change or erase
anything...to make a correction, you debit and then credit (keeping the sum
happy) and yet you have the audit trail...very cool...I am liking this
stuff...
And finally you mentioned that bank accounts are tricky...can you expand on
this please. After all I am under the impression that "bank accounts" are a
corner stone of this whole book keeping...I mean...bank accounts have debits
and credits just like any account...eg interest earned is a credit and bank
fees are debits...what worries you about bank accounts...
Thanks guys...this is very very nice
Medi
On Feb 6, 2008 6:35 PM, Steve Midgley <public@misuse.org> wrote:
> At 05:09 PM 2/6/2008, pgsql-sql-owner@postgresql.org wrote:
> >Date: Wed, 6 Feb 2008 17:08:54 -0800
> >From: "Medi Montaseri" <montaseri@gmail.com>
> >To: pgsql-sql@postgresql.org
> >Subject: accounting schema
> >Message-ID:
> ><8078a1730802061708q17c72234xe61be822459a24a8@mail.gmail.com>
> >
> >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
>
> Hi Medi,
>
> You might read some source code and docs for open source accounting
> software and see "how it's done." Here's one example that might be
> interesting and simple enough to follow:
>
> http://www.gnucash.org/
>
> In general, I think there are many different accounting methods, so you
> have to get clear about which one you're using. "Double-entry"
> accounting is common. Cash vs. accrual posting methods matter (i.e.
> when does an expense or receivable "charge" against the assets
> balance?)
>
> My most basic understanding is that in general you track assets as they
> come in, to an "Accounts Receivable" ledger (i.e. table) and
> liabilities to an "Accounts Payable" ledger. Then you reconcile these
> two "books" into a "General Ledger" table which gives you something
> like an audit trail of all activity (and a running balance). I'm sure
> Wikipedia will define these three terms and lots more with greater
> clarity.
>
> But my (limited) experience with accounting schema is that they often
> involve these three tables (AR/AP/GL) at their core.
>
> As you add bank accounts, complex investment instruments, depreciation
> etc, things get considerably more complex of course.
>
> I'll readily admit my limited experience, and I'm sure others on this
> list have far better information. I hope this gets you started anyway.
>
> Sincerely,
>
> Steve
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: accounting schema
2008-02-07 02:54 ` Re: accounting schema Medi Montaseri <montaseri@gmail.com>
@ 2008-02-07 03:35 ` Steve Midgley <public@misuse.org>
1 sibling, 0 replies; 8+ messages in thread
From: Steve Midgley @ 2008-02-07 03:35 UTC (permalink / raw)
To: Medi Montaseri <montaseri@gmail.com>; +Cc: pgsql-sql
At 06:54 PM 2/6/2008, Medi Montaseri wrote:
>Thanks Steve...
>
>And finally you mentioned that bank accounts are tricky...can you
>expand on this please. After all I am under the impression that "bank
>accounts" are a corner stone of this whole book keeping...I
>mean...bank accounts have debits and credits just like any
>account...eg interest earned is a credit and bank fees are
>debits...what worries you about bank accounts...
>
>Thanks guys...this is very very nice
>
>Medi
Hi Medi,
We may be well off-topic for Pgsql but it is a modeling issue so I'll
reply on-list..
Consider this scenario: You want to track your AP/AR accounts by
"business unit" - so you have a Marketing account, Sales account,
Engineering account, etc. But let's say you want to keep all the money
for all the "units" in only two bank accounts depending on which region
the expenses will be paid out from (to reduce processing fees). So you
might pay for things accrued by Engineering and Marketing on the West
Coast from one bank account and Engineering and Marketing expenses on
the East Coast from the other (a US-centric example).
So the bank accounts where cash is actually deposited/withdrawn is
different from where the money is received or spent from a "logical"
perspective in the AR/AP ledgers.
This is a simple example and real-world issues become truly horrendous.
(For example, try to account for investment instruments like long term
property holdings with lines of credit against the real estate).
I've always thought that if DBA's existed when Accounting was invented,
things would look very, very different. :)
Good luck,
Steve
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: accounting schema
2008-02-07 02:54 ` Re: accounting schema Medi Montaseri <montaseri@gmail.com>
@ 2008-02-07 16:09 ` johnf <jfabiani@yolo.com>
1 sibling, 0 replies; 8+ messages in thread
From: johnf @ 2008-02-07 16:09 UTC (permalink / raw)
To: pgsql-sql
On Wednesday 06 February 2008 06:54:36 pm Medi Montaseri wrote:
> I suppose instead of AR and AP tables, I can just have one table called
> Entry (thanks Joe....) with an attribute indicating AR vs AP.
I recommend you not do have only one table for transaction. AR and AP are
different animals and each can get very complex quickly. Adding a field
that only applies to AP and not AR is a very simple example of what goes
wrong.
--
John Fabiani
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2008-02-07 16:09 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-07 01:08 accounting schema Medi Montaseri <montaseri@gmail.com>
2008-02-07 03:02 ` Robert Edwards <bob@cs.anu.edu.au>
2008-02-07 14:08 ` John Hasler <john@dhh.gt.org>
2008-02-07 15:52 ` Tony Wasson <ajwasson@gmail.com>
2008-02-07 02:35 ` Steve Midgley <public@misuse.org>
2008-02-07 02:54 ` Medi Montaseri <montaseri@gmail.com>
2008-02-07 03:35 ` Steve Midgley <public@misuse.org>
2008-02-07 16:09 ` johnf <jfabiani@yolo.com>
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