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

Question

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

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 2

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.