This is the second part of the series which discusses the issues around modeling M:1 (many-to-one) relationships. If you've not read the first part, then its advisable that you read that first before continuing with this part. To keep up to date with this and other blog's you might consider registering with the RSS feed.

Part I: The Problem

Part III: BusinessKey as a value type

Suggested Solution

Having covered the issues with M:1 relationships and the three different types I'll now present a potential solution which provides the necessary foreign key information, but in a more domain model fashion. I'll also point out yet another issue with the original logical model when it comes to actually using it, which I'll also offer up a solution for.

Remembering the tree types of M:1 relationships discussed previously, lets see how we can treat them differently:

• Reference: As a reference table will typically (should only have one significant column; which must be unique!) only have one column which we can map to as a string just like we did in the logical model. The effect is that instead of having OrderTypeID we have OrderTypeName, both values are unique and therefore provide a 'key' to the OrderType table. As there's nothing else of real interest in the OrderType table, returning only a single field and merging it into the main entity poses little issue but does provide a much more meaningful value than some arbitrary key. In many cases this can avoid a second call to a service or database as we already have the pertinent information.
• Enum: An enum table is also much like the reference table, the only difference being that we can also record all the known values as an enum and tie this enum to the entity exactly like we did in the logical model. When saving the data however, we don't attempt to save the enums number value but its name. That is we send Processing not 1, as the number may have little connection to the back end database, and again we want to remove our system keys as much as possible. Therefore the save is exactly the same as it would be for a reference type, we just represent the 'string' value differently as we know what the allowable ones are.
• Entity: The entity type is the most interesting of the types. If you look at the original model vs the one produced by Linq to SQL, you might be thinking, well its basically the same what's wrong with that? Well a few things actually which is the topic of the next section.

Modeling M:1 Entities

As suggested, modeling reference and enum types are fairly straightforward just requiring an extra join to get the necessary information (which is worth it as the information is useful!). However, I've indicated that the seemingly correct entity M:1 has some issues. The issues I see are:

1. The parent entity still has the child key which is a system key, and is most likely useless to a business person.
2. If you've adopted the Linq to SQL pattern you have the same issues as already discussed with sync issues etc.
3. If you want to create a new child entity and associate it to the parent you either have to create the entity first (thereby getting its key) or using the Linq to SQL model and attach a new child entity object; which should work fairly well.
4. Alternative designs may remove the foreign key from the parent and rely on the child telling the parent what the key is. The issue with this is that you must have a full entity as the child even if you are only concerned with the parent. Also adding the association requires the full entity which may not be available at the time of creating a new entity.

In the past I've tended to treat entity M:1 relationships as reference relationship types with a single key from the entity being used as its business key. This works fairly well, that is until you get an entity that has a business key that maps to more than a single column, which unfortunately happens quite often. In these occasions, I tended to use the entities GUID if it has one or revert to the system key (hey I need something!). As I didn't want the burden of having to retrieve an entire entity just so I know the keys.

So while having the entire entity would be nice and does better represent our original model it poses a number of limitations especially if you just want to associate a pre-existing child entity to a parent. What would be good is to have just the keys, but then be able to upscale to the full entity when the use case requires it. This is exactly what I propose as the solution to this issue. Instead of having a single value to represent a complex entities M:1 relationship we have what I'm calling a business key entity which only knows about the unique business key for that entity and it acts like a value type. The parent then has a relationship with this business key entity not the full entity. Initially I thought an interface could be used to avoid having a separate business entity but as it turns out you can't serialize a property that has an interface as its return type. That basically means in order to support polymorphism (ie the ability to upscale) we have to use implementation inheritance. The original model is demonstrated below using this new technique:

