In general you need for one transaction - General Ledger account Date Reference ID Cross Reference Amount Optional narrative
The Chart of Accounts defines full set of accounts and the details of each account. The G/L A/C indicates whether it is an asset or liability account (plus or minus) and how it is grouped in reports, the a/c, date amd reference ID are a unique ID and the "cross reference" can be a key on an index which ties together balancing entries in each "T Account". If each set of entries is an atomic transaction which balances debits and credits then the ledger is always balanced. Having a separate index linking balancing transactions makes displaying accounting activity much clearer. You could call the cross reference a "journal ID" or a similar name, since it has some functional similarity to the traditional bookkeeper's general journal. Make sure that you insert each set of accounting transactions as one Sqlite transaction which rolls back should it fail to successfully complete, thus maintaining the balance of the ledger. BareFeet wrote: > Hi Rich, > >>> I'm trying to design a schema for double entry book-keeping. Is there >>> already a schema available that provides this functionality? >> Well, if you're going to re-invent the wheel, take a look at the >> source >> code for gnucash or, better yet, SQL-Ledger. > > Thanks for your reply. I can't find the actual schema for either of > those packages. But what I have seen of them indicates that they cater > for a much broader solution than what I'm after. > > I'm just looking for the general schema or data model for storing > double entry accounting transactions. > > I found this: > Modeling Business Rules: Data Driven Business Rules > http://www.tdan.com/view-articles/5227 > which, seems similar to the route I was taking. If I'm interpreting it > correctly, the TDAN article suggests a relationship between Accounting > Entry and Accounting Transaction: > > create table "Accounting Entry" > ( > ID > , "Transaction ID" --> "Accounting Transaction".ID > , "Account ID" --> Account.ID > , Amount > , Operator -- plus or minus > ) > ; > create table "Accounting Transaction" > ( > ID > , Date > , Description > ) > ; > create table Account > ( > ID > , Description > ) > ; > > Is tat the general model others have used, experienced, seen, > implemented? > > Thanks, > Tom > BareFeet > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
