Page 1 of 1

voting once- design and optimization

Posted: Tue Nov 21, 2006 5:57 am
by julian_lp
I've written a small script which allows users to vote (only once) for the articles uploaded.
So, I've taken this road to accomplish it:

tbl_votes's structure

id_tbl_votes (primary key)
fk_id_user_votes
fk_id_article_votes


Then, if the user "1234" votes the article "9876", I add a row like this:

id_tbl_votes => 1234#9876
fk_id_user_votes => 1234
fk_id_article_votes => 9876

(The vote's value itself goes directly to the article's table )

Now comes the question:

Is it better, when a user votes, try to insert the record and watch for duplicate key error, or select first "1234#9876" and see whether it exists or not?

How do you handle this kind of situation?

Posted: Tue Nov 21, 2006 7:03 am
by feyd
I'm not sure why you have an id field in a linking table. The link is enough. Typically one would create a unique key that uses both foreign fields.

edit: oops forgot to answer the rest.

Done in that fashion I will let the database yell that there was a duplicate key if I hadn't pulled out that users records already.

Posted: Tue Nov 21, 2006 7:22 am
by julian_lp
feyd wrote:I'm not sure why you have an id field in a linking table. The link is enough. Typically one would create a unique key that uses both foreign fields.
I'm not sure either what are you trying to say.
If you're talking about why I have

fk_id_user_votes
fk_id_article_votes

I use both fields to use in case I wanted to list what articles a certain user voted...

select ... from votes where fk_id_user_votes= "bla bla"

feyd wrote: Done in that fashion I will let the database yell that there was a duplicate key if I hadn't pulled out that users records already.
great, I think I'll follow that path