Hi everyone!!
I have problem while designing the database tables for one of my projects. I have a product database where the products are divided into various categories and subcategories under them. The heirarchy of these categories are such that one category has multiple no. of subcategories under them and correspondingly each of them has multiple child under themselves. This means the tree will be huge with a various branches and roots.

Now I want to avoid this, and reduce the no. of parent & child tables every time a new category or subcategory of products comes up.

How can I do this using the min. no. of tables? Can I do it with only one single table? Each product has a unique ID which is the Primary Field. So how can use the IDs of the categories & subcategories to arrange the in one table so that at the time of retrieval I should not have any problems.

Any body with better idea of database fundamentals and designing will be of great help!!
Thanks in advance

This is a difficult subject, certainly beyond the scope of postings we can do here.

You should obtain the book, "Joe Celko's Trees and Hierarchies in SQL" for a thorough treatment of the subject. You can also look in his book "SQL for Smarties" where he discusses the basic concepts of representing a tree in an SQL database.

You might also try Google for "SQL Nested Sets" and "SQL Adjacency List".

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com