Gustav Brock
Gustav at cactus.dk
Wed Nov 22 07:41:01 CST 2006
Hi Arthur I don't recall you have written about PITA here, but what you describe is generally known as a temporal database. This is well-known and has been dealt with by some great capacities in the database world, Michael Boehlen, Christian Jensen, Richard Snodgrass and Andreas Steiner. http://www.cs.arizona.edu/people/rts/timecenter/timecenter.html A brief explanation and introduction can be found here: http://www.timeconsult.com/TemporalData/TemporalData.html Note the link to TimeDB, a Java implementing of "A Bitemporal Relational DBMS" for Oracle 10g Also, look up my previous post(ings) on these: http://databaseadvisors.com/pipermail/accessd/2005-May/034503.html http://databaseadvisors.com/pipermail/accessd/2005-May/034455.html This is a very exciting area. For some applications it won't even require that much more data storage (accounting). A simplified approach is a write-only database (which means write and read but neither delete nor update) which both Caché and MySQL offers. Now, wouldn't it be nice if only TSQL2 could be implemented in Access: http://www.cs.arizona.edu/people/rts/tsql2.html /gustav >>> artful at rogers.com 22-11-2006 13:14:50 >>> Right on. That's why I suggested the default GetDate(). There is a whole other subject on this, about which I have written, but I googled it and it didn't come up, so perhaps I wrote it and forgot to sell it to somebody. The gist is this: it's called PITA, which doesn't mean pain in the arse, but rather Point In Time Architecture. Without PITA, the central problem with relational databases is that they don't provide an instant "roll back to August 1" capability. With PITA, they do. It's not all that complicated, but it does require a detailed walk-through so you can understand all the implications, the most critical of which is, "Nothing is ever updated. An updated row is actually replaced, and the updated row's EndDate column is updated to reflect the datetime on which the row was "changed". Thus it becomes possible to issue a query that reflects the state of the database on August 1, 2005. Obviously this increases the size of the db significantly, but in certain environments (such as medical), this is critical -- who was JWC's physician on that date, and what tests were performed, and by which medicos, and so on. So. Today's job is to dig out that PITA article and pitch it to somebody. Arthur