Database schema for multiple category/product relationship

I want to design a database for an e-commerce application with category/subcategory management. Please suggest a database schema where we can create categor开发者_高级运维ies and subcategories and add products to those categories. Each product can have multiple categories and we can select products belong to multiple categories using a boolean database query

Thanks

For categories and sub-categories to any level, along with products belonging to multiple categories, I would start 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)

This way you can reach your goal of a hierarchy of categories as well as achieving a many-to-many relationship between products and categories.

You can select a product ID belonging to multiple categories (e.g., 3 and 4) with a query like:

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

This would be best solved with a join table. That is, you have a products table, and a categories table, each with a primary key. Then, you create a third table, which is the join table. It has 3 columns: it's primary key, category_id, and product_id. Then, when you want to add a relationship between a product and a category, you insert a row into the join table with the category_id and product_id that are related. You can then use the 3 tables together with joins to display the relationships.