The 2006 Toronto Code Camp was the first .NET code camp in Toronto and the largest .NET code camp in Canadian history. There were over 200 geeks present, all focused on programming for the .NET Framework. What a great experience. As it turns out, I'm one of the few that actually look like geeks. Most seem to be normal and a few are even metro-sexual.
There were 20 available 1 hour 15 minute talks spread out over 4 streams and attendees weren't locked into a particular stream. It was a buffet-style feast of current and future technical knowledge including Threading, LINQ, Visual Studio Team System, AJAX, Microsoft Enterprise Library, Mobile Development, .NET Futures, and Code Access Security.
One aspect of the code camp was the target of having 10 neophyte speakers. I had the privilege of being one of them and spoke on creating applications with a Professional Look-and-Feel using Infragistics NetAdvantage. It was the first time that I have ever spoken to a technical audience (aside from coworkers), given a Power Point presentation, and lectured for over an hour.
It's spooky and humbling to realize that I have actually accumulated enough experience to have something worth listening to. It is largely thanks to wealth of guidance given directly and indirectly by peers that I shall never be able to equal in knowledge and understanding.
It's also nice to learn that I rather enjoy public speaking. Of course, having a good and responsive crowd made the whole experience quite pleasant. I'm looking forward to speaking to the East of Toronto .NET User Group in February.
Winning the grand prize of an MSDN Premium Subscription with Team Suite was the thick, gooey, chocolaty icing on the cake.
The day will certainly be one of my best and most memorable ever. It may not beat out my absolute most memorable days, including my wedding day, the birth days of each of my children, or the day I accepted Jesus, but it sure rates up there as a solid second.
Here's to the 2007 Toronto Code Camp. I hope to see you there.
Happy Coding
- Shaun
In a previous post entitled MVP: Object Oriented is Academic, Religion I discussed a classic example of persisting OO classes to RDBMS tables. I gave one possible solution in the post When Object Oriented Doesn't Wax Relational - Part 1. In part two, I will examine another possible solution.
Hats off to Chris Falter for beating me to it. He added a comment at the end of the Part 1 post, suggesting the very solution that I planned to cover here in Part 2. We each have a slightly different angle on it but are talking about the same thing.
Solution 2 - Reversing the Relationships
When performing an inner join, SQL doesn't care which field is the primary key and which is the foreign key. In fact, it doesn't even care* if either field is a primary key or foreign key.
I have seen solutions that use this to their advantage. I've even authored one. When applied to our example, there would be single ContactInformation, Address, Telephone, and Email tables. Address, Telephone, and Email all have a foreign key field called ContactInformationID that relates back to its parent ContactInformation record.
In traditional relational database design, the ContactInformation table would a foreign key field that relates to its parent. Because of our unique scenario, each ContactInformation record could be "owned" by a record from different tables - in this case, Employee, Customer, or Vendor. Thus, ContactInformation cannot have a single foreign key field that references a parent record.
Instead, the parent tables each have a ContactInformationID field that is a foreign key and relates to the ContactInformation table's primary key. INNER JOIN statements become fairly simple:
SELECT * FROM ContactInformation
INNER JOIN Vendor ON Vendor.ContactInformationID = ContactInformation.ID
WHERE Vendor.ID=@IDValue
This certainly allows us to map one table to one class and it makes writing persistence code much easier. We are at least getting the 1-to-1-of-different-types correlations that we desire with far less work. This solution does, however, have some short-comings:
- It would be possible to have a Vendor, Employee, and Customer all reference the same ContactInformation record, thus defeating referential integrity
- The ContactInformation would have to be persisted first, which isn't really a big problem
- The reality of the database structure is opposite the intention of the solution, which is confusing
- The intention of a SQL Statement is easily misunderstood
- This is a very non-standard approach and would have to be very well documented for those maintaining the code
It's not to say that a solution such as this couldn't work, but, like growing a Bonsai Tree, the conditions would have to be perfect. It would be best applied to a large-scale system where database performance is of the essence, all code is object oriented, the code is all maintained in-house, all developers are well versed in the art of OOP, all developers fully understand why relationship-reversal is being used, and the scenario warrants this type of relationship. This may be a pretty tall order.
Some Nay-Sayers may view this solution as invalid due to bad database design. It's not to say that they would be wrong in this view, but when convergence point between two very different theories, methodologies, or technologies is often quite messy. As architects, we have to choose whether we want that mess in the database (as with this scenario) or in the code.
Typically, the messy part ends up in the code. As a lover of OOP and lukewarm to relational databases, I somewhat begrudge this. But I appreciate why this is the case. Often, several different solutions from several different vendors will be written against the same database. The cleaner the database, the better all around.
So would I use this solution? It sure wouldn't be my first choice but if the conditions were fertile and nothing else would work then I would at least consider trying to grow a good crop of reversed-relationships just to keep my farming simple.
Happy Coding
- Shaun
* There will, of course, be mild-to-moderate performance issues if the fields utilized in the JOIN are not indexed