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