Thread: Database Design Ratings Table
11-22-15, 16:17 #1Registered User
- Join Date
- Nov 2015
Database Design Ratings Table
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.
11-22-15, 16:46 #2(Making Your Life Easy)
- Join Date
- Feb 2004
- New Zealand
I would setup the rating table like
rating_ID PK dont need it for any thing but table like to have a PK
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
them we need to
have a Actors to Movie Table
Act_Movie_ID PK dont need it for any thing but table like to have a PK
Play_Ashope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
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