danielk1: yes, that's a "nested sets" hierarchy.
For something like your sample, it's definitely the way to go. It's not likely that "Meat" will ever be moved out of the "Food" category, or that "Beef" will ever move out of the "Meat" sub-category.
That works just fine for relatively static hierarchies. No problem.
The problem I had to solve was what to do with a volatile hierarchy. In this case, a customer-facing web page that had hierarchical access control.
Joe works for company X, and he places an order that will have details on this web page. Joe can see the details, as can various people at X. Simple hierarchy, right? Joe then changes to working with company Y, and now people at X should no longer see his details, but people at Y should. The following week, he moves to Z.
At the same time, Bob moves from Z to X, and Sue moves from A to Y, and B adds twelve new people who will all be placing orders and who weren't even in your database.
And that's a typical hour for this hierarchy.
It's no longer as simple as adding "yellow" by expanding the range for "food". Even that isn't as simple as you might think. What happens in your example if you already have a category "dinnerware" that starts at 19? How do you then add "Vegetable" to "Food"? You can't add 2 to "Food", because now you're going to end up with an overlap with "Dinnerware", which is, of course, wrong. So, you have to redefine "Dinnerware", and everything in it, in order to add "Vegetable" to "Food". Let's say you have to do that kind of thing several times per minute. You need to add "Vegetable" and twenty items in it. At the same time, you need to add four new types of "Meat", six new "Fruit", two more types of "Apple" (under "Fruit"). Also, you're changing who you buy your dinnerware from, and they have different product IDs for plates, cups, glasses, silverware, etc., so you basically have to double the size of that category, but "Dinnerware" has a range that ends at 26 and "Furniture" starts at 27. Suddenly, you have to manage updates that affect nearly every row in your table. The complexity of the update is huge.
There are a number of solutions for this. You could add room to each category you add, based on anticipated growth over a reasonable time span. Instead of defining "Food" as 1-12, you define it as 1-1000, even though you only need 1-12 when you first build it. But does "Fruit" get 2-500, or 2-100, or 2-20, in that scheme? Managing the data ranges becomes something that requires a lot more work.
A path or adjacency hierarchy handles that kind of situation very, very easily. Want to add a new sub-set to "Food"? Easy, add one row, and its ParentID = "Food". You're done. No other rows to do anything with at all. Now you need to add 100 items under "Dinnerware"? Again, it's a single transaction that simply adds 100 rows to the table, each with the correct ParentID. Milliseconds later, you have your hierarchy fully functional.
In other words, there's more to it than what your sample code does. A lot more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon