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. There are other ways to cater for redundancy, such as backups, and other ways to prevent entry errors such as triggers and contraints. 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. I just wondered if anyone had come up with a design that is fully normalized. >> 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? That's fine. I am just boiling the schema down to the bare essentials here to focus discussion. 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. 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. 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? 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
