SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Rate Up or Down system

    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?
    Code:
    IDEAS Table
    ideaID                    (auto increment prim key)
    ideaText                   (actual paragraph of text detailing idea)
    userID           (userid of user that submitted idea)
    Code:
    RATINGS Table
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i forgot to mention, you will also need an index for the pair of columns in the other order --
    Code:
    CREATE TABLE ratings
    ( ideaid INTEGER NOT NULL 
    , userid INTEGER NOT NULL 
    , PRIMARY KEY ( ideaid , userid )
    , INDEX ( userid , ideaid )
    , 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict svcghost's Avatar
    Join Date
    Oct 2010
    Posts
    288
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •