Keeping HierarchyID up to date

Question

I'm in the middle of refactoring an old country/region/location database structure to a hierarchal table.

I going to use the primary key of the new table and a self referencing ParentID column (on the primary key) as the basis for a hierarchyid column.

therefore my table looks like:

PK Name ParentID HierarchyID

1 World NULL /

2 UK 1 /1/

3 Wigan 2 /1/2

My question is: What methods have others implemented for keeping hierarchyid columns up to date when a record is created or the parent of a record is changed?

Note; I am using LINQtoSQL (I'm using a computed hierarchyid.ToString() column to expose the lineage in code, plus stored procedures to give me the performance benefit of hierarchyid queries).

For insert/update management:

I have considered overriding the insert/update procedures in LINQtoSQL for my new table.

I have considered using stored procedures to manage updating/inserting new records.

I considered a recursive trigger after insert/update to perform an update based on the ParentID.

I have not however made a decision on which course to take!

Has anyone used any of these methods for managing hierarchyids? Are there better methods that I haven't considered, or am I missing something really obvious!!?

Answer 1

I just recently did a similar project where I used stored procedures to handle id management. LINQ is cool if you're doing some simple queries, but it introduces difficulties when debugging and maintaining the data model.

The good part about using a stored procedure, is that it abstracts the id management from the application layer, so that a developer doesn't need to understand the underlying database model(s). Similarly on the bad side, it abstracts the id management from the application layer, so that a developer can't understand the underlying database model(s) without heading to the database and viewing the stored proc.

Using a database trigger would probably be the best solution, but I decided not to go with that simply because it would add yet another level of abstraction. This is because it further obfuscates the true data process, by leaving another spot to look for someone who might be trying to figure out how it all works.

The bottom line, is that you should enforce your referential integrity in a way that is consistent across your system (or your project, at the very least).