Database schema for multiple category/product relationship

4
votes

I want to design a database for an e-commerce application with category/subcategory management. Please suggest a database schema where we can create categories 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

2
phpmysqldatabase-design
What have you come up with so far? – Oded

2 Answers

17
votes

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
0
votes

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.