dB schema help

[GUJUm0deL]

Hey all, I'm building an eCommerce site (in CF) and need need some guidance on dB schema.

I would like to be able to have unlimited categories, EX:
Home/Men's Clothing
Home/Women's Clothing/Dresses
Home/Women's Clothing/Summer Wear/Bathing Suits


portion in red has one main-category (Home) and one sub-category (Men's Clothing)

portion in blue has one main-category (Home) and a sub-category (Women's Clothing) and another child category (Dresses) under the sub-category (Women's Clothing).

portion in green has one main-category (Home), a sub-category (Women's Clothing), a child cateogry (Summer Wear) and one more level (Bathing Suits)

What is the best way of achieving this? I'm using MS SQL 2000.

Thanks!


_____________________________
Just Imagine.

http://www.imajinarts.com

 

[mrdenny]

Typically the way that many people do this in SQL 2005 and earlier is by having a ParentID field in the catagory table which referes back to the primary key in same table.

Unfornitually this can lead to some nasty queries to find out how many levels down you need to go to get to the parent.

If you can wait for SQL 2008 there is a new hierarchyid data type which is perfect for this sort of system. (

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1266498,00.html

about half way down.)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[GUJUm0deL]

Thanks for the link. I really can't wait for SQL 2008. I'm developing this for a client. Is there another way? Like recursive tables, look-up tables, etc?

_____________________________
Just Imagine.

http://www.imajinarts.com

 

[mrdenny]

Then your best bet will be the standard method of placing a ParentCategoryID column in the Category table and having the ParentCategoryID value reference the CategoryID value on the table something like this.

create table Catagory
(CatagoryID INT IDENTITY(1,1) PRIMARY KEY,
ParentCategoryID INT,
CatagoryDesc NVARCHAR(255))
GO

DECLARE @ParentCatagory as int
INSERT INTO Catagory
(CatagoryDesc)
values
(Home)

set @ParentCatagory = identity_scope()

insert into Catagory
(ParentCatagoryID, CatagoryDesc)
values
(@ParentCatagory, 'Men''s Clothing')

insert into Catagory
(ParentCatagoryID, CatagoryDesc)
values
(@ParentCatagory, 'Women''s Clothing')
set @ParentCatagory = identity_scope()

insert into Catagory
(ParentCatagoryID, CatagoryDesc)
values
(@ParentCatagory, 'Dresses')

insert into Catagory
(ParentCatagoryID, CatagoryDesc)
values
(@ParentCatagory, 'Summer Wear')
set @ParentCatagory = identity_scope()

insert into Catagory
(ParentCatagoryID, CatagoryDesc)
values
(@ParentCatagory, 'Bathing Suits')

In order to query the data you'll need to use a loop to build the string all the way down. Here are a few examples on the net on how to write these.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com