Re: Hierarchies within Database

From: BP Margolin <bpmargo_at_attglobal.net>
Date: Sat, 4 Aug 2001 22:19:49 -0400
Message-ID: <3b6cab63_3@news3.prserv.net>

Anthony,

If you have opportunity, you might consider picking up a copy of Joe Celko's "SQL for Smarties" and/or "Advanced Transact-SQL for SQL Server 2000" by Itzik Ben-Gan and Tom Moreau.

Joe's book has a chapter titled "Trees" which goes into some depth on a set oriented approach to hierarchies.

Itzik's and Tom's book has a different approach to implementing hierarchies in a relational database. Although this book is geared toward a SQL Server implementation, the technique is not dependent on SQL Server proprietary features, and can be implemented in any RDBMS.

---

BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.

"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 Sat Aug 04 2001 - 21:19:49 CDT