Re: Hierarchies within Database

Anthony,

You didn't mention what platform you are on, but if it is SQL Server, refer to the topic "Expanding Hierarchies" in Books Online. You can also search the Google UseNet
archives for articles by Itzik Ben-Gan. Itzik has posted many solutions for hierarchy problems. His SQL Server code examples have been tested and they really work. :-)

http://groups.google.com/advanced_group_search?q=author:itzik+author:ben-gan &hl=en&safe=off

Linda

"Anthony Robinson" <arobinson6_at_qwest.net> wrote in message news:Fx%a7.786$nd4.246803_at_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 Sun Aug 05 2001 - 04:15:31 CEST