For those who prefer to look at code rather than diagrams, here's how the class for the OrderDetail class and Product business key classes look. The other classes follow a similar pattern, if you are keen to see how all the code looks, leave an email and I'll try bundle it all up:

   1: public partial class OrderDetailBusinessKey 

   2: {

   3:     private System.Int32 _orderDetailID;

   4:  

   5:     //  Business key constructor

   6:     public OrderDetailBusinessKey(System.Int32 orderDetailID)

   7:     {

   8:         _orderDetailID = orderDetailID;

   9:     }

  10:     public OrderDetailBusinessKey(){}

  11:  

  12:     public System.Int32 OrderDetailID

  13:     {

  14:         get {return _orderDetailID;}

  15:         set {_orderDetailID = value; }

  16:     }

  17: }

  18:  

  19: [Serializable]

  20: public partial class OrderDetail

  21:     : OrderDetailBusinessKey

  22: {

  23:     #region Private Variables

  24:     private OrderBusinessKey _order;

  25:     private ProductBusinessKey _product;

  26:     private System.Int32 _quantity;

  27:     private System.Decimal _price;

  28:     private System.Decimal _total;

  29:     // Variables for relationships

  30:     #endregion

  31:  

  32:     #region Constructors

  33:     public OrderDetail()

  34:     {

  35:         this.Initialize();

  36:     }

  37:  

  38:     /// <summary>

  39:     /// This code initializes the domain value types for the generated properties

  40:     /// </summary>

  41:     protected virtual void Initialize()

  42:     {

  43:         _order = new bl.OrderBusinessKey();

  44:         _product = new bl.ProductBusinessKey();

  45:  

  46:         // Call the Initialize routine in the partial class

  47:         this.ExtendedInitialize();

  48:     }

  49:     #endregion

  50:  

  51:     #region Public Properties

  52:  

  53:  

  54:     public OrderBusinessKey Order

  55:     {

  56:         get { return _order; }

  57:         set { _order = value; }

  58:     }

  59:  

  60:     public ProductBusinessKey Product

  61:     {

  62:         get { return _product; }

  63:         set { _product = value; }

  64:     }

  65:  

  66:     public System.Int32 Quantity

  67:     {

  68:         get { return _quantity; }

  69:         set { _quantity = value; }

  70:     }

  71:  

  72:     public System.Decimal Price

  73:     {

  74:         get { return _price; }

  75:         set { _price = value; }

  76:     }

  77:  

  78:     public System.Decimal Total

  79:     {

  80:         get { return _total; }

  81:         set { _total = value; }

  82:     }

  83:     #endregion

  84: }

  85:  

  86: public partial class ProductBusinessKey 

  87: {

  88:     private System.String _productName;

  89:  

  90:     //  Business key constructor

  91:     public ProductBusinessKey(System.String productName)

  92:     {

  93:         _productName = productName;

  94:     }

  95:     public ProductBusinessKey(){}

  96:  

  97:     public System.String ProductName

  98:     {

  99:         get {return _productName;}

 100:         set {_productName = value; }

 101:     }

 102: }

At first glance this might seem overly complex, but if you think about what's happening its not. The perceived complexity comes from the fact that every 'entity' now has a base business key class. While I've shown them all on the diagram, in general they wouldn't need to be seen, just knowing that the property is type ProductBusinessKey would be enough without actually showing the relationship. However, looking at the model, we can see that all but the primary key IDs have been eliminated from our domain model and it looks surprisingly similar to what our original logical model looked like. This approach provides a number of benefits despite its slightly more complex model:

• Flexible persistence: You can add either an existing entity to a parent via its business key or the entire entity. The code used to persist the relationship can simply check what its type is to determine if a full entity needs updating/saving or just an association is required based on the business key. Also you don't have the duplication of keys, so you can write Order.Status = Status.Shipped or even OrderDetail.Product.ProductName = "Product B" (assuming you are using the BusinessKey not the full entity) which seems very intuitive.
• Eliminate foreign key IDs: Although its true that some entities don't have business or natural keys. In these situations, you're left with either using a GUID if available or the system primary key.
• Entities are more useful: Your entities now having gotten rid of meaningless keys (from the business perspective) can do more without requesting more data from the server. Ie if you wanted to list the OrderDetails chances are you could get away with displaying OrderDetail.Product.ProductName to the user and not have to worry about retrieving more detail about a product unless specifically requested.
• SOA friendly: This design is more friendly as the entities resemble documents more than they represent a table, as such they make for a much friendlier XML representation which is now human readable ie instead of seeing IDs for products you'll see the actual product names.

So what's the downside of the approach? From what I can see the main downside is the amount of effort required to make it all work. There are a lot more classes that need creating and you do have to do some extra joins in your selects too; however I believe it reduces complexity overall as the model is more natural and cleaner to work with. However, these issues can be mitigated via code generation, and the good news is that I'll be releasing something fairly soon (notice the vague reference) that implements this model. The sample above was completely code generated. If anyone is interested in being an alpha/beta tester, send me an email via the contacts page or leave a comment.

While this might look like the end of the discussion, there are still a few things that need addressing including inheritance and the concept of the business key as a value type. These will be covered in the last part of this series, so check back soon.

This discussion is to obtain feedback on how others feel about the approach I'm taking with my models and open a discussion on how best to model these M:1 relationships. I look forward to any feedback, positive or negative on the approach.