Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2015
    Posts
    1

    Database Design Ratings Table

    Hi Guys,

    I'm not sure if this is the right place for such an amateur question, but I had nowhere else to turn!

    I'm designing a simple Movie Database in MYSQL for a college assignment. I have 4 tables at the moment (Movies, Actors, Directors, Genres). The Movies Table has a Primary Key of Movie_ID and foreign keys which represent the primary keys of each of the other tables (i.e. Act_ID, Dir_ID, Genre_ID).

    I've decided that I'd like to implement a simple rating feature, that allows people who visit the page to rate movies.

    What I have at the moment is a Ratings table, which has a AUTO_INCREMENT Primary Key called Rating_No, and 2 other columns; Movie_ID and Rate. The person visiting the site inputs the movie_id into a html form along with their rating, and this is posted to the database. I then have a PHP

    However, this table is NOT related to the other tables in any way, so I feel like this is the wrong way of doing things but every other solution I've tried (such as having Primary Key as Movie_ID) would not work as that would only allow each movie to be rated once.

    I also tried having 2 Primary Keys (Rating_No INT AUTO_INCREMENT, Movie_ID INT) however when I try to assign movie_id in the movies table as a foreign key referencing this table I get an error.

    Can you suggest a way of doing this correctly?

    The end goal is to allow each movie to be rated more than once, and then be able to display the movie information back in a table in PHP using a SELECT Statement. (i.e. Movie ID, Movie Name, Director, Actor, Genre, Average Rating). I currently have this working, but as the table has no relationship I feel like its the wrong way of doing things.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    I would setup the rating table like

    rating
    rating_ID PK dont need it for any thing but table like to have a PK
    movie_ID
    User_ID
    Adate 'date it was added
    Score 'the score the user rated it.

    then by avging the score and grouping Movie_ID will get what you want.

    do the same for the Actors

    have a Actors Table
    Actors
    Actors_ID PK
    fname
    Surname

    them we need to
    have a Actors to Movie Table

    Act_Movie
    Act_Movie_ID PK dont need it for any thing but table like to have a PK
    Actors_ID
    Movie_ID
    Play_As
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

Posting Permissions

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