June 25th,2010 by Allan Bogh

In database design it can be nice to have a very flexible database for logging or configuration of resources. This flexibility is obtained by normalizing your database design to the third normal form (3NF) or beyond. One situation that may come up is that a table may have key/value pairs that link to another table and you would like to produce a SQL query which aligns the keys as columns and the values below those columns. I've created a very flexible logging scenario that shows this in action.

Database Design

Log Table

The following log table is an example of what you might see in any major system requiring SOX compliant logging. The key thing to note is that we are not keeping track of what was logged, only that an action occurred, what action it was, and who submitted it. 

Dynamic Log Table

This table references the Log table and keeps track of all of the information regarding what happened. The table structure is rather simple with a LogID, FieldName, and FieldValue. The FieldName column can be anything from any type of log entry. For instance, a Create User might need FirstName, but an Update User might need OldFirstName.
 

Reporting
 
Sarbanes-Oxley says that an IT administrator needs a way to report on the actions taken in an environment. For this, we need to take this highly flexible database and produce a single line for each log entry which includes all the information regarding that log item. Since this report will be a culmination of all of the data in the logging tables, it will also include the columns from the other actions, however these columns must be NULL. To do this, I've created a custom SQL script which pulls the required column information out of the DynamicLog table, then produces a report based on the columns. This SQL script can be loaded into a stored procedure to enhance it or help speed it up by caching the query. The query has the possibility of slowing down as the database gets large, so data cleanup may be necessary over time.
 

-- Gets all of the unique columns from our database
DECLARE Cur CURSOR FOR
    SELECT DISTINCT FieldName
    FROM dbo.DynamicLog

DECLARE @CurFieldName nvarchar(50)
DECLARE @AllColumns nvarchar(max)
DECLARE @Sql nvarchar(max)

-- Variable to store column SQL code
SET @AllColumns = ''

-- Opens the cursor for use
OPEN Cur

-- Get the columns from the LogDynamic table
-- Store column in @AllColumns
FETCH NEXT FROM Cur INTO @CurFieldName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @AllColumns = @AllColumns + '(SELECT FieldValue
        FROM DynamicLog DL
        WHERE DL.LogID=L.ID 
        AND DL.FieldName=''' + @CurDBField + ''' 
        AND NOT FieldValue IS NULL) AS ' + @CurFieldName + ','
FETCH NEXT FROM Cur INTO @CurFieldName
END

-- Kills the cursor
Close Cur
DEALLOCATE Cur

SET @AllColumns = SUBSTRING(@AllColumns,0,LEN(@AllColumns)) /* Strip last comma */

SET @Sql = 'SELECT L.[ID],
L.DateSubmitted,
L.SubmittedBy,
L.LogAction,'+
@AllColumns+'
FROM dbo.[Log] L /* Only select from logging, nothing else. */'

EXEC sp_executesql @Sql

This produces a resulting SQL query that looks like this:

SELECT L.[ID],
    L.DateSubmitted,
    L.SubmittedBy,
    L.LogAction,
    (SELECT FieldValue
        FROM DynamicLog DL
        WHERE DL.LogID=L.ID 
            AND DL.FieldName='FirstName' 
            AND NOT FieldValue IS NULL
    ) AS FirstName,
    (SELECT FieldValue
        FROM DynamicLog DL
        WHERE DL.LogID=L.ID 
            AND DL.FieldName='LastName' 
            AND NOT FieldValue IS NULL
    ) AS LastName
FROM dbo.[Log] L /* Only select from logging, nothing else. */

Resulting Report

Using the SQL Query defined in the script or stored procedure above, you can create a table which looks like the one below. Any columns not defined for a particular action are intentionally left NULL.