What structure and queries should be made for the directory?

Question

MySQL

Catalog. In addition to household chemicals, there are other main categories. Each product can have a different number of nested categories.

spoiler

The menu on the website will be:

spoiler

Category table:

spoiler

Product table:

spoiler

1. Correct structure for such a directory?
2. And how to make inquiries?
It's one thing if a visitor clicks on "Antibacterial", we show products whose parentCategory_id=7
And if a visitor clicks on "Soap" or "Household chemicals"... how to show all products in this category, including all subcategories?

Answer 1

normal structure. Just writing in the parentCategory_id table is stupid, for two reasons. First, the product does not have any parent category. And there is just a category. Second, getting in the way of naming styles is like walking around in dirty pants. Traditionally, databases use snake style in lower case. So just category_id
The second point applies to the category table too. so just parent_id
How to get all child categories - in general, no difference. it is possible with recursion, but to make it very simple, I suggest stupidly adding a path field in which to enumerate all parent categories through a dot. For example, for liquid toilet soap, the path will be 1.2.3.4.6
, respectively, if the client is waiting for soap, then we get the path from the soap, and write in the WHERE query path like "1.2.%"
At the same time, sorting the tree of categories mono will be a simple sorting order by path

Answer 2

To begin with, we read how trees can be stored: https://habr.com/ru/post/153861/
I would do it differently:
1) Table with a hierarchy of categories tbl_category using the Nested Sets method
2) Table of goods tbl_product
- id,
- name ,
- art,
- price
3) Table linking a product with a category tbl_product_category
- product_id
- category_id