Let's say you are storing information about coffee shops, and you want to assign an arbitrary number/assortment of static tags/labels to each cafe which act like attributes (i.e., the cafe either has the attribute or it doesn't). So for example, you could have one cafe which has the following tags/attributes: (
open-late) and another cafe with (
serves-food) and a final with (
How do I structure my data and queries to be able to e.g., do complex and grouped AND/OR statements such as (pseudo code)
tags CONTAIN ('mid-price' OR 'high-price') AND 'serves-food'?
Can this be achieved using a relational database, or should I be looking at using NoSQL e.g., sub-documents in mongo? I want a solution which scales well, to handle potentially 10,000s cafes and even greater numbers of tags.
In MySQL I'd be tempted to have 3 tables – one for
coffee_shops, one for
tags and a 3rd 'mapping' table having a many-to-many relationship with the other 2 tables called
coffee_shop_tags, however I'm not sure how to structure the queries to do this efficiently without doing a 'dumb' select against all referenced tags, then refining the list of coffee shops in application code.
What would be your recommended approach?