0

I am trying to design a database for nested categories of products, e.g. "men/shoes/sandals", "women/shoes", or "men/jeans".

I can model it with two entities Category and Product. The "subcategory" Category-to-Category relationship is many-to-many. Every Category has zero or many subcategories. Every Category has zero or many parent Categories. For example, "jeans" is a subcategory of categories "men" and "women", category "men" has subcategories "jeans" and "shoes", etc. I believe Category entities form a DAG (direct acyclic graph) and can be modeled with a "join table".

In this model a Product belongs to a path in this DAG. For example, a product can belong to path "men/shoes/sandal" but not to category "men" or category "sandal". Does it make sense ? How would you model this Product-to-Category relationship ?

Another problem is the order of products. I would like to display products in category men/shoes in some particular order. How would you suggest store this order in the database ?

Michael
  • 41,026
  • 70
  • 193
  • 341

1 Answers1

1

Since category-to-category is a many-to-many relation, a well-known approach to this is to create a many-to-many table, called

subcategory(id, parent_category_id, child_category_id)

Since products are not belonging to categories per se, but to a set of ordered categories, paths, this is how you could model it:

product_path(id, product_id, category_id, order_index)

where order_index represents the order in which the items of the path are sorted, so a path of men/shoes/sandals would be represented by 3 product_path records, the first being a connection between the product and the category of men, the second being a similar connection with the category of shoes and the third would be a similar category of sandals.

Since you intend to have a particular product order for each category, in this problem-space you have two main options:

  1. You can create a product_category_order(id, product_id, category_id, order_index) and store a record for each orders in the category
  2. You can create a product_category_order(id, category_id, products) table, where you would have a comma-separated value or a JSON for products

Option 2. violates normal form theory, but for practical reasons you may decide to put aside theory in this case. Really, the exact scenario that you have determines whether option 1 or option 2 are better matches for your task.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • Thanks ! Now I wonder how to get all products by nested category, e.g. "men/shoes/sandals". I will add a new question about it. – Michael Jun 06 '22 at 10:33
  • @Michael I thought it probable that this will be a next problem, as this was my thought process as well when I solved this kind of issue for the first time. Please let me know about your new question when you post it, also, when you ask the new question, it is advisable to share the exact schema that you have, along with sample data (possibly a SQL fiddle as well) and whether we are allowed to solve it using application code (if so, which one). Finally, if the maximum depth is known, that can be a helpful information as well. – Lajos Arpad Jun 07 '22 at 08:22