Rate Up or Down system

Databases
1

Hey guys,

What are your opinions on rating systems in this case? I have users submitting ideas. Now other users can rate an idea UP or DOWN. I want to keep track of what user rates what idea, up or down, and want to be able to display this information on a page of my site, ordering by highest rated ideas or lowest rated ideas.

Is this the best way to go about this?

[B]IDEAS Table[/B]
ideaID                    (auto increment prim key)
ideaText                   (actual paragraph of text detailing idea)
userID           (userid of user that submitted idea)



[B]RATINGS Table[/B]
ratingID       (auto increment prim key)
rateType       (single boolean, 0 for rate down, 1 for rate up)
userID        (userid who rated)
ideaID        (ideaid of the idea being rated)

So is this approach the best approach here? The only thing I fear is (definitely hypothetical) what if there are 1 million users, and 1 million ideas, and they each make ratings on many ideas. This means there will be millions of records in the ratings table. Is this still efficient ??

2

When I select two columns to make a primary key in PHPMyAdmin it doesn’t let me. When you make a 2-paired primary key, do you just click the UNIQUE key button? Or am I missing something here.

EDIT: Plz disregard. Got it! Thanks so much friend!

3

remove ratingid from the ratings table, make the primary key a composite key consisting of the userid and ideaid, and instead of boolean ratetype, use a TINYINT rating

yes, this structure will be efficient for millions of rows

4

Thank you! :slight_smile:

5

i forgot to mention, you will also need an index for the pair of columns in the other order –

CREATE TABLE ratings
( ideaid INTEGER NOT NULL 
, userid INTEGER NOT NULL 
, PRIMARY KEY ( ideaid , userid )
, [COLOR="Blue"]INDEX ( userid , ideaid )[/COLOR]
, rating TINYINT NOT NULL
);

queries which retrieve the average rating for a particular idea will use the primary key index, while queries which retrieve the ideas that a particular user has rated will use the other index

and of course the PK ensures that a given user can rate each idea only once