On 20/02/2009 9:23 AM, BareFeet wrote: > 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.
That's just fine and dandy at data entry time. However do please consider that one ill-considered action by a support programmer can cause a whole lot more damage a whole lot faster than a book-keeper with a green eyeshade and a quill pen. Redundancy is *GOOD*. It lets you detect errors. With sufficient redundancy you may even be able to correct errors. > 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. Oh. I thought you wanted to store only n-1 amounts and "deduce" (i.e. guess) the last one. > >>> 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. Some people would go into severe shock-horror mode at the suggestion of "updating" the transaction date after it was entered. They would prefer to reverse out [not delete!] the presumably erroneous transaction and write a correct one. Concerns about "wasted storage" were understandable when a DASD held (say) 50Mb and was the size of a washing machine. That's a long time ago. > > 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 Do you mean Amount is always non-negative and Operator tells you whether it is DR or CR? >>> ) >>> ; >>> 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 > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
