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

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:
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 ;-)

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?

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

To fix your typo in the /LIST tag.

-PatP

Oh cool - is that a new servcie?

On this machine it is a daemon.

-PatP

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

Agreed - me too.

Thx for the reply,

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

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

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.)

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=

Yes, the term INDATE never caught on,for some reason.

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

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.