Optimizing Your Data Model – Common Mistakes and How to Fix Them

Most of our applications these days are backed up by a database of some sort. In Salesforce, this is particularly true since most of us develop business applications where data has to be… well… stored, somewhere. Unfortunately, Salesforce makes it really easy to create objects, but Database design is more art than science. Sometimes the best-intentioned admins can make mistakes that are hard to fix later.

Not Normalizing Correctly

Database normalization is a process to design a database to improve efficiencies and reduce data redundancy. Properly normalized databases are easier to maintain and don’t make users record information in several places. There are countless pages to be written on how to properly normalize a database (you can start here if you are interested), but we can cover the 2 most common issues with normalization.

Redundant Fields

One of the most common problems is the existence of redundant fields. This occurs when some information is repeated in every row of an object, instead of being abstracted to a new object and connected to the original one via a Lookup.

An example could look like this:

In this example, you can see how the Department and the manager is repeated multiple times. Furthermore, when the manager changes, the change must be made in four separate records. This can cause a myriad of problems, including departments that have two or more managers. You can see how reporting can eventually be a problem.

We see this fairly often when a picklist is created to segregate records (Department, in this case) and then a further request (for Manager, for example) is not accompanied by a small refactoring. Fortunately, this problem has a straightforward fix.

We can see here, what the ideal scenario is:

Now we can update the department managers one time (we could even make this a lookup to the User object!) and greatly simplify how data in this object is managed.

Repeated Fields

Repeated fields are another of the really common problems with data management, this is what it looks like:

You can guess that the company grew and started to open new stores, and the admin correctly imagined that a Store table would be needed. However, he/she simply added new space for each store, instead of thinking about scale. In this model, what’s going to happen when the company opens a 4th store? And a 5th? Yup… you guessed it, now we’re creating fields every time a store opens. Let’s hope that the company doesn’t grow past the 500 custom fields limit!

Take a look at the solution below:

We created what’s normally called a Junction Object. This object allows us to create infinite (well, as long as we have storage space) combinations of stores and products, allowing this company to grow better. You could even add a Price field in that object, thereby charging different amounts per store.

This solution, however, is not always necessary or appropriate. For example, think about Addresses or Phone Numbers. A company could arbitrarily decide to have no more than 2 addresses for a person (Salesforce is one of those, with Billing/Shipping for Account and Mailing/Other for Contact) or decide that storing 3 phone numbers is ok. In those cases, it is perfectly acceptable to not normalize, since it’s clearly more convenient to have the fields in the same object, with no related lists or extra clicks to see the information.

PS: There are several good reasons to denormalize a database. In fact, we’ll mention one below, so do your homework before attempting a big re-architecture. If you’re not sure whether you should or shouldn’t normalize your Database, engage an Architect for help.

Lookups vs Master-Detail

Have you ever seen an org where there are two objects related by a lookup, and then a gazillion sharing rules that are identical in both? No? I have many times.

There are a lot of people who don’t know there is a difference between a Lookup and a Master/Detail and make mistakes that have a high-performance cost. Essentially, both M/D and Lookups behave in the same way, with a few notable exceptions:

• A Master/Detail is required on the child

• Children records of a M/D relationship inherit security from their parent

• Rollup Summaries can only be created in M/D relationships

• Deleting the parent record in a M/D automatically deletes its children

• Up to 2 M/D relationships are allowed per child object (vs 25 for Lookups)

What this means is that choosing a M/D relationship allows you to inherit security to children (potentially saving you from duplicating lots of sharing rules) and to automatically get rid of children records when the parent is deleted (Note that if you have 2 M/D the first one created is the primary and therefore is the one who controls security. The second one, however, is good for everything else M/Ds do). Those are two huge reasons to choose M/D relationships over Lookups. The reverse is also true, if you need the flexibility of the lookup field not being mandatory, or you need to be able to share records differently, you should lean towards a lookup field instead of an M/D.

Making the right choice when connecting two objects together is critical and you should be very careful and deliberate when you do because the impact of that choice is going to be felt throughout the application.

Not taking Skew into Consideration

I love talking about skew, not because I am a fan of the problem but because so few people even know that the problem exists. Skew is, essentially, when you have 10,000 children to a parent. This is manifested in three ways:

1. Ownership skew

2. Account data skew

3. Lookup Skew

The first item deals with a queue or user owning 10,000 records of the same time, the other two are basically the same thing, and they involve a record being the parent of more than 10,000 child records.

Skew is no joke, and it can cause severe performance disruptions especially during sharing calculations because the parent stays locked for as long as the calculation happens in the children (sometimes even one child being added can lock the entire tree!). And sometimes the children are locked if a change in the parent happens!

There are several solutions for this, including re-distributing children records to multiple parents or creating an intermediate layer of parents to act as a “go-between” the real parent and the children, but the best solution is to think about the problem in advance and architect for it beforehand.

Skew is a fairly simple problem to understand but oftentimes it is really hard to fix because you have to work around whatever code and click-based solutions you already have in place. If you find a skew in your org or suspect you may be suffering from it, engage with an Architect as soon as you can and ask for him/her to help you design a path to get out from under the issue.

ABOUT THECODERY

theCodery understands the challenges in modern tech stacks. We have developed a personalized approach for each Salesforce Cloud implementation while leveraging our deep been-there-done-that and best-practice expertise to ensure you get the most value from your Salesforce deployment.  We take an agile approach with all development, optimization, and integration projects.  Whether you are trying to broaden your engineering and development capabilities, reduce technical debt, integrate tools you are unfamiliar with, or create new applications, theCodery has a proven track record of solving problems and streamlining complexity.

If you have any questions for theCodery about our team, our process, or the clients, please reach out to us at: https://www.thecodery.io/contact-thecodery

theCodery: Accelerate your time-to-value on Salesforce with a trusted partner that delivers scalable architectures that are tailored to delight your customers.