By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,462 Members | 1,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,462 IT Pros & Developers. It's quick & easy.

Anyone know how to implement....

P: n/a
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;
}
Apr 4 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
a wrote:
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

Apr 4 '06 #2

P: n/a

a wrote:
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

Apr 4 '06 #3

P: n/a
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?

Apr 4 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.