On 2 Jun 2015, at 12:34pm, Richard Warburton <richard at skagerraksoftware.com> wrote:
> I'm wanting to store data in a way such that I can choose a time in the > past and view records as they were at that dateTime. Therefore (in my > mind), all updates are really inserts with a timestamp. Ids are suddenly > no longer primary keys, as many records can have the same id, but a new > entry must be given a unique id (otherwise it will look like an update for > an existing entry). This is an area I used to do a lot of work in, and there are some complicated security implications I'm not going to try to explore in one post. If you have a specific question I'll try to answer it, but here are my general recommendations: Keep your existing tables as they are, modify them as you have been, and create a new table or tables for the historical information. That way, parts of your code which don't care about historical information can continue to look at the 'normal' tables as they did previously, and you have the same uncomplicated 'id' system that would be used in a normal database. When you have commands which modify the logged table, use an AFTER TRIGGER to make a copy of the new row into this new table, with a timestamp on it. The primary key of this new table becomes (truerowid, timestamp). Of course, DELETE FROM doesn't leave a 'new row', so you will invent a way to record DELETEs. As an alternative, rather than logging copies of the actual data in this new table, you can keep copies of the commands used to change the table. If you do this, then you can create a copy of your database at any time merely by replaying all the commands executed up to that time. One advantage of this is that all your reconstruction information can be stored in one table. Which of these you do depends on why you're keeping the historical data: whether you're trying to keep an 'changes list' for audit/security purposes or whether you expect to actually be working on the data in the same way as you work on current data. Simon.
