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.
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
hope this help
See clear as mud
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.