Suppose I am allowing users to vote only if they are registered users.
I should limit each user to vote only once per article?
I should maintain a table with 4 columns - articleid, userid, bit (to indicate positive/negative) and a datetime column.
Do you still see this being abused?
to obtain net rating, I would query the table twice - once to count all votes that are positive and once to count all votes that are negative.
Please tell me any flaws/optimizations in my approach that you see.
Keeping voting to registered users avoids abuse, revoting through different IP adresses.
To obtain the net rating, you query the table once, to sum all the votes, or better yet, you keep a tally on each item of the final vote. Voting once means you increment or decrement the rating of the item.
EDIT: I must insist that querying multiple tables is bad practice if you can pre-calculate it. Breaking up relational models is a big part of database optimisation.
Restricting voting to registered users is the way to go to avoid possible abuse. However once a user has voted you may want to think about whether they can undo their vote. I'm sure that won't happen often but something to think about.
BTW, you won't need to query the database twice as you could get the two values through a group by query, like so:
SELECT Vote, COUNT(Vote)
FROM tVotes
GROUP BY Vote, ArticleID
HAVING ArticleID = 1