This post details how to accomplish a cross-join between two tables and left join in the many-to-many to override a value in the left most table. The result is a dynamic setting view in which each target section pulls from a base of setting values, but may override the default value with it's own value.

Background

I am developing a settings manager for a web application. The web application manages several libraries and each library may have a different value for each setting. However, those libraries that don’t have a value defined for any given setting should use the default value.

The solution should be clean and I want retrieving settings as simple as:

Select * from LibrarySetting where id = 2;

Environment

Solution

I’ve come up with a solution that took a couple of hours worth the headache. Mostly because I discovered very little to no documentation on the web for the techniques I’m attempting.

Table Structures

Here is the table structure I’ve setup. Obviously there is more going on in the database, but these are the affected tables.

The Setting table holds all of the possible settings for the libraries. The SettingSet table is a many-to-many table joining Setting and Library. The value column in the SettingSet table is intended to overwrite the defaultValue in the Setting table when present.

This structure allows me to change the name, description, or default value of a setting without adversely affecting the values defined for any given library. The keycode will be used for building a struct (hash table) in the application for storing and referencing the settings without placing a query each time a setting is needed. Assume that everything is properly indexed and foreign key constraints are in place.

Sample Data

I’ve real quick populated the tables with some sample data; 3 libraries, 1 setting (default language), and 2 of the libraries have values defined for the setting.

The Query

Recall that for any given library I want to display all the settings. To accomplish this, I need a Cartesian join between the setting and library table. This essentially produces the product of those two tables.

Then, to get in the library override values I left join in the SettingSet table. I had troubles mixing and matching the queries in the same select statement, so I used a left join between two subselect statements.

The master select uses a case statement to select the default value when no value is defined for a library-to-setting pair.

	select
		set2.id,
		set1.library_id,
		set1.keycode,
		set1.name,
		set1.description,
		case
			when set2.value is null then set1.defaultValue
			else set2.value
		end as value
	from (
		select
			s.id,
			l.id as library_id,
			s.keycode,
			s.name,
			s.description,
			s.defaultValue
		from
			Setting s
			cross join Library l
	) as set1
	left join
	(
		select
			ss.id,
			ss.setting_id,
			ss.value,
			ss.library_id
		from
			SettingSet ss
	) as set2 ON set1.id = set2.setting_id and set1.library_id = set2.library_id

Database View

Once the query is right, I’ve chosen to capture it in a view. A stored procedure would also be appropriate, but I want some flexibility to place queries against my view. I find the drop if exists just to be convenient practice

IF EXISTS (select TABLE_NAME from INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'LibrarySetting') drop view dbo.LibrarySetting
GO

create view LibrarySetting as
	select
		set2.id,
		set1.library_id,
		set1.keycode,
		set1.name,
		set1.description,
		case
			when set2.value is null then set1.defaultValue
			else set2.value
		end as value
	from (
		select
			s.id,
			l.id as library_id,
			s.keycode,
			s.name,
			s.description,
			s.defaultValue
		from
			Setting s
			cross join Library l
	) as set1
	left join
	(
		select
			ss.id,
			ss.setting_id,
			ss.value,
			ss.library_id
		from
			SettingSet ss
	) as set2 ON set1.id = set2.setting_id and set1.library_id = set2.library_id
GO

Usage and Result

Query the View

As promised, I can do a simple select of my LibrarySetting view and get all the settings of all the libraries. You could also limit via a where statement to pull for just one library.

Notice in the third record of the result there is no id. This is because no value is defined for that setting for that library. As desired, it is pulling the default value. Your application can interpret that state by the null value in the id. (so on save; if id update, else insert)

Example usage in Coldfusion

This coldfusion function can be used to build a struct populated with the settings for a given library. I’ve cut out the error checking for simplicity. Sorry my syntax highlighter doesn’t support coldfusion.

    <cffunction name="getSettings_forLibrary" access="private">
    	<cfargument name="library_id" required="yes" hint="primary key of library to collection settings for">
        
    	<cfquery datasource="#Application.datasource#" name="libarySetting_query">
        	SELECT *
            FROM LibrarySetting
            WHERE
            	library_id = <cfqueryparam cfsqltype="cf_sql_tinyint" value="#library_id#">
        </cfquery>
        
        <cfset Application.libraries[library_id].settings = structnew()>
        <cfloop query="libarySetting_query">
            <cfset Application.libraries[library_id].settings[libarySetting_query.keycode] = structnew()>
            <cfset Application.libraries[library_id].settings[libarySetting_query.keycode].name = libarySetting_query.name>
            <cfset Application.libraries[library_id].settings[libarySetting_query.keycode].description = libarySetting_query.description>
            <cfset Application.libraries[library_id].settings[libarySetting_query.keycode].value = libarySetting_query.value>
        </cfloop>
    </cffunction>

You can then call upon a setting from anywhere in your application.

<!--- Assume this is set somewhere --->
<cfset request.library_id = 2>
<!--- I have a page object in my request scope that is used by my template handler to render a page --->
<cfif isdefined('Session.language')>
   <cfset request.page.language = Session.language>
<cfelse>
   <cfset request.page.language = Application.libraries[request.library_id].settings['defaultLanguage'].value>
</cfif>

This ultimately allows a user to specify a language for the site. If they havn't, then it defaults to the library's setting for the language. If the library hasn't defined one, it defaults to the application default.