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: (vegan-choices, high-price, open-late) and another cafe with (mid-price, open-late, serves-food) and a final with (low-price, serves-food).

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?

  • So i'm looking at using a single JSON column for tags in MySQL, and using statements such as: SELECT * FROM coffee_shops WHERE (JSON_CONTAINS(tags, '"mid-price"') OR JSON_CONTAINS(tags, '"high-price"')) AND JSON_CONTAINS(tags, '"serves-food"');. I've read MySQL does some internal optimisation on JSON columns, but is this a viable/scalable solution? My gut feeling is not. – Harry Lewis Oct 9 '18 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.