There are numerous example of how to store the hierarchical tree structure is in SQL 200 DB, and probably the most pointed out is the Celko's method (http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=6477
). To tell you the truth I'm having a hard time wrapping my mind around it and I do need some help getting it to work (does not have to be Celko in my case either). I'm using SQL 2K and .NET (VB). First what I need it for:
I was asked to create a "troubleshooter" type of app for our internal use, where you select one question and it gives you a list of other related questions from where you select another question and so on until you reach your solution.
For my needs I the tree does not need to go deeper then say 25 levels. Though infinite tree would be nice too :)
So the 5 things I need is
- the structure used to save the relations (including the question content obviously I'll add my fields here)
- a query or SP that would list available parent in this format:
P1 > Sub-P1 > Sub-P2
P2 > Sub-P2 > Sub-P3 > Sub-P4
(This would get loaded to a control from where you'll select the paretn for a new question)
- a query to add the new Q if the insert is somewhat more difficult then referencing the parent ID
- a way to delete any question including it's sub-questions.
- ideally the information would be stored in one table, but if not possible then I can live with it :)
I won't need the ability to move one question under another, so the nodes will stay in the same place once it's added. I know I'm asking a lot questions that require a lot of code and I don't expect a complete solution, but rather a few example how it can be accomplished.
Any ideas are welcomed ! Thanks.