Hi John, Thanks again for your discussion.
> "Double Entry" book keeping is actually a misnomer. A transaction > is very likely to have more than two entries. Yes, I realize that, though most Transactions generally have just two. In any case, if there are n entries in a Transaction, we only need n-1 of the Entry amounts in order to deduce the remaining amount. SQL (or more generally, set theory) normalization suggests that explicitly storing a redundant record (in this case, the nth amount) is not ideal. I understand that double entry bookkeeping traditionally uses the redundancy as an error check, but that seems more appropriate for manual paper systems, since computer systems can validate entries automatically. So I wondered if there's a normalized approach to double entry bookkeeping. > The "transaction ID" I mentioned is a locally generated reference ID > to tag the particular complete transaction, which might include > postings to a bank account, sales, cost of sales, inventory etc. It > is not conventionally used, but confers great advantages when it is > implemented. It lets you locate the balancing entries for any > accounting transaction. In other words given any element of an > accounting transaction you can complete the T Account. I think you actually called this the "Cross Reference" and I called it "Transaction ID". But, yes, I understand using it in each Accounting Entry that is part of the same Transaction, in order to group them together. All Accounting Entries in the same Transaction should sum their Amounts to be zero. >> 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? > Dating each accounting transaction aids in creating a unique ID. I understand the need to date a Transaction as a whole (therefore storing the Date in the Transaction table), but it seems redundant and denormalized to store the same date in each constituent Accounting Entry. It doesn't make any of the Accounting Entries more unique (since they all by nature have the same date). > Normalizing it out is nor necessarily an advantage. I can see disadvantages of requiring multiple updating (changing the date in one Accounting Entry requires updating the Date for all other Accounting Entries that are part of the same Transaction), wasted storage etc. I can't see any advantages. Or perhaps you actually also meant to imply that Date should be stored once for the Transaction (not for each Entry within it)? > It has been my observation that accounting data is generally stored > in poorly thought-out data structures which then require layer upon > layer of logic to overcome the deficiencies. Thanks then for your insight into the matter :-) I want to get it right from the outset. In my experience poor normalization necessitates layers of fixes down the road. Here is the basic schema from before. I welcome any direct suggestions about the schema: >> 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
