We run an in house created CMS with a flexible tree structure to create URL’s and lists of pages.
It uses the adjacency list model to represent the tree in SQL 2008. It was started this way back on SQL 2005, and uses a simple parent / id relationship.
We currently generate a number of pieces of data to work with the tree after we have gathered the data from SQL. The issue with this is that when the tree gets large, we transfer more data than we need to the application because we need it all to be able to create the required information.
We have the ability to extend this data model in any manner we please using a series of tables to define data types and data saved against the content. We currently change these rows of data into columns in the application.
Because the data set can grow to be large and we are going all this work on the application side, this is now creating an issue, and we see the opportunity to have this data generated in SQL and saved in a View so that it is kept up to date by SQL Server.
Design and implementation of a SQL 2008 Stored Procedure / Function that can be executed to select the data required with 3 extra fields on the query result for Depth, URL, and ParentList. It should also select the custom data to produce new columns populated with data based on the extension tables. Ideally this could be implemented into producing the data to a view so the data is cached on the SQL side.
Please PM me for a DB diagram and a Full brief.