You still miss the point of the cross reference ID. It is NOT the reference ID od the document, such as an invoice or check number, but it more like the row ID used by Sqlire as a unique key for a DB row.
You could normalize out the date and have stored as associated with the cross reference. The cost of that would be an extra row lookup each time you want to get the date of an accounting transaction. A performance and code complexity decision. As they say "you pays your money and you takes your choice". 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. 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
