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,420
    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

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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