Sol 3

I've been handling hierarchical data in SQL for many, many, many years now.  About 6 years ago I came across some articles by a guy named Joe Celko.  By numbering a left and right side to each node in a tree he describes a way to deal with hierachical data in a very direct way.  The following are some of the articles:

• Trees in SQL
• SQL Lessons
• SQL Tree Query
• How to create a newsgroup-like db

If you do a google search you will find many such articles.  The other thing I would like to point out is the above solution as done in SQL 2005 now carries even more weight.  You have the use of .NET Framework within SQL Server with full CLR support.  This means you can write a CLR based sproc that has the use of full blown arrays to do some of the trickier INSERTs and DELETEs.

SQL 2005 has introduced a new way at looking at hierarchical data - Common Table Expressions.  I will be the first to admit that I am not up to speed on SQL 2005.  I've yet to have a client willing to move to SQL 2005 so everything I still do is on SQL 2000 (or Oracle, etc).  However, this is some slick technologies here and well worth the research.

• Navigate hierarchies using recursive common table expressions
• C# 3.0 and LINQ - Expression Trees
• Working With Temporary Views Using Common Table Expression in SQL Server 2005

So, with a lot of data out there for sulutions there should be no problems implementing a tree solution in the SQL language.