In a previous post entitled MVP: Object Oriented is Academic, Religion I discussed a classic example of persisting OO classes to RDBMS tables. Here’s a recap:
We have 4 classes that hold contact information. They are: ContactInformation, Address, Telephone, and Email. ContactInformation can hold zero-through-infinity each of Address, Telephone, and Email.
Normally, we would create 4 tables that correspond to the 4 classes. ContactInformation would have a primary key and Address, Telephone, and Email would each have a foreign key called ContactInformationID.
The problem is that in our class design, we have the classes Vendor, Employee, and Customer. Each of these has a ContactInformation member of the type ContactInformation. This simplifies our code because a single change or fix to any of the 4 contact-related classes is immediately reflected in the Vendor, Employee, and Customer classes.
The big question is this: how do we design the data storage for a relational database management system and how do we write our persistence code?
There are three possible solutions that I can see and I would like to cover the first one today.
Solution 1 - Separate Tables
It seems that the most "referentially integral" solution would be to create separate tables to house the contact information for each of Vendor, Customer, and Employee. Thus, we have the following tables:
- VendorContactInformation
- VendorAddress
- VendorTelephone
- VendorEmail
- CustomerContactInformation
- CustomerAddress
- CustomerTelephone
- CustomerEmail
- EmployeeContactInformation
- EmployeeAddress
- EmployeeTelephone
- EmployeeEmail.
While it does mean that the database maintains its own integrity (a good thing indeed), it certainly has some issues:
- When we make a change to the structure of any of the ContactInformation and related classes, we must make the change 3 times in the database and possibly in several redundant persistor objects in our code
- We must create a fairly heavy single persistor in our code or else Vendor, Customer, and Employee must each have their own ContactInformation persistor
- To do a search for a piece of contact information (e.g. a telephone number) regardless of what type of entity to which it belongs, we must perform a union in a view or subquery
- When adding additional entities (e.g. ShippingCompany, Manufacturer, Agency) that each include the ContactInformation class, we must reproduce everything over again and update several views
A portion of the inconvenience could be fixed with stored procedures by creating, say, sp_GetAddresses that takes the Vendor, Employee, or Customer ID and a string denoting whether we are after the addresses for a Vendor, Employee, or Customer. At least the use of stored procedures can make persistence and retrieval more generic.
I have no doubt that this is a common solution but it certainly is cumbersome. The OO code makes standardization and maintainability so easy but the database's limitations seem to undo this advantage quite quickly.
This classic solution just doesn’t seem to have the forward-thinking for where we are heading in the future. It is, however, safe. Please feel free to post your own ideas, no matter how conservative or how crazy. I've got a few more ideas coming up that are sure to get me a one-way-ticket to the looney bin.
Happy Coding
- Shaun
Tuesday, November 8, 2005 was a day of wonders with the Toronto VS2005 Launch event. This was the first time that I have attended such an event. I burst into laughter when I saw the "rock concert" style complete with giant sparkle-filled balloons for the audience to bat around while AC/DC blasted over the loud speakers completely with accompanying multi-screen video.
There is something seemingly absurd about geeks trying to pretend to be cool. Granted, as I look all around me at various nerd events I notice that many of my programmer contemporaries are turning into metrosexuals. As for me, I will continue to wear electrical diagram print suspenders and glasses held together with scotch tape.
All in all, I was very impressed with the day's events. Microsoft is really good to their developers.
One great resource that they had available was an array of MVP's that were ready, willing, and able to answer all of your development questions. Adorned in matching tight-knit cotton shirts with the word "Expert" written down one sleeve, I was sure they could satisfy even my deepest burning curiosities. This time, I decided to ask about the age-old question of persisting class structures into a relational database.
The Example
Suppose we have a ContactInformation class. This class contains an Address collection, a Telephone collection, and an Email collection. Thus, each ContactInformation class can have 0-through-many addresses, telephone numbers, and email addresses. Each Address has an AddressType (such as Shipping, Billing, Mailing, etc.), each Telephone has a TelephoneType, and each Email has an EmailType.
Persisting this to a relational database makes sense. You create an Address table, a Telephone table, and an Email table. Plus you can either use enumerated values or create tables for AddressType, TelephoneType, and EmailType. The programmer may wish to create a ContactInformation class in case there are some extra details contained therein.
The problem comes when we wish to have a Vendor class, Customer class, and Employee class - each having ContactInformation as a member. Obviously, we must create Vendor, Customer, and Employee tables but how do we related them to the ContactInformation table?
The Microsoft Expert Answer
For an expert (and leader of a SQL Server user group), I certainly wasn't the least bit impressed. I started to ask my question about mapping objected oriented classes into SQL Server when he interrupted and gave me a 5-minute lecture on why "object oriented" is a religion.
Instead of letting me ask my question (which wasn't on the merits of OOP) and giving me an answer (I was hoping to learn more about O/R Mappers), he decided to go on a tirade about performance problems and why he doesn't use object oriented programming for anything. He said that the only benefit to object oriented programming is code maintainability and other than that, it is useless. There was something in there about it being solely an academic pursuit that unfortunately some people have tried to apply to the real world.
Too bad Microsoft disagrees with that assessment. VSTS includes a pretty sweet class designer, System.Drawing is a set of class wrappers over GDI, and WinFX takes away ugly API's and replaces them with class wrappers. Everything in the Microsoft world is heading OO – thank goodness.
Now, I've heard the Multiple Inheritance debate referred to as a religion but I thought that OOP in general was long past being an obscure prophesy of Bjarne Stroustrup and was now accepted as the de facto way to get most things done.
I guess when it comes to the word "Expert", the old adage holds true: X is the unknown quality and a spurt is the force behind a drip.
Happy Coding
- Shaun