Object Oriented Relational Database Design
Michal submits a question he’d like answered in our daily editorial.
“How to take a database that is the basis for entities and apply common patterns like factory, etc – writing databases for object oriented design?”
I think this is an excellent question. The short answer is that you cannot design an object oriented database using relational database storage. Moreover, if you want your application to perform well, you do not want to let your object oriented code drive your database design.
There are some object oriented databases on the market today such as Caché which is very popular in the medical industry. There are also a number of no-sql databases that are more Key Value Pair types of storage, lending themselves readily to your objects.If you want a closer tie of your objects to storage, this is definitely the direction you should consider.
However, there are a few relational database patterns that lend themselves to object oriented techniques better than others. For example, Michal mentioned a factory pattern. If you use a surrogate key (sequentially increasing value) as a key in tables, you may use generics coupled with a factory pattern to simplify your data retrieval.
A factory pattern lets you re-use code by creating things that work that have the same behavior, and only differ in contents. Say you had a person object with an ID row identifier, and an organization object that also has an ID row identifier, then saving or retrieving of those objects is essentially the same. You find the person or organization record for the matching ID, and update or select the contents of that table.
Using Generics you could have a common interface that works with either object.
Interface IRowIdentifiedObject <T>
{
bool set(int rowID, T item);
bool get (int rowed, T Item);
}
T in this case is a placeholder for any class I wish to implement for saving or retrieving data. So, I will create a PersonORM class that knows how to handle the fields of my Person table, and an OrganizationORM class that knows how to handle the fields of my Organization table.
Using a factory method I can instantiate an IRowIdentifiedObject<T> variable as either a PersonORM or an OrganizationORM. Both ORMs implement the set and get methods, but have very different code for setting or getting data out of the database..
I can now have a generic call to persist a person or organization object. A factory instantiates the correct ORM based on the type of data (Person or Organization in this example). This technique simplifies and reduces the amount of code I have to write.
Many ORM code generators use this pattern if you want to learn how to do it yourself.
If you are not familiar with using C# Generics and interfaces, this example is nowhere near enough information for you to really get the picture. My apologies; these newsletters are to help establish direction, not provide detailed code examples. In that case, I recommend you find a good book on patterns… there are a number of good ones demonstrating patterns using different languages.
Just because having an identity column on your tables may make your object oriented code easier to write is not a good reason to select it as part of your database design! Be sure you wish to really use surrogate keys. With SQL Server, there are optimization benefits to having a sequential value in your table as a clustered index…this value can also act as a primary or secondary key.
In order to be more complete, you can also use a factory with a composite key as well. This technique uses two generic types in the interface definition and methods. Instead of hard coding int as the key, we use a generic type which also is established by the factory. Using this method you can easily implement composite/natural keys.
Interface IRowIdentifiedObject <K, T>
{
bool set(K key, T item);
bool get (K key, T Item);
}
Do you have database design patterns that work better with OO techniques? Share it with our readers by sending your insight to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Elemental MDX: Relative Member Functions (Part 2)
BI Architect Bill Pearson continues his Elemental MDX Series with an introduction to some "major players" in the "relative" member functions. In this article, we will examine the .CurrentMember, .PrevMember and .NextMember functions. In addition, we will introduce calculated members and named sets as a preview to their examination in future articles.
Featured White Paper(s)
SharePoint Migration
Written by AvePoint
Microsoft SharePoint Server 2010 is already transformin… (read more)
Featured Script
dba3_ExtendedProperty_level2type_demo
demo of ExtendedProperty level2type property use… (read more)