There’s a thin line between having your Salesforce end up as a glorified ‘dumb database’ where data entry is considered as an overhead vs. a smart, real-time insight generating database where data entry is considered worthwhile given the analytics it generates.
If the above sounds familiar to you and you’re getting anxious to know the “secret Salesforce sauce” that causes all the difference, then this post is for you.
As someone rightly said, “You are only as strong as your foundation.” In order to realise your Salesforce investments’ worth, you need to get your foundation design RIGHT, which in Salesforce’s case is the Data Model.
Before we get to the secrets of getting your Salesforce Data Model design done right, let’s reiterate WHY Data Model design so important.
1. Go top to bottom when designing a Data Model, first understand the Vision/Challenges/Pain Points, then understand Reporting/Integration/Security/Performance needs and then finally design and build the Data Model.
2. In order to understand when to go flat (de-normalize) or not to go flat (normalize), let’s consider the below user stories.
Let’s take the first user story to understand when not to go flat.
Here Data can be modelled in below two ways.
User Story – AS A Sales Manager I WANT to track commission percentages for all the sales rep working towards getting sales closed as per their individual contributions SO THAT I can generate an important KPI report showing Commision Share Per Consultant Per Quarter.
From the story we pick up, we are able to derive below specific needs,
Here Data can be modelled in below two ways (green and red):
The focus of this user story is on the analytics needs, so from a reporting perspective, it makes sense to store Sales Rep’s split % into a separate table of its own (normalize/not go flat) because, now we can easily build a report to generate metrics like commision share per rep per quarter in minutes time by using report type Sales with Sales Rep Splits and grouping columns by sales rep and columns by sales date (grouped by quarter).
Now let’s take the second user story to understand when not to go flat.
User Story – AS A Sales Executive I WANT to easily enter mobile/fax/home contact numbers for a Lead SO THAT I can quickly find contact numbers when looking at lead record page, list views & reports and establish contact.
From the story we pick up, we are able to derive below specific needs.
Here also Data can be modelled in below two ways,
But here, as we don’t have any specific analytic needs on contact numbers, instead the focus is on the user experience in entering and viewing the data. So here it makes sense to go flat and simply create fields of phone data type on Lead itself, so that those fields can then be easily added to the list views built on Lead object.
3. Understand cardinality in Salesforce (1:1, 1:n & m:n)
Create Lookup/MasterDetail to achieve 1:1 & 1:n & Create an additional junction object to achieve many to many relationships
4. There are two types available to build relationship between objects, use below table to understand when to use which,
5. Let’s consider you have built a lookup relationship but still desperately need rollup fields, worry not – there are many AppExchange packages like Rollup Helper and open source code projects like the one here which I built available for your rescue.
6. Understand when to leverage standard objects vs. when to build a custom object, using the below pointers, Start with, understanding the Salesforce Standard Objects and fields and their ‘Special Features’
7. Define the user stories in a way that expresses a requirement in a way that provides context and justification.
8. Rename Object, Tab, and Field Labels to make your users feel at home.
9. Get into continuous refactoring mindset, what is right today might not be right tomorrow, so don’t be afraid to refactor/redesigning your Data Model to suit your needs, and use tools like Dataloader to massage your existing data to match new Data Model design.
10. Be thoughtful about your naming conventions, because what you name something can live with you forever. Remember to add descriptions & helptext everywhere it is possible in order to self document your org.
Here are a few resources to learn more than just tips and become a Pro at scalable data modeling: