Designing a domain model can be a tough job and if you're lucky enough to start from scratch then it becomes even tougher once you apply it to the physical database model. Over the next few posts I' like to discuss the issues we encounter when modeling this relationship type, examine the way its 'normally' done and then present an alternative that I believe presents a more domain model centric view of the world. The series will be broken up into the following sections, with this being the first. To keep up to date with this and other blog's you might consider registering with the RSS feed.

Part I: The Problem

Part II: Suggested Solution

Part III: BusinessKey as a value type

The main issue when designing a domain model is that they don't have these oddities called foreign keys, as the relationships are known by association. However, once you turn your domain model into a database schema we have to start adding all these extra keys, which get worse when you have a M:M (many-to-many) relationship! In this post I'd like to discuss how we normally model the M:1 (many-to-one) relationship and its pitfalls.

The Problem

When designing a domain model (entity model) in UML its unlikely that you'd include foreign key relationships as part of your design as this is a database concept rather than a domain concept. If we take a very simple and arguably contrived example (its very basic just to highlight the concepts I want to discuss) of an Order it might look something like this (note I'm using the VS class designer which doesn't support multiplicities hence I have to use two associations to highlight relationships):

In this example we have three different types of M:1 relationships being modeled, which are:

• Entity - The OrderDetail --> Product and Oder-->Customer are M:1 relationships which map to other entities within the business domain.
• Reference - The Order.OrderType property is one that is restricted by a known set of values. However, this set of values can change over the life time of the application. That is its data driven and so the current values can't be coded directly within the application. Also a reference value is usually defined as a single field with no other business properties.
• Enum - The Order.Status property is also restricted by a known set of values. However, unlike a reference type the values don't or can't change. These values typically drive logic which is set during design, adding a new value after deployment wouldn't add value as the application wouldn't know what to do with it.

Now that I've defined the different M:1 relationship types, they would typically be modeled in the database all the same way as foreign key constraints to their respective tables. The following diagram shows the ER (Entity Relationship) diagram of the physical database model.

So the next step in modeling our sample is to create the actual business entities that we can use in a real application, taking into account how our data is physically stored so that we can map our logical to the physical as easy as possible. This of course is where things typically start to deviate from our nice logical model. We have to take these foreign keys into consideration as the database needs them, so typically we end up with something more like the following figure than our original logical model. The following was created using Linq for SQL, which like most O/RMs (the Entity Framework will improve on this) will create something like the following:

You'll notice from this model that it more resembles the database model than it does our original logical model. We have an extra entity in the OrderType which we'd probably have in our logical too, to allow for maintenance but we didn't need to tie this object to our Order entity. The changes that were made to our original model can be summarized as:

• Simple types are now complex types, namely instead of a simple enum or string we now have full entities with their associated complexities.
• Our 'domain' entities now have system keys liberally scattered throughout to support persistence back to the database.

We also have the following issues as a result of this:

• We expose a lot of internal information about our database when we expose foreign keys, exposing this data presents a security risk as a hacker may attempt to corrupt your data by altering the primary or foreign keys. This becomes a bigger issue when you expose your data via web services. 
• In the case of Linq to SQL we also have duplication of foreign key fields, namely we have both an OrderTypeID (foreign key field) and an object reference to the OrderType entity. This just looks plain ugly! Also lets say you want to change the OrderType to a different one, if you change the OrderTypeID from 1 to 2, and you did have an OrderType entity associated at the time, you'd be out of sync! The entity version of the OrderType wouldn't match the change you've just made. In the Linq to SQL architecture you're not even allowed to change the foreign key once its been set! You have to change the OrderType entity which will then update the Order.OrderTypeID. Which only goes to highlight the problems.

So now that I've expressed the issue and the potential problems with this type of design which is an all to common pattern, with the exception of the double foreign key reference that Linq to SQL uses . The question becomes why? The reasons as I see it are two fold:

1. Effort, it takes some effort to design more closely to the original logical model
2. Architectural/Technical in that we need those damn keys, so the issue is how do we make a nicer model without loosing the essential information we need?

The next part of the series will examine what we can do to fix this situation and bring our model back in line with what we originally intended.