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.
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:
If user hasn't voted yet Then
Create new vote record
Create history record and update current vote record
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
easy to find all of the user actions
no extra insert overhead
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 ;-)
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
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).
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 13:02.
"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
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.
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.