Re: SQL Table Design

Eric,
It's a category break down for a web site. It's really for more of a drill
down. Pretty much like something you would see on any e-Commerce web site
like amazon.com, newegg.com, etc.

There will be no recording/history of this data. It's just for simple
breakdowns, but thanks for pointing that out.

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


On Thu, Oct 8, 2009 at 08:56, DeLong, Eric <EDeLong@xxxxxxxxxxxxxxx> wrote:

James,

Honestly, I think this approach (Cat, SubCat, SubSubCat) may be hard to
justify. I suspect this may be a vocabulary issue, as much as anything
else. What sort of Category items would nest so deeply? Is there
really that much aggregation in Category items?

Could some of these category items actually be something else
altogether? Perhaps things like "Promo item", or "Discontinued items"
are in your category items table, and should actually be expressed as
flags in the product master, instead of ganging up in the category
hierarchy...

Is Category a volatile field? Does your sales history need to capture a
snapshot of the category hierarchy that applies to the product at time
of sales? If so, you would need to de-normalize the category hierarchy
into your sales snapshot, which could get expensive with deeply nested
hierarchies...

Regardless, the implementation is straight forward... One table for
each layer of the hierarchy.

Files
CAT1 k(catID) Category Master
CAT2 k(catID, sub1) SubCat master (Lvl1)
CAT3 k(catID, sub1, sub2) subSubCat (lvl2)
...

I'm really not sure, can you provide more details?

-Eric