voting once- design and optimization

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
julian_lp
Forum Contributor
Posts: 121
Joined: Sun Jul 09, 2006 1:00 am
Location: la plata - argentina

voting once- design and optimization

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
julian_lp
Forum Contributor
Posts: 121
Joined: Sun Jul 09, 2006 1:00 am
Location: la plata - argentina

Post 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
Post Reply