Force.com Data Model - Enumeration Tables versus Picklists

The Salesforce Platform allows customers to build robust, relational data models to suit any need. In fact, with tools like Schema Builder it is so simple to get started building that it can be a bit of a double-edged sword. The simplicity allows functions that once rested solely in the hands of a Database administrator to be performed by a Business Analyst, or even the End Users. However, with great power always comes great responsibility.

The number one problem I have encountered working with clients who have performed Salesforce.com self-implementations is data model related. There are several common mistakes self implementers should avoid. In this blog post I'll be discussing how heavy data normalization can work against you on the platform.

This problem often occurs when the implementation was run by an internal IT team who have traditional SQL skill sets. They will create a custom object for every single enumeration table they think is needed without regards to how SFDC relational data models actually work (picklists for example).

Heavily Normalized Data Model in MySQL

This leads to headaches when building standard reports, and usability issues when viewing and editing data with standard SFDC pages. For the above example the mult-select picklist for "Payment Options" would show up as a Related List, and the Marketing Status would show up as a Lookup. If we created this same data model in SFDC it would look like this:

Erroneously built Data Model in SFDC - Heavily Normalized Data Model in SFDC

And this would manifest itself on the Standard UI as this:

Illustration: Ugly UI

The Related List at the bottom "allows" for the multi select picklist, and the lookup in the detail section allows for the lookup. This is very nasty for end users! Imagine if you had dozens of multi-select picklists! You would have dozens of related lists! And the users would have to click multiple times to enter a payment option, and they need to do a lookup each time for the marketing status.

Not to mention they can't easily filter on Payment Options for List Views and Reports.

This can easily be corrected by using picklist and multi-select picklists in SFDC. If we use those types of fields our data model removes those 3 objects and everything resides on the Company object. The correct data model looks like this:

Who! Only 1 object! 

And it manifests itself in the UI like this:

Much Cleaner!

This is much cleaner in the UI, allows easy reporting and filtering, and saves us 3 objects we don't have to build on the back end.

In future posts I'll discuss the inverse problem where data is too heavily de-normalized on the platform.

In short, the key to building successful data models on the platform is to delicately balance the need for custom objects ("tables") and features of the Salesforce platform (picklists, multi-select picklists, record types, etc).