Mixing Normalized and Inverted Tables

If you wish to employ a traditional normalized database design in your data-driven application, you may certainly do so. In the personnel-management system example, you've likely created a series of data-collection fields for the employee's personal information that you store in a series of normalized tables. Yet you realize that you can't possibly anticipate all the data elements a user may wish to collect. To remedy this, you can create a one-to-many table that stores an unlimited number of data-driven columns. The DataElementID column in the DataStorage table references the EmployeelD in the Employee table. Here, you need to perform JOINs in a stored procedure using dynamic SQL. The stored procedure code shown in Listing 8-10 illustrates how to convert inverted data to a normalized structure and then JOIN it with an existing table.

Listing 8-10. Extracting the Inverted Data

DECLARE @SOL varchar(max) DECLARE @ColumnName varchar(l00) DECLARE @DataValue varchar(l00) DECLARE @DataType varchar(l00) DECLARE @Cnt int DECLARE @x int DECLARE @ID int DECLARE @DataElementID int DECLARE @DataElementIDPrev int

--Store the employee IDs that match the criteria

CREATE TABLE #IDtemp (

EmployeeID int

--Put all matching employeeIDs into the temp table INSERT INTO #IDtemp

SELECT DataElementID FROM DataStorage WHERE DataDictionaryID = 5 AND DataValue >= 90000

--Pull all the inverted data whose primary key is found in the temp table SELECT ds.DataElementID, ds.DataValue, dd.ColumnName, dd.DataType INTO #DataStoragetemp FROM DataStorage ds

INNER JOIN DataDictionary dd ON ds.DataDictionaryID = dd.DataDictionaryID WHERE ds.DataElementID IN (SELECT EmployeeID FROM #IDtemp) ORDER BY ds.DataElementID, ds.DataDictionaryID

DROP TABLE #IDtemp

--Add a unique key to facilitate iteration ALTER TABLE #DataStoragetemp ADD ID int IDENTITY

The first section of this code is very similar to Listing 8-11. You need to extract a list of the matching primary key values and then pull the detail data based on those values. The tricky part is shown in Listing 8-11. Here, a normalized temporary table is built from the structure held in the DataDictionary. Then, the primary key value is INSERTed into it, and all subsequent values are UPDATEd one column at a time. The result is a normalized table that can be joined with the Employees table.

Listing 8-11. Converting an Inverted Table to a Normalized One

--Create a temp table to hold the normalized data CREATE TABLE #Datatemp (

DataElementID int

--Add columns to the normalized data table --from the data dictionary SELECT 'ALTER TABLE #Datatemp ADD ' + ColumnName + ' ' + CASE

WHEN DataType = 'varchar' THEN DataType ELSE DataType END AS ColumnName INTO #Structuretemp FROM DataDictionary

by extracting them

--Add a unique key to facilitate iteration ALTER TABLE #Structuretemp ADD ID int IDENTITY

SELECT @SQL = ColumnName FROM #Structuretemp WHERE ID = @x

DROP TABLE #Structuretemp

SET @DataElementIDPrev = 0

SELECT @Cnt = COUNT(*) FROM #DataStoragetemp

--Iterate through the inverted data and create INSERT and --UPDATE statements to populate the normalized temp table WHILE @x <= @Cnt BEGIN

SELECT @DataElementID = DataElementID,

@DataValue = DataValue,

@ColumnName = ColumnName,

@DataType = DataType

FROM #DataStoragetemp

IF @DataType = 'varchar' OR @DataType = 'date' SET @DataValue = '''' + @DataValue + ''''

IF @DataElementID <> @DataElementIDPrev BEGIN

SET @SQL = 'INSERT INTO #Datatemp (DataElementID, ' + @ColumnName + ') VALUES (' +

CONVERT(varchar, @DataElementID) + ', ' + @DataValue + ')'

-- INSERT INTO #Datatemp (DataElementID, LastName) VALUES (1, 'Gates') EXEC (@SQL)

SET @DataElementIDPrev = @DataElementID

END ELSE BEGIN

SET @SQL = 'UPDATE #Datatemp SET ' + @ColumnName + ' = ' + @DataValue + ' WHERE DataElementID = ' + CONVERT(varchar, @DataElementID)

-- UPDATE #Datatemp SET FirstName = 'Bill' WHERE DataElementID = 1 EXEC (@SQL)

--Join the permanent normalized table to the temp normalized --table to prove this really works SELECT e.LastName, e.FirstName, t.* FROM Employees e

INNER JOIN #Datatemp t ON e.EmployeelD = t.DataElementID ORDER BY e.LastName, e.FirstName

DROP TABLE #DataStoragetemp DROP TABLE #Datatemp

0 0

Post a comment