Most Efficient Design for Hierarchy Table

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 2008 Forums

Transact-SQL (2008)

Most Efficient Design for Hierarchy Table

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-07-17 : 05:35:23

We have two tables in our eCommerce system that are hierarchical.

The Page Tree and the Category Tree

The Page Tree uses a second table of "hierarchy" data that is adjusted when Page records change / move / are added. It has Level, ParentID, HadChildrenFlag, SibblingID, Path and WalkingOrderSeqeunceNumber

The Category table just has ParentID and Level.

Data (i.e. as displayed on the site) from both is heavily cached so I haven't been too worried about table structure being efficient.

However, we now have a need to dynamically show Category Tree (only showing Categories relevant to the user's filtered query) with numbers of associated products etc.

Category does not change often, so I'm happy to warehouse / denormalise / redundancy-ise the data.

I can live with fixed number of levels. 4 is probably enough.

The most common query is "All children from a given starting point"

Some sample data:


CREATE TABLE #Category
(
	[Code] varchar(30) NOT NULL,
	[Level] tinyint NOT NULL,
	[Sequence] smallint NOT NULL,	-- Tie-break for display order
	[Parent] varchar(30) NULL,
	[BrandCode] varchar(10) NULL,	-- Associated Brand
	[IsActive] bit NOT NULL,
	CONSTRAINT PK_Category PRIMARY KEY CLUSTERED 
	(
		[Code] ASC
	)
)

CREATE NONCLUSTERED INDEX IX_DNG_CTL_CAT_Parent ON #Category 
(
	[Parent],
	[BrandCode],
	[IsActive]
)


INSERT INTO #Category
SELECT		'TOYS', 	1, 1, NULL, 'BRAND_A', 1 UNION ALL
  SELECT	  'BOYS', 	2, 1, 'TOYS', 'BRAND_A', 1 UNION ALL
    SELECT	    'CARS', 	3, 50, 'BOYS', 'BRAND_A', 1 UNION ALL
    SELECT	    'GUNS', 	3, 50, 'BOYS', 'BRAND_A', 1 UNION ALL
  SELECT	  'GIRLS',	2, 2, 'TOYS', 'BRAND_A', 1 UNION ALL
    SELECT	    'DOLLS', 	3, 60, 'GIRLS', 'BRAND_A', 1 UNION ALL
    SELECT	    'GAMES', 	3, 50, 'GIRLS', 'BRAND_A', 1 UNION ALL
  SELECT	  'UNUSED', 	2, 3, 'TOYS', 'BRAND_A', 0 UNION ALL
SELECT		'CLOTHES', 	1, 1, NULL, 'BRAND_A', 1 UNION ALL
  SELECT	  'BOYCLOTHES',	2, 1, 'CLOTHES', 'BRAND_A', 1 UNION ALL
  SELECT	  'GIRLCLOTHES',2, 2, 'CLOTHES', 'BRAND_A', 1

SELECT	*
FROM	#Category

SELECT
	[Code] = REPLICATE('..', Level-1) + C_Code
-- SELECT TOP 100 *
FROM
(
	SELECT	[GP_Code] = GP.Code,
		[P_Code] = P.Code,
		[C_Code] = C.Code,
		[Path] = COALESCE(RIGHT('00000' + CONVERT(varchar(5), GP.Sequence), 5) + '.' + GP.Code+'/', '')
			+ COALESCE(RIGHT('00000' + CONVERT(varchar(5), P.Sequence), 5) + '.' + P.Code+'/', '')
			+ COALESCE(RIGHT('00000' + CONVERT(varchar(5), C.Sequence), 5) + '.' + C.Code+'/', ''),
		C.Level,
		C.Sequence
	FROM	#Category AS C
		LEFT OUTER JOIN #Category AS P
			ON P.Code = C.parent
		LEFT OUTER JOIN #Category AS GP
			ON GP.Code = P.parent
) AS X
ORDER BY [Path]

-- DROP TABLE #Category

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-17 : 08:35:30

Kristen,

what would you need to do?

Kristen
Test

22859 Posts

Posted - 2010-07-17 : 12:03:34

Thanks slimt_slimt

I want to do this in the most efficient manner. So perhaps just storing the PATH on the Category row is best - in order to avoid having any joins?

But maybe there are other methods available to me?

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-17 : 12:34:06

this might be the fastes / efficient manner. As long as you know that hierarchies will not change, otherwise expect lots of work updating this relations.

the other solution to this query is to use CTE.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-07-17 : 13:28:16

Or the HierarchyID data type.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources