php - How to implement search for a tagging system? -

many types of systems use tags organize content, including blogs , stackoverflow. let's i'm making movies website , want users able tag movies, search movies using combinations of tags.

here's sample database structure:

movies: id, name  movie_tag_options (used provide users list of available tag options): id, name  movie_tags: id, m_id, mto_id 

let's there's movie called "transformers 9 - explosions" , has tags "action", "pg-13", , "terrible movie". want users able find movie performing search "action,pg-13,terrible movie".

i explode string of tags in php array of tags:

action pg-13 terrible movie 

so question is, how use tag names find movies these tags? movies can have other tags well, should returned if have of tags in search.

the solution can think of denormalize tag name , store in movie_tags movie_tag_options (i.e. adding duplicate name column movie_tags), constructing query in php generates join statements each tag. this:

select id, name movies join movie_tags mt0 on mt0.name = "action" join movie_tags mt1 on mt1.name = "pg-13" join movie_tags mt2 on mt2.name = "terrible movie" 

the join lines generated via php , inserted query.

the downside approach storing tag names in 2 tables instead of one.

is there better way?

or can use

select a.name, count(c.id) c movies join movie_tags b on a.id = b.m_id join movie_tag_options c on b.mto_id = c.id c.name in ('action', 'pg-13', 'terrible movie') group a.id having c = 3;