Getting the last answer id for the questions

In M.C.Q test, when user come back to the same question again how can I track the last answer given for the question?,

following is the table structure

Method: 1    
temptblUserAnswer
    id   uid   qid   ansid
    1    1     1     5
    2    1     2     6

Should I update the table value for that particular Question?

OR

Should I use following table structure?

Method: 2    
temptblUserAnswer
    id   uid   qid   ansid   timestamp
    1    1     1     5       2011-09-28 11:54:32
    2    1     2     12      2011-09-28 11:58:40
    3    1     1     7       2011-09-28 12:02:50

Here with the help of timestamp I can find the the last answer for any particular question.

Now the question is, which method to follow, and which will be p开发者_如何转开发erformance oriented?

Or any better suggestion for the same? Should I go with Method:1 and apply the index on uid, ansid column?

---

If your Id is autoincrementing, you can get last answer based on Id:

SELECT TOP 1 AnsID
WHERE Uid=@UserId AND Qid=@QuestionID
ORDER BY Id DESC

About performance:you should put non-clustered index on Uid AND Qid AND ID and Include AnsID

You can avoid of using timestamp column in this table.

---

1: For the first method:

To get the last answer id for a given question id, you can use this script:

   --index creation
   CREATE UNIQUE INDEX IUN_temptblUserAnswer_qid_id ON temptblUserAnswer(uid,qid,id DESC);

   --query
    DECLARE @QuestionID INT, @UserID INT;
    SELECT @QuestionID=123, @UserID = 456;

    SELECT TOP 1 ansid
    FROM temptblUserAnswer
    WHERE qid = @QuestionID
    AND uid = @UserID
    ORDER BY id DESC;

In this case, I have assumed that the last answer is given by the last temptblUserAnswer.id. Also I have assumed that temptblUserAnswer.id is IDENTITY(1,1). Problem: if somebody runs an ad-hoc insert on temptblUserAnswer table it's possible to get wrong results.

2: For the second method:

You should use a date/time data type with higher precision (for SQL version<=SQL2005: DATETIME is the only option, for SQL version>SQL2005 you can use DATETIME2(7) data type and SYSDATETIME() function instead of GETDATE()/CURRENT_TIMESTAMP functions). Even so, you could get two or more answers with the same timestamp. In this case, you could use 'temptblUserAnswer.id' column as the second criteria.

   --index creation
   CREATE UNIQUE INDEX IUN_temptblUserAnswer_qid_id ON temptblUserAnswer(uid,qid,timestamp DESC, id DESC);

   --query
    DECLARE @QuestionID INT, @UserID INT;
    SELECT @QuestionID=123, @UserID = 456;

    SELECT TOP 1 ansid
    FROM temptblUserAnswer
    WHERE qid = @QuestionID
    AND uid = @UserID
    ORDER BY timestamp DESC, id DESC;