Elegant database design help... (MySQL/PHP)

Question

Hi, I'm building a movies website... I need to display info about each movie, including genres, actors, and a lot of info (IMDB.com like)...

I created a 'movies' table including an ID and some basic information. For the genres I created a 'genres' table including 2 columns: ID and genre. Then I use a 'genres2movies' table with two columns:movieID and the genreID, to connect between the genres and the movies tables...

This way, for example, if a movie have 5 different genres I get the movieID in 5 different rows of the'genres2movies' table. Its better than including the genre each time for each movie but...

There is a better way for doing this???

I need to do this also for actors, languages and countries so performance and database size is really important.

Thanks!!!

Answer 1

You are in the right track. That's the way to do many-to-many relationships. Database size won't grow much because you use integers and for speed you must set up correct indexes for those IDs. When making SELECt queries check out the EXPLAIN - it helps to find the bottlenecks of speed.

Answer 2

It sounds like you are following proper normalisation rules at the moment, which is exactly what you want.

However, you may find that if performance is a key factor you may want to de-normalise some parts of your data, since JOINs between tables are relatively expensive operations.

It's usually a trade-off between proper/full normalisation and performance

Answer 3

You're on exactly the right track - this is the correct, normalized, approach.

The only thing I would add is to ensure that your index on the join table (genres2movies) includes both genre and movie id and it is generally worthwhile (depending upon the selects used) to define indexes in both directions - ie. two indexes, ordered genre-id,movie-id and movie-id,genre-id. This ensures that any range select on either genre or movie will be able to use an index to retrieve all the data it needs and not have to resort to a full table scan, or even have to access the table rows themselves.