. . I am refactoring an old MySQL database for a project. We have a products table (some 20k rows and it will possibly grow to 40k or a little more) and different types of categorization (currently 4 kinds). For simplicity's sake I'll create an example with only two obvious and common example types ("categories" and "tags"). It's just an example and I'm not a creative person, of course.
. . I need to get a list of all products with its categories and tags tied together. The (relevant and greatly simplified) database structure for this example would be as follow:
- Products table: id, name
- Categories table: id, name
- Tags table: id, name
- Products_Tags table: product_id, tag_id
- Products_Categories table: product_id, category_id
. . I can flatten the relationships using subqueries and GROUP_CONCAT (there is a good example in this article) with a relatively good performance, but I wonder if this is not a case for a document-oriented database like MongoDB in which I can simply save everything in a single document, like this:
- Products table: {id, name, categories: [], tags: []}
. . Going the document-oriented route I have the added benefit of being able to use different kinds of categories for different kinds of products, but my code would need to check for data consistency rather than leave it up to the database.
. . Any better alternative? Tips, ideas, similar experiences?
