THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Paul Nielsen

Why the Relational Model (as implemented) Is sufficient (part 2)

There’s a growing trend to develop the data structures within the class diagram without any input from a data modeler, and then create a single table per class for object persistence or use an object/relational mapping layer to convert the classes and objects into tables and rows.  This is based on the notion that relational databases and object oriented technologies are incompatible. The object-relational impedance mismatch is as much cultural as it is technological. Most of the work being done in this area is by the object community.

A while ago I spent a year dedicated to working on the question of how to model object-oriented behaviors within a relational database. To clarify, by relational database I mean a real working implementation that can be purchased off the shelf – specifically SQL Server.  The result was Nordic (New Object Relational Design), an O/R implementation developed using only T-SQL. Here’s what I discovered along the way.

The two big ideas of OOA/D that must be modeled are (1) classes with inheritance and (2) polymorphism.

Inheritance, or the ability to duplicate attributes from one class in sub classes is very doable in a relational database. There are three possible patterns. The first pattern is the generic pattern, also called the Value-Pairs pattern. I rejected this pattern because, while it can work for modeling the classes with inheritance, it’s just plain ugly when querying it using SQL.

The second pattern, and the one recommended by the Object Modeling Group, is the concrete class table pattern. In this pattern a table is created for each class that contains both inherited and direct attributes for the class. I rejected this pattern because it complicated polymorphism (as you’ll see in a few paragraphs.)

The pattern I like for modeling classes and attributes is a cascading class pattern, which is an object/relational term for a supertype subtype pattern. In this pattern, a table is created for each class that contains just the direct attributes of the class. So, for an animal class and mammal class, the animal class might have 5 attributes and the mammal class adds another 3 attributes. To select all the attributes for a given object, the select query must join the animal and mammal class tables.

To application programmers, polymorphism is the ability to modify how a method is implemented within subclasses. For example, a vehicle class might have a method called accelerate. But the actual implementation fir the boat subclass would be different than how accelerate is implemented for the hot air balloon sub-lass.

For object databases the nature of polymorphism is slightly different, it means that the verb select can select objects from subclasses as well as objects that directly belong to that class. This is because with an object database we’re working with sets of objects and the primary verb, or method, is still the all-powerful select command. For example, if Bob the monkey is an object in the mammal class, then “select * from mammals” should find Bob the monkey. And “select * from Animals” should also return Bob the monkey because Bob is a mammal and a mammal is-a animal.

So the real trick to building an object relational database is to implement a select command that can perform polymorphism and SQL relational databases actually excel at this with several possible solutions.

The first and most obvious solution is to build a view for each class that selects all subclasses.

A more dynamic solution is to store the class structure in a hierarchy and use a UDF to navigate the hierarchy and return a list of all possible subclasses. Using this UDF in a where clause or within a derived table can easily implement polymorphism.

Although I’ve just skimmed the issues of designing an O/R dbms within SQL Server in this post, it is very possible to handle every object programmers requirements within an off-the-shelf relational dbms. I believe there are two reasons why it’s not regularly accomplished.

First, the cubical wall between the DBA and the C# programmer is a bit too high and there’s no cross communication, even our lexicons use a different jargon. Read the abstracts for any enterprise architecture conference. There’s not a single database related topic! The recent call for papers for the international software architecture association did not even include a database category.

Secondly, we’ve (in the database world) have missed the boat on advancing and promoting database technology with the right patterns as a solution in an OO world.

I believe the solution to the software ills of corporate America is not wrapping every app in XML and SOA, but fixing the poorly designed databases that were designed by object programmers for object persistance. And, I'm convinced that the off-the-shelf relational dbms of today – SQL Server – is more than sufficient.


Published Thursday, December 21, 2006 4:24 PM by Paul Nielsen



Joost said:

UDF seems to mean User-Defined Function.
December 25, 2006 5:54 AM

Luís Bruno said:

You need to look at Check the documentation for 8.2, the most recent version of PostgreSQL, and have a look at the table inheritance section.
December 25, 2006 12:48 PM

mashby said:

At a minimum, SQL Server should have datatype inheretence to SPs (which postgreSQL doesn't have).

Create table X (

    A as int

  , B as varchar(123)


Create PROC DoSomething


      @Param1 as X::A;  --inherets type from table X::A.

     , @Param2 as X::B; --inherets type from table X::B.




If SQL Server could do this then I as a developer wouldn't have to keep checking what the types are, and when changes happen the query engine could take care of the dataype changes in the SPs/Functions/etc.

It completely floors me that this simple feature is missing. Tons of more or less useless XML features but not this useful feature.

May 4, 2007 12:55 PM
New Comments to this post are disabled

About Paul Nielsen

Paul Nielsen believes SQL is the romance language of data. As such he’s a hands-on database developer, Microsoft SQL Server MVP, trainer, and author of SQL Server Bible series (Wiley). As a data architect, he developed the concepts of Smart Database Design and Nordic – an open source O/R dbms for SQL Server. He lives in Colorado Springs.

This Blog



news item test
Privacy Statement