Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Feb 2008
    Posts
    6

    User Action History

    Hi,

    I am designing a DB for a web platform where you want to see an action history for each user. I am not sure how exactly to implement this:

    I am demonstrating my ideas on the examples of votes: A user can vote only once for specific poll, but he can later change it (so the old vote doesn't count anymore).

    My original design without history in mind was straight forward:
    One table with polls in it, one table with users in it, one relation from user to poll with the actual vote in it (only thumbs up/down for now).

    Now if a user comes later and changes the vote I change the relation. To keep his "vote history" i would have to create a second table with all the vote changes in it. But this history table is basically the same as the vote-relation table just with timestamps. So the original vote-relation would be redundant. I could just keep this history vote relation table and if I want to know someones current vote I just ask for the newest entry connected to this user.

    How would you handle this problem? One temporal table, 2 tables (but redundant?), with the 2 table solution, should I user triggers to updated it every time?

    Any other suggestions?

    I read about temporal databases but didn't find out wether they are really already major.

    thx
    LastBoyScout

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Ok, I'll take a go at thsi one because no one else has

    First off I'd like to say that I really like this question; not only is it well thought out and presented; it's also a big head scratcher!

    Ultimately, I think both your suggestions could be deemed correct in their own right, so really we're looking for the optimum of the two.

    So we'll start with option 1 - history table with trigger inserts.

    My biggest concern with this method is the separation. Finding all the votes a user has made involves a join between the tables; but then again, how often do you need to find this out?
    On the positive side it makes finding out the current results much easier!

    Now, I imagine the trigger to go something like this:
    Code:
    If user hasn't voted yet Then
      Create new vote record
    Else
      Create history record and update current vote record
    End If
    Which gives you a very funny (almost double) "UPSERT" effect... which is pretty nacky, don't you think?

    Also imagine that piece of logic running on every transaction to your votes table (you can see why some people don't like triggers, eh!).


    Onwards to option B - timestamp each record and only select the top 1 per user

    Pros:
    • easy to find all of the user actions
    • no extra insert overhead

    Cons:
    • Getting current result information becomes slightly harder which results in some extra overhead
    • Changes the relationship between users and votes

    So that's my little synopsis, ultimately neither method is ideal, you'll have to way the differences up yourself (imo).
    I hope you found it an eency bit useful












    1 and B was deliberate in case you were wondering ;-)
    Last edited by Pat Phelan; 02-13-08 at 12:28.
    George
    Home | Blog

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Oh, and just to point out that I only covered the points that immediately popped into my head, so undoubtebly there's lots missing

    Hopefully my response will spark some more interest from other users though!


    Note to Pat; any reason for the edit matey?
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good going George. One thing - you don't need that conditional logic - the triggers are specific to insert, update and delete events (although you could create one trigger to handle them all if you wanted).

    Is this is SQL Server?

    If I went with option A... I mean 1.... I would just insert a timestamped record into the history table for inserts as well as updates. Basically it would contain current records as well as historical. KISS

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by georgev
    Note to Pat; any reason for the edit matey?
    To fix your typo in the /LIST tag.

    -PatP

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh cool - is that a new servcie?

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    On this machine it is a daemon.

    -PatP

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I too fixed it as soon as I had posted it
    Sniped on my own edit!

    With regards to the trigger logic I was coming from the standpoint of using the database instead of the FE to apply the logic
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    With regards to the trigger logic I was coming from the standpoint of using the database instead of the FE to apply the logic
    Agreed - me too.

  10. #10
    Join Date
    Feb 2008
    Posts
    6
    Thx for the reply,

    "UPSERT" effect.
    I am not a native english speaker and I am not sure what you mean with that hehe.

    With regards to the trigger logic I was coming from the standpoint of using the database instead of the FE to apply the logic
    Again, I didn't understand FE

    I am still not quite sure wich way to go. Option A err 1 (hehe) makes it easy to output a report like "you changed the vote from up to down" or "from up to nothing/neutral". To be honest option 2 feels cleaner to me but to have the "action history report" outputed in a nice way I have to check the previous entry of a current entry to generate the appropriate sentence. ("You voted up" vs. "You changed your vote vom down to up").

    Anyways, have you ever heard of temporal databases? The way I understand it it basically automatically timestamps everything... and SQL is extended to a "TSQL". It's very interesting but I haven't found an implementation I trust enough to have it handle bigger databases (currently using MySql).

    thx
    LastBoyScout

  11. #11
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    FE = Front End. i.e., the client. BE = Back End, or the database server.

    A temporal database isn't a database type, like mysql or oracle - it is a database design. You can design a temporal database using mysql, postgresql, sql server, oracle, DB2, dBase, foxpro, firebird, ... or even Access!. So, you could continue to use mysql, or, if you need more capabilities, move up to postgresql (if you want to stay open source.)
    Last edited by loquin; 02-14-08 at 14:02.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    UPSERT is a made up word. It is formed from UPDATE and INSERT. The idea is it is a single operation where if there is a record in the table that matches the primary key of a record you want to write then you update the existing record. If there is not then you insert the record. It is known as MERGE in ANSI SQL.

    Also - google is your friend:
    http://www.google.co.uk/search?sourc...e+Search&meta=

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump
    UPSERT is a made up word. It is formed from UPDATE and INSERT.
    Yes, the term INDATE never caught on,for some reason.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2008
    Posts
    6
    Hi,

    thx for the two word definitions. True, I should have googled upsert, I am sorry.

    I thought a temporal database is a different kind of database. Sure you can "handcode" the temporal aspect in mysql or whatever but isn't a temporal database supposed to manage the "boilerplate code" for you? Even wikipedia says: A temporal database is a database with built-in time aspects, e.g. a temporal data model and a temporal version of structured query language.

    Mysql doesn't support a temporal version of SQL, does it? I was looking at http://www.cs.aau.dk/~csj/Thesis/ and http://www.timeconsult.com/Software/Software.html for example. It's not quite the same the way I understand it.

    In TSQL you can for example say "select snapshot ..." to have a plain old query on a non-temporal database in the most current (is this english?) state.

    But then again I think I may just be splitting hairs.

    thx
    LastBoyScout

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Be careful using the term TSQL. Far, far more commonly this would be taken to mean Transact SQL - the dialect of SQL used in SQL Server (and Sybase????).

    I have no idea WTF Temporal SQL is.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •