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.