In a previous post entitled MVP: Object Oriented is Academic, Religion I discussed a classic example of persisting OO classes to RDBMS tables. I have now given two possible solutions in the posts When Objects Don't Wax Relational - Part 1 and When Objects Don't Wax Relational - Part 2.
Now onto Part 3, which outlines my preferred solution.
Solution 3 - One to One of Different Types
Really, what we want to say is that each ContactInformation record relates to one other record but that record is one of many different possible types. This kind of relationship doesn't exist natively in SQL Server but can be produced manually.
As with the previous solution, there is only one instance of the ContactInformation, Address, Telephone, and Email tables. Address, Telephone, and Email records are each owned by a single ContactInformation record. Nothing radical so far.
The difference is the way in which each ContactInformation record relates to its parent record.
In this solution, ContactInformation contains two columns: ParentID and ParentType. ParentID is like a foreign key field but can contain a primary key value from a Vendor, Employee, or Customer record. The ParentType field is a text field that contains the possible strings "Vendor", "Employee", or "Customer", thus telling us the type of parent record.
Compared with the previous solution, the INNER JOIN statement is much clearer in terms of how the tables relate:
SELECT * FROM ContactInformation
INNER JOIN Vendor ON ContactInformation.ParentID = Vendor.ID
AND ContactInformation.ParentType='Vendor'
WHERE Vendor.ID=@IDValue
Likewise, we can go the other way:
SELECT * FROM Vendor
INNER JOIN Vendor ON ContactInformation.ParentID = Vendor.ID
AND ContactInformation.ParentType='Vendor'
WHERE ContactInformation.ID=@IDValue
These SQL statements are very clear about their intentions. The only difference between these relationships and a standard one-to-one relationship is the addition of "AND ContactInformation.ParentType='Vendor'", which isn't really a major inconvenience. Nevertheless, this solution also carries a few issues:
1. It would be possible to orphan ContactInformation records by putting nonsense data in the ParentID and ParentType fields
2. Forgetting to add "AND ContactInformation.ParentType='TableName'" may yield undesired results
3. This is a non-standard, albeit straight-forward, approach and would have to be well documented for the benefit of those maintaining the code
4. Performance would suffer due to the doubling in the number of comparisons in a JOIN and making one of the comparisons text-based
If you absolutely must wax OO in a relational database, something like this may work for you. The use of triggers can alleviate the lack of referential integrity; 7+ years ago all relationships were done with triggers. Performance becomes less and less of an issue every year with RAM, CPU-speeds, and Hard Drive speeds all increasing. Plus you could always use constants mapped to numeric values to represent the parent type.
This solution certainly wouldn't be the answer for a small-scale project where redundant work doesn't have the same impact. This solution could really shine in a large-scale system where programmers create code-based "Lego blocks" and fit them together in different ways, particularly in ERP systems.
Maybe I'm a little crazy, but given the current features in SQL Server and what I've seen so far with O/R Mappers, this really is my solution of choice. At the very least, it's some good food for thought.
Happy Coding
- Shaun