Database schema for multiple category / product relationships

Question

I want to create a database for an e-commerce application with category / subcategory management. Please suggest a database scheme where we can create categories and subcategories and add products to these categories. Each product can have several categories, and we can select products belonging to several categories using a Boolean database query.

thank

Answer 1

For categories and subcategories at any level, along with products belonging to several categories, I started with:

Categories:
    category_id
    parent_category_id foreign key (Categories.category_id)
    ... other category information ...
    primary key (category_id)
Products:
    product_id
    ... other product information ...
    primary key (product_id_id)
ProductCategories:
    product_id foreign key (Products.product_id)
    category_id foreign key (Categories.category_id)
    primary key (category_id,product_id)
    index (product_id)

, , " " .

---

, (, 3 4), :

select a.product_id
from Products a, Products b
where a.product_id  = b.product_id
  and a.category_id = 3
  and b.category_id = 4

Answer 2

. products categories, . , . 3 : , category_id product_id. , , category_id product_id. 3 .