How to realize rating system

Hey there!

How would you realize a rating system in a MySQL Database?
What is the best solution to have good performance?

Users should be able to give a +1 or -1 for a entry. I need to know what a user has (if) already voted for.

Possibility 1:
Table "entries"
[e_id] [name] [rating]

Create a column "rating" where I have saved the total rating points. When a user votes, update this column.

Table "users"
[u_id] [name] [has_voted_+] [has_voted_-]
1 testuser 1_5 2

The user has voted positive for the entries 1 and 5 and negative for 2.

pro: no count() needed.
contra: bad solution, no single entry for columns, uses update etc.

Possibility 2:
Create a table
Table "votes"

[v_id] [e_id] [u_id] [type]
1 5 12 1
2 5 18 0

The user 12 has voted positive for the entry 5, the user 18 has voted negative for it.

pro: clean solution.
contra: to get the rating for a entry i need to count() all positive, then all negative and add them. Loses to much performance.

Solution: how does mysql_num_rows in PHP work? Is it faster than count()?

This database will get really big, so possibility 2 isn't really a possibility because of its performance.

Anyone got an idea?

 

You need combine both:
table entries from 1 and table votes from second.
update total rating with trigger or UPDATE statement.