Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Hierarchy trees, lineage and surrogate PK

Author  Topic  davidM Starting Member 2 Posts Posted - 2005-04-05 : 04:39:31 Hi I'm implemented a tree hierarchy and storing each node's tree path e.g. (as per Corey's website) id_path node_id --------------------------------------- 0000;0001; 1 0000;0001;0004; 4 0000;0001;0004;0008; 8 0000;0001;0004;0009; 9 This corresponds to the report: name_path node_id --------------------------------------- transport;plane 1 transprt;plane;jet 4 tansport;plane;jet;concorde 8 tansport;plane;jet;harrier 9 I would ideally like to keep these name_paths stored in a table, rather than having to create them on the fly when running the report. Could you point in the right direction as to how to go about tackling this? My initial thought was to use a trigger to insert a name_path record whenever an id_path was inserted. The use of a surrogate PK was also suggsted to me, but I've no experience with them. The tree will be added to and selected from on a very fequent basis, and whilst moving nodes/branches to a different part of the tree is fairly rare, it can and does happen. I very much appreciate any guidance you be able to offer. David