Hi John, Thanks for the input.
> 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. OK, that seems very similar to the schema I offered before (appended below), with some different names: General Ledger account = Account ID Date = Date Reference ID Cross Reference = Transaction ID Amount = Amount Optional narrative = Description I assume that your Reference ID is for references such as cheque number, invoice number etc, but linked outside of this basic schema. You seem to suggest storing the date for each entry within a transaction. Would it not be better to store it once only for the transaction as a whole, since each entry will share that same date? > If each set of entries is an atomic transaction which balances > debits and credits then the ledger is always balanced. Yes, I had concluded the same, since it's kind of the definition of double entry bookkeeping. I had wondered, though, since most transactions involve two entries for the same amount but for two different accounts, whether storing that same amount twice would not only be redundant but actually de-normalized from an SQL point of view. Is it preferable to have a schema where the amount is only stored once in those circumstances? > 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. Your cross reference or "Journal ID" seems the same as the "Transaction ID" I included in my earlier schema. > 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. Good tip :-) >> 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 >> ) >> ; >> Thanks, Tom BareFeet -- Comparison of SQLite GUI tools: http://www.tandb.com.au/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
