I have a table of content. Users can vote on a content row. They can vote up to three times. I'm looking for the most efficient way to make sure they don't go over three votes.
I can think of two main designs...
1) Each user can add one row per vote.
CREATE TABLE Vote int userId int contentId
2) Each user can add one vote row and then its quantity value is incremented until 3 has been reached.
CREATE TABLE Vote int userId int contentId int quantity
To do this properly (i.e. thread safe) I think the sequence for these cases would be as follows...
1) a) Read the parent User row and hold an update lock on it. b) Count the number of votes. c) If less than three then insert a row. d) Commit
2) a) Read the parent User row and hold an update lock on it. b) Do an exists on the vote row. c) If not there then add it. d) If there then check the quantity is below 3 and update if true. e) Commit
Any ideas? Any way around holding an update lock on User? (At the very least this always seem to be awkward to do with an ORM ;))
If I forget the transaction then I think method 2 is the safest and the worst case scenario is that the odd vote gets lost or a user gets a few more than 3 votes.
Any ideas on how best to achieve this?