The inspiration for this vlog is from my travels around Europe last year. I traveled to 10 different places and saw many magnificent masterpieces. Examples were Gaudi in Barcelona, Michelangelo's David and his other piece of work, the Sistine Chapel in Vatican City.
Inside the La Sagrada Familia in Barcelona. One of Gaudi's masterpieces.
It was such an honour seeing these pieces of art and witnessing the intricacies of each detail was amazing. A lot of planning and design would have been key to these long lasting art pieces and the same applies to designing a solution for Microsoft Dynamics CRM.
So without further ado, this vlog covers the use of Entity Relationship Diagrams [ERD] for solution design. Entity Relationship Diagram is a modelling technique for databases and describes the entities, attributes and relationships. I first came across it when I was studying in my second year of university. I didn't think I would use it but hey presto, I am using it today in each project I work on.
What type of Entity Relationship Model do I use?
I use the Crow Foot model where it displays the "many" notation of a relationship in a way that looks like a crow's foot. I like this type of ERD because for me, I find it easy and it actually suits the customisation methods of Microsoft Dynamics CRM.
At what point do I apply it in my solution design routine?
ERD is something I like to apply prior to building (customising) CRM. I usually tend to create ERD's after the following has been achieved:
- Gathered requirements from the client through numerous workshops.
- Assess what is solution design vs standard functionality of CRM. Part of this step is assessing the "CRM Fit" of the requirements - what is minimal configuration vs high development.
- List the requirements in the form of user stories including the narratives behind it which contain the business rules.
- Identify the different elements in alignment to the user stories
- Entities
- Fields
- Relationships
OK so what relationships exist in CRM?
The different types of notations that reflect the true cardinality of a relationship in CRM is listed below. These are what I find applicable to CRM due to the nature of customisations.
- One (and only One)
- Zero or One
- Zero or Many
The first two notation's in the "N:1" relationship in CRM customisations is represented by a Lookup field.
If the Lookup is mandatory, then it is a One (and only One) notation.
If the Lookup is not mandatory, then is a Zero or One notation.
The many notation of a "1:N" or "N:N" relationship in CRM customisations is visually represented by a Sub-Grid in the entity form.
There is the following notation but it's not really applicable to CRM as the "many" notation of the relationship will always be a Zero or Many due to how Sub-Grids work in CRM.
- One or Many
A Sub-Grid can exist without a record being created, there is no rule of a Sub-Grid needing to have at minimum "one" record at all times. This is because of how Sub-Grids are not enabled on the record until the record is saved.
How do I start putting it all together?
Here's the method I apply when creating an ERD:
- Bring up your two entities - I will call these two Entity A and Entity B.
- We begin with the maximum cardinality of the relationship. Start from right to left and think: Entity A can have many or 1 of Entity B.
- Then from right to left, think: Entity B can have many or 1 or Entity A.
- Sweet! Then we finish with the minimum cardinality of the relationship. Start from right to left and think: Entity A must have zero or one of Entity B.
- Then from right to left, : Entity B must have zero or one of Entity A.
I repeat the above for all my entities that I have identified. I will also usually have conversations with either the solution architect or developer ninja if I need to bounce ideas off someone else. Typically in the end I walk it through with both of these resources so that anything can be corrected before customising the CRM.
Basically, you need to think about
- What is the maximum rule?
- Entity A can have ..... Entity B
- Entity B can have ..... Entity E
- What is the minimum rule?
- Entity A must have ..... Entity B
- Entity B must have ..... Entity A
These will form the cardinality of the relationship.
Breaking down N:N relationships
Sometimes you create what we call "native" relationships in CRM. This is where you create a many-to-many relationship from one record to another. In my example in my vlog, I had a N:N relationship from Contacts to Property in my initial ERD.
I would have created the N:N relationship like the following:
This is OK, it's not a bad thing to do. However N:N relationships can be troublesome at times such as
- When you want to perform a data import using the OOB data import method, it will not work for a N:N relationship. You are out of luck in this scenario.
- When building custom reports through SSRS or FetchXML, it can be a pain.
- When searching in Advanced Find, it too can be a pain. You can do an Advanced Find search for N;N relationships if you enable the following in the relationship record. But it will not give you exactly what you are after.
The solution to minimizing your pain and swearing threshold with N:N relationships is to create a manual intersect entity that will form as the "in-betweener," it is your custodian of data that sits between the two entities. It will pull through both entities in the form of Lookup fields. This is what your ERD would then look like:
Final touch
I usually provide a legend where I colour code what is a custom entity. And oops, it should say in my ERD legend "custom entity" not custom box entity :). This helps you, the developers and client understand what you are building that is not "out-of-the-box" for the solution. The ERD helps visualize what will be an extension of Microsoft Dynamics CRM from the custom entitiy colour coded boxes in the ERD. Ideally the ERD reflects the "CRM Fit" of the requirements I spoke about earlier in this post.
Start customising
When you have your ERD completed, you can start customising. Anything that is a Sub-Grid is in the zero or many notation of the relationship which will exist after the zero or one, or one (and only one) notation of the relationship is formed through a Lookup field. It's pretty much straight forward from here onward.
It may take a while to get used to creating ERD's and then customising CRM from your ERD but trust me, it is worth it. Sometimes, your initial ERD may not work out at first but that shouldn't stop you from changing it. I do update the ERD overtime as I either add more entities during the development/build stage or I find that something that was originally thought to be done in a certain way may change. Perfectly normally to update your ERD, hopefully not too frequently!
Why are ERD's awesome for CRM?
I like ERD's because not only does it help myself in terms of understanding the customisations required, it helps developers when they do custom development. Why? Well they can see the solution design outside of the clickity click of CRM. What I mean by this is clicking through the records to find out how is Entity A related to Entity B. Developers are able to see the different relationships in the form of a diagram which assists them in building plug-ins,
What else is this entity intertwined with.. ahhh, I see it's always going to be associated to record XXX due to the mandatory field from this relationship of "one and only one." Therefore my plug-in can go and update the status of record XXX too.
Maybe I can make a unicorn appear too? Oh wait, wrong system... Hey Benitez what I can make the plug-in do is...
It can also help design within the project scope. If there are way too many entities for a small project budget, it could get curly in terms of the time you have to complete the customisations plus development plus reports plus training... the list can grow.
If you are a service provider, ERD's are handy when you hand over (see what I did there? Handy... hand over...) to the support team in your organisation where they look after the client after go-live. It helps them understand on a high level what has been delivered for the client. It will help the support team tackle future enhancements as they will need to know how the solution has been designed to date to add additional functionality. From here, they can update the ERD.
My final reason for ERD's being awesome is that it helps you create and design a solution that is simple and not over complicated. If you lay it all out and it makes sense to you, your developers, your solution architect and the client then you're going down a positive path.
If you receive questions about your ERD in terms of why there's XY relationships to get to Entity D, rethink and adjust it. Take on board the suggestions of your team mates and take another look at your ERD. It helps to run it past others to not only verify your design thoughts but also promote team work. Ultimately your team will be working with you in building the solution. If they provide their input to the ERD that's great, they're in there with you from the early days of the project.
Summary
ERD's are valuable for solution design of Microsoft Dynamics CRM. It allows you and your project team to understand what is being built in alignment to the user stories and what the customisations will be in CRM. Rome wasn't built in a day and an extended version of Microsoft Dynamics CRM will certainly not be built in a day either. Design your masterpiece using ERD's and design it in a way where it will last a life time.
New and cool features are continuously being introduced by the Microsoft Dynamics CRM product team however as most fellow consultants know, we build solutions that extend Microsoft Dynamics CRM. Therefore it is important to have a good understanding of the building blocks of Microsoft Dynamics CRM. ERD's can go a long way before you start to customise. Bring out your inner ERD powers from within and have a go the next time you do solution design.
*CRM'ing with passion*
Toodles! I'll leave you with my photo of Michelangelo's David. His behind, to be more specific. Magnificent.
