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.