Wednesday, February 16, 2011

MSCRM vs Normalization?

Today I ran into a silly discussion about Microsoft Dynamics CRM and normalization in a meeting. A senior consultant in my team made a statement that "MSCRM database is not normalized" during the meeting. I was astonished and shocked when I heard this statement. I challenged him and asked him to prove where his point stands. He claimed that CRM picklist metadata is not normalized. Well, that's sort of true, we cannot re-use a picklist across entities in CRM 4.0, and I told him that's not going to be the case in CRM 2011 which has the support of global option set so that you can define reusable picklist options. He didn't give in to that because the project we were discussing is still using CRM 4.0. The silly part of the discussion was, I firmly believed that MSCRM platform supports and encourages normalization in every possible aspect, but his point was that MSCRM is totally the opposite of database normalization (just because of the picklist options), which was so wrong.

Let me have a quick summary about my points:
  • Microsoft Dynamics CRM database itself has respected database normalization principle in general. But some CRM metadata tables and some few business entities are not fully normalized for either performance reasons, or due to constraints of the platform.
  • Microsoft Dynamics CRM fully utilizes relational data model which is the foundation of the platform.
  • Microsoft Dynamics CRM fully encourages normalization through its flexible entity relationship models including one to many, and many to many. Those relationships are not only available to two different entities, but also can be applied to the same entity through self-referential relationship. What level of normalization that your custom application can achieve on CRM platform really depends on how you design your CRM entities and their relationships. You use the relationships between entities to normalize your data, which is an almost identical approach to what you would do in any traditional data modeling practice, where a CRM entity can be seen as a database table, while a CRM field (or attribute) can be seen as a table column. In this sense, CRM entity & database table are pretty much a pair of interchangeable terms, so are CRM field & table column.
  • If you are currently using CRM 4.0, and you want to reuse some picklists. An alternative solution would be using a lookup entity, which contains all the options, so they can be re-used and referenced by different entities. If you want to provide better usability, you can use a script from one of my other blog posts to convert the lookups into picklists. 
When it comes to the discussion about the level of database normalization that we should be actually pursuing in the real-world project, I have seen some people really become religious simply because that's what they have been told from the textbook. I don't want to start a war here, but I do want to make my points quickly, my take or solution to this issue is, normalize to the point that your data model looks logic enough so that it reflects and satisfies your business requirements, while not sacrificing the scalability and maintainability of your application.



  1. Daniel,

    On a related issue: I am working on a data migration into a very denormalised CRM data model that has been justified by the developers on the basis that (and I paraphrase) "...if you create a custom entity with no relationships, CRM can automatically generate display and maintenance forms for it, but if it contains relationships, you have to develop them yourself...".

    Is that assertion correct? It has led to a very odd model with (for example) addresses scattered through a dozen custom entities, reflecting application forms on which addresses might be recorded.

    Simon Sellick

  2. @Simon, I am relatively sure that the assertion itself was wrong. When you create a custom entity, CRM does automatically generate a default form for the entity. The default form consists of one field which is the primary attribute that you have defined when creating the entity. Although some options (such as the Ownership, Notes, Activity options) you choose when defining a new entity may affect the number of relationships that the new entity will have, the default form would be the same regardless.

    You have got a good point with regard to Address entity, I agree with you that Address entity is an odd one, which on one hand tried to respect normalization principle by having a centralized entity, but on the other hand it scattered in a number of CRM business entities, including account, contact, quote, salesorder, etc. I tend to think that MSCRM team chose this relationship model for performance reason, or it is probably just a legacy. But this is just my speculation.

  3. I've been having similar conversations relating to normalisation and addresses (and other contact information such as phone numbers, email etc) keeps popping up in the conversation.

    I did read a post somewhere relating to CRM2011 where they mentioned that although the account (I think) entity has what appears to be a denormalised set of address fields, plus a set of "more addresses" which is more normalised, CRM is actually saving the main addresses data into the seperate address table as well. A little odd, and does feel like some sort of historical legacy type functionality.

    As you mentioned, its all about getting the right balance between business requirements, maintainability, and scalability. We played with going all out normalised, and while quite possible to achieve (we're talking CRM2011), you end up with a clunky interface requiring lots of navigating through multiple popup select record windows.

    We may build some custom controls to provide better UI options with normalised data, but until then our users are quite happy with a more pragmatic approach.

  4. Hi Daniel

    To act the Devil's advocate - there are primarily two things in CRM (both 4 and 2011) that encourages you to create a less than optimal normalized design.
    1. The inability to display related information other than primary attribute on forms, and 2nd related information on views. This encourages you to duplicate information from where it actually belongs, to entities that just want to display the info.
    2. The mapping function on relations encourages the designer to copy information rather than relating to information.

    With that said - I definitly agree with you that the CRM platform supports creating fully normalized databases. It is just a matter of your own qualities in information modelling. A good platform is not a guarantee of good implementations, only a possibility.

    We simply cannot prevent implementations where proper possibilities are tweaked into lousy datamodels, but I dare to say that is true for all platforms and architectures.

    Jonas Rapp

  5. @Steve, thanks for sharing your thoughts.
    @Jonas, thanks for bringing up the two important facts. Excellent observations, I could not agree more with you!