On Tue, Jun 2, 2015 at 4:34 AM, Richard Warburton < richard at skagerraksoftware.com> wrote:
> 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. > if Id == UID always; the question is really, do you need Id? > 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? > > Using a GUID key can solve it also... but again if Id is always the same a UID do you need Id? 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 >
