Technobard, you have some good and valid points. With the software I write, we rely heavily on breaking the multiple parent rule that you describe. It was important to us to break this rule because of our environment; imagine doing telephone billing for a large coroporation who requires multiple levels of billing statements, reports, and the like across many departments rolled up and aggregated in different ways. Believe it or not, a single department may have to be reported for multiple department heads who live in parallel.
Further, this company wanted to arrange their reporting heirarchies differently for varying views. To accomodate this requirement we simply added a new field to the heirarchy table, called it heirarchy_id and now they can have separate living heirarchies representing the same data set. The lesson learned here was that when people were trying to talk me out of "over normalizing" the database, I had to keep repeating to them that intentionally limiting yourself at any point is more distracting than just doing things the more complicated complete way from the start.
Basically, let your data be your data and not your guide. Guide your data through other means, programmatically or with other data constructs but not embedded within. It's when you least expect it that you'll be rearchitecting your database simply because the data is too specific for a single need.
Sometimes I talk to much.
Oh, just to add about this real quick:
Quote:
|
One last thing. If the description "Cameras" appears under multiple categories, I would create unique ids for each entry. This preserves the single parent rule and keeps your tree traversal consistent. If the description has to change across the board, you can change it with a single update statement.
|
If you do this, then your reporting can no longer rely on your heirarchy or you'll end up with two identical categories listed with two different numbers.
If I come across as rude I apologize. It certainly is not my intent to do so but re-reading this it does seem a little, rash.