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