| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2004-08-06 : 14:16:18
|
| I'm looking for suggestions for methods to traverse the tables in a database to build a list of the hierarchies. So far, I've used the lineage method found here [url=http://www.sqlteam.com/item.asp?ItemID=8866]here[/url]. I'm hoping to find other methods people have used in the experience to compare to what I've done so far. Any suggestions? |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 14:20:19
|
| Hmm i actually use the first example on that topic, for use in categories/subcategories and sub-sub-sub-sub...etc. Not sure if it's a good way, it was for a tiny site, so it was no big deal to use the recursive joins.- RoLY roLLs |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 14:47:00
|
Very interesting Corey, never thought about the multi-parent part. Never had the need...so far  - RoLY roLLs |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-08-06 : 16:20:32
|
I wish I could say I didn't have the need  Corey |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 16:25:50
|
| speaking of 'the need' for that. Can you give me some situations, other than maybe a familty tree project, would that model be used for?Like EC (eyechart) says, too lazy to think about it, maybe cuz it's friday! - RoLY roLLs |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-08-06 : 16:33:01
|
Well I use it for reporting... I build two tables, one with summary information at every point in the structure and the other with the detail for base points. Then, web reports are extremely simple: two pges (summary & detail)All I have to do is change the structure and all of my reports will restructure themselves. Its nice to look at subsets easily.for example:All Clients Client 1 (Active) (Account Rep #1) Region 1.1 Site 1.1.1 Site 1.1.2 Site 1.1.3 Region 1.2 ... ... Client 2 (Canceled) (Account Rep #1) ... Client 3 (Active) (Account Rep #2) ...Active Clients Client 1 (Active) (Account Rep #1) ... Client 3 (Active) (Account Rep #2) ...Canceled Clients Client 2 (Canceled) (Account Rep #1) ...Clients By Account Rep Account Rep #1 Client 1 (Active) (Account Rep #1) ... Client 2 (Canceled) (Account Rep #1) ... Account Rep #2 Client 3 (Active) (Account Rep #2) ... Corey |
|
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-06 : 16:43:17
|
| Ah, I see. Not bad. I see you got an account rep section there. I'm in the process of determining a good database design for something similar with sales reps, in the case that any client can have multiple reps and of course reps can have multiple clients.- RoLY roLLs |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2004-08-06 : 17:02:06
|
quote:
Originally posted by Seventhnight
I have used a similar method to the lineage. My sample is at: http://www.seventhnight.com/treestructs.aspCorey
Actually, this might help me out with another issue I'm having. I currently am only tracking single parents but a table name can exist in multiple branches (FK's to multiple higher level tables) - which has it's own issues. I started thinking about how to identify if a table is in multiple branches and this will make that moot and address the same problem. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-08-06 : 17:04:01
|
| Yeah. It took me a while to wrap my head around it. The hard part is to maintain only 1 path to a site within a specified rollup. I am toying with the idea of relationships that can belong to one or more trees. (or maybe relationships that require certain permission status to view)...Corey |
|
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2004-08-06 : 17:05:18
|
quote:
Originally posted by RoLYroLLs
speaking of 'the need' for that. Can you give me some situations, other than maybe a familty tree project, would that model be used for?Like EC (eyechart) says, too lazy to think about it, maybe cuz it's friday! - RoLY roLLs
1) Removing all records from the database for a specific entity (build the lineage and begin purging top down). Please do not suggest using cascade deletes :) !2) Data movement scripts for moving data for a specific entity to another server.There are more but to summarize it is if for any requirement where only a subset of the data (and all the child data) needs to be processed against, this is a candidate for traversing the tables. I can also think of a ton of administration tasks that could utilize this type of approach. I'm also keeping in mind maintance. Over time, tables get removed/added and why rebuild something if it can be dynamic to start with? |
|
|
|
|
Traversing database hierarchy