Another Reverse Recursion Problem
-
Hi,
This is really a twist on an earlier question I posted, I want to take things a bit further. I have a two tables as follows:
ObjectLinks:-
ObjectID - PK
ParentObjectID - PK
Path - string
Objects:-
ObjectID - PK
IsMenu - bit
IsMenuInheritable - bit
MenuID
The tables are joined with the Objects.ObjectID linking to the ObjectLinks.ObjectID and ObjectLinks.ParentObjectID to provide a heirachical data structure.
The structure allows a single object to have one or more parents so in order to keep track of where an object is in the hierachy I use the Path field to store the position in the tree by storing the ObjectIDs of the parent up the line in the following format 1-29-48-72. In the actual app it is this information that is passed around as the querystring and is therefore indexed to speed the searches.
I want to be able to write a stored procedure that will take that string, and then check each record in the branches leading to the root to see if the object is a menu and then build up a temp table of the menuIDs.
To add to the complications, I need to check to see if the menu is inheritable by child objects, if it isn't it only applies to it's children and not it's grand children.
So in other words the stored procedure needs to be able to accept the string and then search each entry in the ObjectLinks table for the MenuID in the following order:
1-29-48-72
1-29-48
1-29
1
Once I have the temp table with the MenuIDs, I can then query the Menu table to extract further info to be returned as a recordset.
Confused?? I am!
Do any of you have any suggestions on the best approach or even recommendations for good on line articles covering data manipulation like this.
Many thanks in advance.
-
You are using an adjency list but materializing a Deway path. May save some IO but you have to maintain that path and your model is not normalized (no easy way to put a constraint so you can end up with data corruption). You also want to use the path as a parameter? Why not use the id?
You have many options:
- quite easy to write a table UDF that returns your ids in a table. Then use it for joins.
- do a simple while loop to crawl up the list and populate a temp table. Not nice, but it will work and can be extremely fast. Depends on the list depth.
- implement a nested set approach instead. It will be orders of magnitude faster but it will take you more time to develop since you need to understand it first and implement the algorithms after. Search on "nested sets", you should find a bunch of examples. It is the fastest (by far) approach to hierarchies for reading, but updating large sets very frequently can cause locking issues (if you are making lots of changes extremely often).
- use a recursive CTE (if you are using SQL2k5...or upgrade
). Extremely simple to use and it will save you from most problems. -
Hi,
OK, you have me totally lost on the first bit.
I use the path because an Object can have multiple parents so the path enables me to identify where they are in the tree structure.
I was working on the basis of doing a simple loop for the time being - the hierachy is never likely to be more than about half a dozen levels deep at the moment - if we need to handle more I'll get a better programmer than I to re do the database 😉
But working out how to do the loop and create the temp table is beond me at the moment.
Cheers,
Julian
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply