This is just something I was wondering about how to get done, I’m not really doing it currently, just interested. Anyway, say you have a category entity and a product entity. The category entity has a 1:M recursive relationship with itself and a 1:M relationship with the product entity. Basically a category has more subcategories and each category has more products.
So I’m wondering if this is a cool model to have when say grouping products in categories and dealing with categories and sub categories in general.
If we simplify, this is what we have then.
CATEGORY
--------
CategoryID (PK)
ParentCategoryID (FK)
Name
PRODUCT
-------
ProductID (PK)
CategoryID (FK)
Name
So let’s say we have a root category called ‘Computer parts’ that has the ParentCategoryID FK set to NULL. Then we have subcategories ‘GPUs’ and ‘CPUs’. Assuming that ‘Computer parts’ has the CategoryID of 1, if we wanted to display all computer parts we would just run.
SELECT Product.Name FROM Product, Category WHERE Product.CategoryID = Category.CategoryID AND Category.ParentCategoryID = 1;
Right?
Now say ‘GPUs’ had two sub categories called ‘Nvidia’ and ‘ATi’ and ‘CPUs’ had two subcategories called ‘AMD’ and ‘Intel’. So now the query we’d have to run to get all products would be
SELECT Product.Name FROM Product, Category WHERE Product.CategoryID = Category.CategoryID AND Category.ParentCategoryID = Category.CategoryID AND Category.ParentCategoryID = 1;
Would this be OK? Looks dodgy.
Anyway, I want to know if there’s a way to display all products no matter how many categories you have and how to display all products that fit under a certain category even if it has n sub categories. And generally how to deal with recursive relationships and categories within databases. Thanks.
