Re: SQL Table Design

That looks good. The only other thing that comes to mind is that some folks add a sequence_number integer to allow sub-categories to be sequenced within category.

-Nathan.



----- Original Message ----
From: James Perkins <jrperkinsjr@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Thu, October 8, 2009 9:58:13 AM
Subject: Re: SQL Table Design

Thanks to all that replied. I think you have all solidified that the old
non-normalized database table should be a thing of the past :-) Just what I
wanted to here.

So here is the DDL for my table:
CREATE TABLE WEBVISION.ITEM_CATEGORIES (
CATEGORY_ID FOR CAT_ID INTEGER ,
CATEGORY_DESCRIPTION FOR CAT_DESC VARCHAR (128) ,
PARENT_CATEGORY_ID FOR P_CAT_ID INTEGER DEFAULT NULL ,
INCLUDE_CATEGORY FOR INC_CAT CHARACTER (1),
PRIMARY KEY (CATEGORY_ID),
CHECK(INCLUDE_CATEGORY IN('N', 'Y')) )
NOT VOLATILE RCDFMT ITEMCATR ;

Here is the SQL statement to find the children categories.

WITH temp_cat(category_id, parent_category_id, iteration) AS (
SELECT item_cat.category_id, item_cat.parent_category_id, 0
FROM mylib.item_categories AS item_cat
WHERE item_cat.category_id = ?
UNION ALL
SELECT item_cat.category_id, item_cat.parent_category_id, temp.iteration
+ 1
FROM webvision.item_categories AS item_cat
JOIN temp_cat AS temp ON temp.category_id = item_cat.parent_category_id)

SELECT *
FROM temp_cat

--
James R. Perkins
http://twitter.com/the_jamezp