Re: SQL Table Design

[James Perkins <jrperkinsjr@xxxxxxxxx>]

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