On 20/02/2009 12:35 PM, BareFeet wrote: > Hi John (Machin), > > Thanks for the discussion. > >>> 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. > > True, but redundancy within an SQL schema is a bad thing.
Excessive unthinking redundancy is bad. So is excessive unthinking normalisation. > There are > other ways to cater for redundancy, such as backups Really useful. How long are they kept? How long will it take to get access to an old backup? Will it be readable? Does your SQL allow INNER JOIN old_backup.critical_table ON ...? > and other ways to > prevent entry errors such as triggers and contraints. As I said, data entry time is not the problem. In the real world, support programmers will turn off the constraints in order to do maintenance. If you are lucky, they will remember to turn the constraints back on after they have had their wicked way with the database. > The SQL schema > should be a tight normalized model. > > Having said that, however, I think that storing the Amount of each > Entry within a Transaction is inevitable from a design perspective. Of course. It's screamingly obviously an utter nonsense to pursue normalisation that far. > I > just wondered if anyone had come up with a design that is fully > normalized. See "nonsense" above. > >>> 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. > > It was more of a wonder than a want ;-) Since every stored double > entry transaction will sum its Amounts to zero, we can deduce the nth > Amount by knowing the other Amounts, which technically violates > normalization (Third Normal Form). So I wondered if anyone had come up > with a schema that practically implemented this. I presume not, so > will continue with the n Amounts stored for each Transaction. > >> 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. > > Do you mean, like an audit trail of changes? Yes, but not as a separate table or as something external to the database. I mean within the same table; like I said, no update, no delete. > That's fine. I am just > boiling the schema down to the bare essentials here to focus > discussion. An audit trail *is* a bare essential. > Catering for logging of changes is a great idea and > practice, but just outside the scope of the basic schema to which I'm > referring. Whether an update initiates an overwrite of data or a > "reverse out", it would still have to be done for each Date > redundantly, if the Date was stored for each Entry within a > transaction. Since by definition all Entries in a Transaction occur on > the same Date, it would be best to store it once once for that > Transaction. > >> 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. > > Again, it's not a case of "we have space so let's waste it". That > would be like storing a person's contact details in every event in am > Event table. A screamingly obvious strawman. > It doesn't matter if you have a terabyte of space, it's > just bad design, creates many problems and defeats the whole purpose > of a relational database. > > For reference to Normalization, see here: > <http://books.google.com/books? > id=VsZ5bUh0XAkC&pg=PA65&lpg=PA63#PPA63,M1> > >>> 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 >>> ) >>> ; > > >> Do you mean Amount is always non-negative and Operator tells you >> whether it is DR or CR? > > That schema was my interpretation of the design discussion page above. > There, yes, they use Operator as plus or minus with always a positive > Amount. I think that's unnecessary and confusing, so I would instead > opt for an Amount that can be positive or negative, eliminating the > Operator column. You'll get no disagreement from me on that one :-) > The data can still be presented to the user in Debit > and Credit columns that only have positive values, but the storage can > be signed. Any thoughts/agreement/disagreement on that? Cheers, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
