Re: Hierarchies within Database

Anthony,

Allow me to toss in another possibility:

You could try three tables, i.e.

PRODUCT FOLDER TABLE

ProductFolderID        Identity            NOT NULL
ProductFolderName      Varchar(64)         NOT NULL

PRODUCT TABLE
ProductID              Identity            NOT NULL
ProductName            Varchar(64)         NOT NULL
ProductFolderID        Identity            NOT NULL

PRODUCT VERSION TABLE
ProductId              Identity            NOT NULL
Version                Varchar(whatever)   NOT NULL

You would then have:

Product Folder 1       >> Product Folder Table
    Product 1          >> Product Table
        Version 1      >> Produc Version Table
        Version2       >> Produc Version Table
    Product 2          >> Product Table
        Version 1      >> Produc Version Table
Product Folder 2       >> Product Folder Table
Pruduct Folder 3       >> Product Folder Table

It means you have to keep track of three tables, but that's not too bad. And you could easily create a view across all three tables which would allow you to select all products by folder and version.

Anthony Robinson wrote:
>
> 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
 

-- 
====================================
To reply, delete the 'x' from my email

Jerry Stuckle
JDS Computer Training Corp.
jstucklex_at_attglobal.net
====================================