Search code examples
phpmysqlrating-system

5 star rating system mysql setup

Ok so I know there's other questions like this. But mine is a little different...

I'm going to have a list with all the information of these games and i need to display the games average rating along with their info...

so, I dont want have 2 tables 'games' and 'games_ratings' because then i cant do a simple

SELECT id, name, howtoplay, otherinfo, avrating FROM games ORDER BY id;

it would be nice if there is a clever way I could have a 5 star rating system, that remembers users and can display an average value all in 1 table along with the information.

I dont know a lot about the limits of mysql. Should I just run 2 queries at once? Ive always tried to limit each page to only one query. especially if my first query will be loading 50 games to display info about.

im aware of the system where the ratings table is like userid, songid, rating and you just select the average.

Solution

  • I am going to go out on a limb here as I think you may not only be new to mysql, but a bit new to modeling as well.

    I would recommend 3 tables for this idea.

    1. Users Table
    2. UserRatings Table
    3. Games Table

    Users table is used to store just that. User information. Possibly a username, password, first name, last name for example. The table should have a primary key. Call it UsersID. It should auto increment itself and be unique for every row.

    The Games table is next. Put a game name in it. It should have a primary key as well. Call it GameID.

    Lastly is the UserRatings table. I am a fan of primary keys over composite keys even in associative type tables, but you could go that route as well. Say UserRatingsId, Rating, InsertTimeStamp, UpdateTimeStamp.

    Now to store the average rating I would look at another table possibly, or putting a rating column inside the games table. Every time you insert / update a row in the UserRatings table you fire off a refactor of that column, or table.

    Also, joins are your friend. Do a bit of reading on them. Ignore outer, inner, cross, left, right etc until you are familiar with the concept of a simple straight join (and its called many things).

    Try some of the answers provided above and this one. I am guessing your questions will be much more targeted once you start playing around with it.

    Cheers

    Matt