I have got two tables, Item and SubItem. Item is a Parent of SubItem. Any SubItem can children Items.

-------
Item 
-------
ItemId
Desc
----------

-----------
SubItem
-----------
SubItemId
ItemId
SubSubItemId
Desc
-----------


Where SubSubItem contains a key from the Item Table

So an Item can have multiple SubItems. A SubItem doesn't have to, but it can have one Item, which in turn can have multuple SubItems, and so on.

I hope that makes sense... If it does, then how do I prefetch this?

An example of how this is used

A hamburger can have an Item. SubItems could be Fries Or Salad. An Item to for Salad can be dressing. Sub Items for dressings can be Fat Free or Full Fat Or what ever other dressings may exist.

This sounds like the classic example used to illustrate the decorator design pattern! Not the database design, I mean, but a menu item that can have an unknown number of condiments.

I agree, this is a basic 'hierarchy in 1 table' approach, so every item is also a subitem, unless it doesn't have a parent. What the item really is is stored in another table related to the item (decorated)

You can use the 1-table approach, or use the 2 table balanced tree approach like discussed here: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3670 (scroll down to the bottom)

Thanks alot, that worked great.