Hierarchies within Database

From: Anthony Robinson <arobinson6_at_qwest.net>
Date: Sat, 4 Aug 2001 18:28:16 -0500
Message-ID: <Fx%a7.786$nd4.246803@news.uswest.net>

I have a design question that has me completely perplexed - although part of me feels that I may be over-complicatiing things!!

The scenario is this: My company manufactures budgeting software. Our library consists of various families of suites, or product folders. (i.e. Web Suite, Client/Server Suite). Within those suites, there are specific software titles (i.e. Budgeter, Planner, Templates, etc.). And those particular titles have version numbers. We need to design a database (or tables) that will allow us to store a hierarchy of product folders and products.

Product Folder 1

    Product 1

        Version 1
        Version2
    Product 2
        Version 1

Product Folder 2
Pruduct Folder 3

As you can see, a product folder needn't have any products under it. A product folder can essentially be a product unto itself.

Here's one idea I had:
PRODUCT TABLE

ProductID                Identity            NOT NULL
ProductName          Varchar(64)     NOT NULL
RootFolderID           Identity            NOT NULL
ParentProductID        Identity            NOT NULL

PRODUCT FOLDER TABLE
ProductFolderID        Identity            NOT NULL

ProductFolderName Varchar(64) NOT NULL **FK relationship between two tables on RootFolderID/ProductFolderID

This woks somewhat but there's the issues of SELECT statements sorting on the ProductID. The table(s)need to be designed as such so that no matter where a new row is inserted into the product table, a SELECT statement will return the hierarchy in the right order based on the product folder and then by product, version, etc. It also needs to be designed as such to allow for essentially an infinite number of levels under a root node(product folder).

I would welcome any suggestions or insight into my little problem. Again, I think I may be over complicating things....

Thanks in advance for any help any can lend!!

Anthony Received on Sat Aug 04 2001 - 18:28:16 CDT