[SOLVED] Setting up Table Structure for multiple Categories & Subcategories

[moagrius]

Hi,

 

What's the best approach for table structure when using multiple levels of categories and subcategories for each item?

 

For example, say I a table "Items", and each is to have an FK to a category, and I have multiple "top-level" categories like "Clothes", "Shoes" and "Jewelry".

 

Within "Clothes" there's "Tops", "Bottoms", and "Dresses".

 

Within "Tops" there's "Tees", "Tanks", and "Jackets".

 

Within "Jackets" there's "Sleeved" and "Sleeveless".

 

etc.

 

My first instinct is to create a single "Category" table, and have each Category use a Key to another row in the same table...

 

So if "Clothes" has a PK ID of 1, then "Tops" would have a "Parent Key" of 1, and an PK ID of say 3, so "Jackets" would have a Parent Key of 3, and a PK ID of say 10, and Sleeves would have a Parent Key of 3, and a PK ID of say 22...

 

Is this approach wise, or even feasible?  If so, how would retrieval work - to get all "Tops" how would I query it?  I presume there's some kind of "self-join" operation I'd need to use...?

 

"select * from Items [some kind of self join] where Items.Parent_Key = 3"

 

But would that grab "Sleeveless", since it's Parent Key is not 3, but it's parent category's parent category Parent Key is...?

 

Sorry if this is stated poorly - I'm having trouble getting my head around it.

 

Any input appreciated.

 

TYIA.

[xtopolis]

This article on mysql.com explains different approaches to the problem you described.

[moagrius]

That article was very helpful, thanks