Actually I would store the history values outside the main table for performance and storage overhead reasons (history table may have additional columns, e.g. When-who-etc for auditing information). Peter
2009/7/9, Donald McLean <[email protected]>: > On Thu, Jul 9, 2009 at 9:03 AM, Alessandro Bottoni<[email protected]> > wrote: >> Hi Guys, >> I'm completely new to Derby and I evaluating it as a possibile embedded >> RDBMS for a Java/Swing application that I'm goig to develop. I'm >> currently following the tutorials at IBM DeveloperWorks >> (http://tinyurl.com/6mlwpj) and I have ordered the IBM book at Amazon's >> already (http://tinyurl.com/ln3cas). >> >> I have a stimulating question for you all (I apologize if this topic has >> been discussed already): >> >> how would you implement a system to keep track of any old version of a >> record (row) of your database? >> >> I'm asking this because I need a system that will allow the user to see >> any old version of a record and to undo/redo any change with just a >> couple of mouse clicks. >> >> Any idea? Any suggestion? > > Actually, this is a question that I have previously considered for a > project that I am starting. > > My thought was to have an identity column (value assigned by the DBMS) > and a separate ID column. From here, you can do several different > things: > > 1. Have a "version" column. Use the max function to find the current > version, or make the current version -1 or something else easy to > find. > 2. Use the max function on the identity column to find the latest version. > 3. Have "previous" and "next" columns with the identity values of the > appropriate objects in the chain. The current version would have a > null "next". > > There are probably other approaches that I haven't thought of. > >> PS: No, I'm not planning to use any kind of ORM (Hibernate or similar). >> Should I? > > I have used and recommend Hibernate. It simplifies things immensely > IMO, though it does have its quirks (some of which are not properly > explained by the documentation). It also works well with a number of > other DBMS (if changing is something that might happen later). > ------------------------------------------------------------- > Family photographs are a critical legacy for > ourselves and our descendants. Protect that > legacy with a digital backup and recovery plan. >
