Just take an example of this....
[ Footwear->men's->shoes ]->all product
[ Footwear->women's->shoes ]->all product
[ Clothes-->men's--->T-shirt ]->all product
[] implies category of product
So this is hierarchy of a site.
Product table
---------------------
pID Name CategoryId
1 Shoe1 here my question lies
1)What should be category table design?
catId ParentId Name
1 0 Footwear
2 1 men's
3 2 shoes
4 1 women's
5 4 shoes
6 0 clothes
I think this should be.Right?
2)If this is right,what should be product table design?
In product table which will be my categoryid?
As there is no child of shoes,so for men's shoe product "3" should be category id.Am I right?
So in menu if I click shoes from men's tab I can get 3 and by query I can fetch all men's shoes.
But if I click footwear,then all type of foot wears(shoes,ketos) of men and women are loaded.So how to do this?
If I create product table like
pid pname gender type1 type2
1 shoe1 men's shoe footwear
2 keto1 women's keto footwear
then query will be very simple.Clicking on footwear I can get all product of footwear.
clicking on men's I can get all product of men's.
But in this case unlimited hierarchy is not possible (because types are fixed in columns).
=>So my question is what is the best approach to do this type of job?
=>In this type of case how to create breadcrumb
on clicking footwear it should be Home>footwear
on clicking men's-shoes it should be Home>footwear->men's>shoes
Please help.