Hi, 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).
Here's a very simplified view of what I'm currently doing: CREATE TABLE data ( UID INTEGER PRIMARY KEY AUTOINCREMENT, Id INTEGER NOT NULL DEFAULT 0, -- shared by updates of the same data entry Data TEXT, User INT, -- who made this update At INT, -- when the update was made Remove INT NOT NULL DEFAULT 0 -- set to 1 if data entry has been deleted ); My initial questions are: 1) Do I need UID? I'm currently using it to ensure a unique Id for when the user is creating a new entry. This however means two operations, an Insert, then an Update to set the record's Id to match its UID. It also has the overhead of autoincrement, ensuring that UID is never ever reused and only goes upwards (I'm currently using MAX(UID) to get the latest record <= the time I'm interested in. I guess there's no extra storage as rowid would exist anyway. 2) Can I auto fill Id to UID on insert instead of having to do two operations? 3) Am I on track or is there a better way to approach this problem? Thanks in advance for any feedback. -- Richard Warburton
