Anyone know how to implement....

[ºa¤Ö]

multi-level category in database and object design with good performance?

current my design like this

category {
id
category
name
}

however...when i try to retrieve it from database, except a loop, i cannot
find another solution

function getCategory(id) {
connection database;
rs = get category list basic on the provided id;
while (rs not EOF)
category = getCategory(list.item.id);
}

return category;
}

 

[Jon Skeet [C# MVP]]

multi-level category in database and object design with good performance?

<snip>

It's not entirely clear what you're after, but I suspect you need to
make your query do a join to get all the information you need in one
go.

If that doesn't help, could you provide more information?

Jon

 

[mpetrotta]

multi-level category in database and object design with good performance?

current my design like this

category {
id
category
name
}

however...when i try to retrieve it from database, except a loop, i cannot
find another solution

function getCategory(id) {
connection database;
rs = get category list basic on the provided id;
while (rs not EOF)
category = getCategory(list.item.id);
}

return category;
}

Sounds like you want to retrieve rows from a hierarchy. What database
are you using? I've found no other way than a loop to do this in SQL
Server 2000. I've chosen to implement it as a table-valued function:

CREATE FUNCTION GetCategories(@category int)
RETURNS @r TABLE (@category int)
AS
BEGIN

DECLARE @current int
SET @current = @category

WHILE (1=1)
BEGIN
-- walk up the tree, adding rows one by one
-- when we reach the top, break
SELECT @current = category FROM myTable WHERE category = @current
IF (@current is null) BREAK
INSERT INTO @r (category) values (@current)
END

RETURN
END

To call it, you would execute:
SELECT ID FROM dbo. GetCategories(@categoryID)
or
SELECT * FROM myTable WHERE category IN (SELECT ID FROM dbo.
GetCategories(@categoryID))

SQL Server 2005 has this functionality built in, using Recursive
Common Table Expressions. It's a lot like a view.

You may find this of interest:
<http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm> It has
ideas applicable for other databases besides Microsoft's.

Michael

 

[Bruce Wood]

How many categories (roughly) are we talking about?

Do you need to fetch the information every time? That is, will it
change during the duration of one run of your program?