Movie Database, storing multiple genres
database-design, join, many-to-many, mysql, sql
Solution
I would suggest you should follow the following structure:
tablename: movies
movieid, title, plot, rating, director
> sample data:
>
> 1 titanic Bollywood 10 James Cameron
tablename: genres
genreid, genre
> sample data:
> 1 Horror
> 2 Thriller
> 3 Action
> 4 Love
tablename: moviegenres
moviegenresid, movieid, genreid
> sample data:
> 1 1 2
> 2 1 4
And the query is:
select m.*,group_concat(g.genre)
from movies m inner join moviegenres mg
on m.movieid=mg.movieid
inner join genres g
on g.genreid=mg.genreid
group by m.movieid
;
See the fiddle
Problem
I'm trying to build a database that will store information on movies. ``` Title Plot Genre Rating Director ``` The only thing that is bothering me is that most films don't just have one genre and I'm struggling to figure out how to store this on a MySQL Database. At first I was thinking that I'll just have one table and store all the genres in one column, separating them by a comma and when I want to retrieve them separate them using PHP, but I'm not sure this is the best way as I think I would have trouble sorting and searching for a specific genre e.g. Horror when the collumn contains 'Horror, Thriller, Action'.