Hello,
having just started learning php and MySQL I've decided to set myself the task of designing a movie website(kind of a similar idea to imdb).

I want users to be able to view films by category, however I want one movie to be able to have several categories.

My question is, how should I go about choosing my tables?


My initial idea is to have seperate tables for Movie and Genre and have a field for genre in table Movie allowing multiple genres seperated by commas. In doing this I would have to presumably extract information using the php "explode" function to split the string into an array.

Thanks

A much better idea would be to create a linking table, that has foreign keys to both the Movie and the Genre table (and those foreign keys combined can be its primary key). You can also include any additional information about the relationship such as the date it was created, the date (if any) it was invalidated, who created it, etc.

-PatP