Typically, Genome is used to map tables to their data domain objects. But what to do when you have to use a database that is not made for mapping objects and therefore is not in any normalization form etc.?

Recently, I encountered such a problem. I had to build a new view for an existing database schema, which is not in any normalization form. For example, consider a table in the database that stores information in the following way:

Table 1: Value-table storing information in a non-normalized form

As you can imagine, this is a very “long” table. It stores information about the user, and the property code of the field on the GUI. For example, the property code ‘T010203’ is used to store the username, and the property code ‘T010304’ stores the user’s job. Such a table is very hard to map with an O/R mapper.

After a few hours of research, I remembered the PIVOT table feature in database systems such as SQL Server 2005. What I did is to use the idea behind that feature to bring my table into a different form – one which can be mapped. I created views on the table, providing me with new “views” of the data behind them. These views create “virtual tables”, which can be mapped with Genome.

Mapping the user as an object will result in the following view (let’s call the table ‘UserProperties’):

	SELECT User_ID,  
	      ( SELECT Value 
        	FROM UserProperties
	        WHERE User_ID = u.User_ID 
	          AND PropertyCode = ‘T010203’ ) AS Username, 
	      ( SELECT Value 
	        FROM UserProperties u1 
	        WHERE User_ID = u.User_ID 
	          AND PropertyCode = ‘T010304’ ) AS Job 
	FROM UserProperties u
	GROUP BY User_ID
	ORDER BY User_ID
    

Listing 1: View, mapping the user object

Now we have a view that generates a “virtual” table (shown below), to which we can map our business objects. Genome does not differentiate between a table and a view in the database. You only have to take into consideration that you must provide Genome with a unique identifier.

Table 2: View "User"

Having done this, we have provided read-only access for Genome. Updating values in the database is a little bit tricky. Providing writeable mappings via views means writing INSTEAD OF triggers for the created views. Doing so, Genome really doesn’t notice that we are mapping a view and not a table.

The following listing shows the INSTEAD OF UPDATE trigger required for our user object.

	CREATE TRIGGER myUpdateTrigger
	   ON myView
	   INSTEAD OF UPDATE
	AS
	   /* select the updated values */
	   DECLARE @id   int;
	   DECLARE @name nvarchar(50);
	   DECLARE @job  nvarchar(50);
	
	   SET @id   = ( SELECT User_ID  FROM INSERTED );
	   SET @name = ( SELECT Username FROM INSERTED );
	   SET @job  = ( SELECT Job      FROM INSERTED );
	
	   /* now update the values in the original table */
	   IF UPDATED (Username) 
	   BEGIN
	      UPDATE UserProperties
	      SET Value = @name
	      WHERE User_ID = @id AND PropertyCode = ‘T010203’ 
	   END
	
	   /* update the rest of the fields the same way */
	
	GO
	/

Listing 1: INSTEAD OF UPDATE trigger for the user object

If you want to INSERT or DELETE values as well, you have to additionally provide the required triggers.

Posted by Harald Köstinger

Technorati tags: mapping views object relational non-normalized forms