svcghost — 2011-06-25T22:39:04-04:00 — #1
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?
ideaID (auto increment prim key)
ideaText (actual paragraph of text detailing idea)
userID (userid of user that submitted idea)
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 ??
svcghost — 2011-06-26T02:02:32-04:00 — #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!
r937 — 2011-06-26T00:23:47-04:00 — #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
svcghost — 2011-06-26T00:27:06-04:00 — #4
r937 — 2011-06-26T00:34:32-04:00 — #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 )
, <font color='"Blue"'>INDEX ( userid , ideaid )</font>
, 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