What is the reason for wanting the id of a record to be fixed at the unique record number of the original insertion?
Do you need to access historical data regularly or only for specific inquiries? For rarely required historical data, you could use a "history table" to hold historic copies of records with fields valid_from and valid_to. Moving record contents to the history table should be doable via triggers, with the additional advantage that the rowid never needs to change. For regular access to historic data, you would need to include valid_from and valid_to fields. These fields need to be present in every index and queried in every select. -----Urspr?ngliche Nachricht----- Von: Richard Warburton [mailto:richard at skagerraksoftware.com] Gesendet: Dienstag, 02. Juni 2015 13:34 An: sqlite-users at mailinglists.sqlite.org Betreff: [sqlite] Best way to handle time slicing of SQL data. 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 _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
