Get items from child categories sql query
I have the below database structure:
[CATEGORY] category_id parent(default 0) title [PRODUCT] product_id title [PRODUCT_TO_CATEGORY] product_id category_id
Sample data for CATEGORY:
1 0 TV 2 1 PLASMA 3 1 LED . . ...
Sample data for PRODUCT:
1 Sony 2 Sharp 3 Samsung . ...
Sample data for PRODUCT_TO_CATEGORY:
1(Sony) 1(TV) 2(Sharp) 2(PLASMA) 3(Samsung) 2(PLASMA)
First of all I want to ask if my structure is good. For example should I assign both category and subcategory ids to each product or a single category works good?
Then based to the assumption of each product belonging to one category and a category may have a parent or more, how can I retrieve products for current and every child category ? For example hitting TV category should list Sony,Sharp and Samsung and not just Sony item.
Use NULL instead of 0. This will allow you to use a proper FOREIGN KEY (as illustrated below).
should I assign both category and subcategory ids to each product
You shouldn't. Just assign the most specific sub-category that applies to that product. All the super-categories can be inferred from the hierarchy defined in the CATEGORY table, so storing (even one of) them in the product is redundant.
Such redundancies can sometimes be justified for performance reasons, but then you have to balance them with data consistency. In this case, there is no good declarative way to keep the redundant data synchronized with the "master" data - you'd have to do it through triggers or (god forbid) application code and you'll probably introduce concurrency bugs in doing so, unless you consider your locking strategy very, very carefully.
Then based to the assumption of each product belonging to one category...
This assumption makes your model incorrect. To correctly model this N:1 relationship, you do not need the junction table PRODUCT_TO_CATEGORY, you simply need a foreign key between product and category:
NOTE: Make the PRODUCT.CATEGORY_ID NULL-able if you want to allow category-less products (i.e. your relationship is actually N:0..1 instead of N:1).
And your data would look like:
CATEGORY: 1 TV NULL 2 PLASMA 1 3 LED 1 PRODUCT: 1 Sony 1 (TV) 2 Sharp 2 (PLASMA) 3 Samsung 2 (PLASMA)
how can I retrieve products for current and every child category?
First identify all categories that belong to the desired "sub-tree", then retrieve products connected to them. In this case, searching for all TVs can be done like this:
Get the ID of the "top" category you are searching for: 1 (TV).
SELECT CATEGORY_ID FROM CATEGORY WHERE TITLE = 'TV'
Get the IDs of all the child categories: 2 (PLASMA) and 3 (LED).
SELECT CATEGORY_ID FROM CATEGORY WHERE PARENT_ID IN (1)
Repeat the step (2) until you reach the "bottom" of the hierarchy:
SELECT CATEGORY_ID FROM CATEGORY WHERE PARENT_ID IN (2, 3)
The above query will return an empty result set for your example data, so you'll know you can stop. In more elaborate hierarchies, you may need to repeat the above query several times, always replacing the IN list with the most recently retrieved "layer" of categories.
Beware of infinite iteration if you happen to have a cycle in your data!
Get all the products connected to any of the identified categories: 1 (TV), 2 (PLASMA) and 3 (LED).
SELECT * FROM PRODUCT WHERE CATEGORY_ID IN (1, 2, 3)
Unfortunately, MySQL doesn't support the kind of recursive query that would allow you to do all that in a single database round-trip.
Read about nested sets for grouping products. http://en.wikipedia.org/wiki/Nested_set_model
It allows you to create a nice structure in a relational database to assign products to categories. Much better than the typical child->parent model most people come up with.