Relational to Hierarchical data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Relational to Hierarchical data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-06 : 09:42:37

Jacques writes "Hello,

Here is my problem.
I want to categorize a list of items and present it by categories in a hierarchical manner.
The problem is that the categories are not know in advance, they are user defined and the priorities of the categories (The parent/child relationship between categories) has to be able to be modified at run time by the user.
I defined a relational schema, but I don't know the best way to retrieve the data so that it can be easily put into the tree structure.
The user can define attributes and assign a value for 0 or more attributes on each item.
The value of the attributes determines the categories.

Example:
The user defined 2 attributes: fruit and color and set values:
Item Apple Red
Item Prune Black
Item Pear Brown
Item Strawberry Red
Item Kiwi no color
Item no fruit Yellow
...

The tree ordered by Color first then Fruit would be:

Black
>Prune
Brown
>Pear
Red
>Apple
>Strawberry
Yellow
>no fruit
No color
>Kiwi

If the user re-orders it by Fruit then color, it would become:

Apple
>Red
Kiwi
>no color
Pear
>Brown
Prune
>Black
Strawberry
>Red
no fruit
>Yellow

Of course, this should work with more than 2 attributes.
It would be implemented with MS SQL server and C#.

Here is what I came up with so far, the procedure GetItemCategories retieves the categories, but I don't know how to establish the hierarchical orde with them.

Jacques PHILIP

CREATE TABLE [dbo].[Items] (
[ItemID] [int] IDENTITY (1, 1) NOT NULL ,
[ModuleID] [int] NOT NULL ,
[DisplayOrder] [int] NOT NULL ,
[MetadataXml] [nvarchar] (3000) ,
[ShortDescription] [nvarchar] (256) ,
[Keywords] [nvarchar] (256) ,
[CreatedByUser] [nvarchar] (100) NULL ,
[CreatedDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemAttribute_Value] (
[ItemAttribute_ValueID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemAttributeID] [int] NOT NULL ,
[ItemAttributeValue] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ItemAttributes] (
[ItemAttributeID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemAttributeName] [nvarchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Item_Attribute] (
[Item_AttributeID] [int] IDENTITY (1, 1) NOT NULL ,
[ItemID] [int] NOT NULL ,
[ItemAttribute_ValueID] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE PROCEDURE dbo.GetItemCategories AS

select ItemAttributes.ItemAttributeName,
COUNT(Items.ItemId) as PicNb,
ItemAttribute_Value.ItemAttributeValue,
Items.ItemID
from Items
join Item_Attribute on Items.ItemId = Item_Attribute.ItemId
join ItemAttribute_Value on ItemAttribute_Value.ItemAttribute_ValueID =
Item_Attribute.ItemAttribute_ValueID
join ItemAttributes on ItemAttributes.ItemAttributeID =
ItemAttribute_Value.ItemAttributeID
GROUP BY Items.ItemID,
ItemAttributes.ItemAttributeID,
ItemAttributes.ItemAttributeName,
ItemAttribute_Value.ItemAttributeValue
ORDER BY Items.ItemID,
ItemAttributes.ItemAttributeID,
ItemAttributes.ItemAttributeName,
ItemAttribute_Value.ItemAttributeValue
GO"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2004-05-06 : 21:37:42

Hi

You could do something like this : http://www.sqlteam.com/item.asp?ItemID=8866
but maintain different sets of lineage, one for each thing you want to sort by.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources