Store your configuration settings as a hierarchy in a database.

Clearly there are many well known and proven ways to store
and maintain the configuration settings of your applications. However, where
your requirements have outgrown the‘off
the shelf’methods, such as Active
Directory, the relational database can
be used to maintain a hierarchy of default settings, such as a global default
setting for the whole company,optional
lower level default settings for departments overriding upper level defaults,
as well as optional settings for individual users overriding all default ones.
This article provides a simple working
example which demonstrates how to accomplish that.

Why develop your own solution when there are canned ones?

If all you need is to supply hierarchical sets of
configuration values to your applications, then you can just use an existing
solution such as Active Directory, and you are all set. In this case you
probably do not need the solution described in this post. Suppose, however,
that your system needs more functionality than just storing and retrieving
configuration settings. For instance, suppose that your system frequently needs
to include various configuration settings with the data stored in the database
in the same reports. Consider the following requirement: select names and
e-mail accounts of all users (both names and e-mail accounts stored in the
database) who configured their application to output reports on network shared
drive NAS1. If configuration settings are stored in your database, developing
such a report is much easier. If you need many such reports, it is generally
easier to invest some development time to come up with your own solution for
storing configuration in the database, and save more development time on
writing simpler reports. As you will soon see, a solution for storing
configuration in the database is quite simple. Another requirement that I frequently
need is to store history of old values and provide ‘query as of some date in
the past’ functionality. Although storing history is very easy to accomplish,
it is beyond the scope of this post.

Setting up the tables and populating them with sample
data.

In this section I will provide the database structure and
populate it with sample data. In the next two sections, I will demonstrate how
to retrieve configuration settings and how to modify the hierarchy.

The following table dbo.ConfigTree
stores a hierarchy of departments and employees. It uses materialized path
approach to implement hierarchy. Materialized path is a well known approach for
storing hierarchies; it represents the hierarchy in an easy to understand way
and allows for very simple querying.

Note: there are other ways to store hierarchies in
the database, all having their advantages and disadvantages. A more detailed
discussion of materialized path and its alternatives is beyond the scope of
this article.

The table has several constraints which ensure that the
hierarchy is indeed a tree. The columns ParentPath and FullPath
visually present all the ancestors of a node in a very similar way to a
hierarchy of directories in a file system.

You’ll notice that backslashes in node names aren’t allowed,
so as to keep the querying logic simpler. The Fullpath value must end
with a backslash for the same purpose. You will see it for yourself in the next
section.

Here is how we populate this table with sample data:

INSERTINTO
dbo.ConfigTree

(NodeName, ParentPath, FullPath)

SELECT'ABC Inc.',NULL,'\ABC Inc.\'

UNIONALL

SELECT'UK','\ABC Inc.\','\ABC Inc.\UK\'

UNIONALL

SELECT'Lars','\ABC Inc.\UK\','\ABC Inc.\UK\Lars\'

UNIONALL

SELECT'Midwest','\ABC Inc.\','\ABC Inc.\Midwest\'

UNIONALL

SELECT'Jill','\ABC Inc.\Midwest\','\ABC Inc.\Midwest\Jill\'

UNIONALL

SELECT'Bob','\ABC Inc.\Midwest\','\ABC Inc.\Midwest\Bob\'

UNIONALL

SELECT'Headquarters','\ABC Inc.\','\ABC Inc.\Headquarters\'

UNIONALL

SELECT'CTO','\ABC Inc.\Headquarters\','\ABC
Inc.\Headquarters\CTO\'

The following table dbo.SettingValues
is where we store configuration settings for each node of the hierarchy tree:

The main advantage of materialized path is that it is easy
to select the data. The following stored procedure is used to return the
settings and their values for any node. It is short and not particularly complex:

CREATEPROCEDURE
dbo.SelectConfiguration

@NodeName VARCHAR(20)

AS

WITH Leaves(LeafPath)

AS(SELECT FullPath AS LeafPath FROM dbo.ConfigTree c WHERE c.FullPath LIKE'%\'+@NodeName+'\%'

The query is short, but not entirely obvious. Let me explain
why I used ROW_NUMBER()in this query.
Consider the situation when a default setting is overridden by a lower level
default or by a setting for an individual employee. The setting which applies
is the one on the lowest level in the hierarchy. Of course the setting on the
lowest level has a longer full path to the root than any of its ancestors.
Translating this condition into Transact-SQL leads to the following condition:

To retrieve the configuration for an individual, call the
stored procedure with that individual’s name:

EXEC dbo.SelectConfiguration
@NodeName ='Bob'

The FullPath column indicates where the setting comes
from. In this case, server name comes from the global setting, but time zone
isBob’s personal setting. The ability
to specify a global setting for the whole company in one place is very
convenient, it is a great advantage of this approach.

You can call the
stored procedure with a department’s name just as easily. You will get
configuration settings for all the employees in the department:

EXEC dbo.SelectConfiguration @NodeName ='Midwest'

How to modify the hierarchy.

In this section I will briefly demonstrate basic
modifications against the hierarchy stored as a materialized path. Although
some modifications are not exactly trivial, that’s the price you need to pay
for easy selects.

Adding a Setting: Suppose Bob wants to keep Mountain
Time on his desktop computer, but needs Central Time on his laptop. Extending
the hierarchy is very easy:

Comments

MERGE is available on 2008, but on 2008 I would just use hierarchyID which is built in. Also because the table is clustered on FullPath, LIKE predicates which I am using are indexed and as such very fast.

Leave a Comment

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization.
Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.