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
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of James Perkins
Sent: Thursday, October 08, 2009 10:09 AM
To: Midrange Systems Technical Discussion
Subject: SQL Table Design
Hello All,
I'm in the process of creating a table to store a category and a
subcategory. It's possible for there to be multiple levels of this
structure, e.g. a subcategory may have another subcategory.
One of my co-workers suggested we just make a table with 10 category
columns. I don't think I really like this idea, it seems to like "old"
database design. With a recursive SQL statement I can go up or down to
find
the category hierarchy. To me this seems much more scalable.
So my question is, how are others handling this?
Thanks in advance,
--
James R. Perkins
http://twitter.com/the_jamezp
midrange.com
