I use mysql to store a huge number of concerts. I use foreign keys to relate tables to each other. I have the next tables:
bands (id, band)
cities (id, city)
genres (id, genre)
venues (id, venue)
I guess these tables don't need an explanation as it's pretty clear what's stored in them. Finally, I have a table called "shows" that stores the show information and references to the earlier mentioned tables using foreign keys:
shows (id, band_id, city_id, genre_id, venue_id)
Now I want to implement a search function on my website. I want users to be able to search through all tables at once. So the user can enter the search query "madonna" to find all Madonna concerts. But if he enters "pop new york" he should be able to see all pop concerts taking place in NY. Or if he enters "knitting factory madonna" he should be able to see all madonna concerts that take place in the venue knitting factory. I guess you get what I mean...
So basically I need a search query that returns the ID of all shows matching the user's search query. I read about MATCH() AGAINST() but I also read it's impossible to search multiple tables with it. Is that correct?
Or would I'd better search my database using LIKE as the tables i'm searching contain just single words (venue names, city names, genres...), not really texts.
