Choose between Record Types or multiple custom fields – Have you ever come across this dilemma while designing data model in Salesforce when dealing with huge data volume?

Record Types is an excellent feature in Salesforce which avoids creation of duplicate table structure for data which falls under the same umbrella but differentiated by some categories. A single object can be elegantly used as multiple tables provided the core purpose is same. Although it is the same object, they appear and behave like separate entities.

A simple example can be Reference Tables. In any other RDBMS we would create separate tables for each reference category; however in Salesforce we can achieve this using a single custom object with multiple record types.

Now, let us take a more complicated scenario wherein a single entity can play multiple roles but the data that we are dealing with it is huge. You would also like to run analytics at each role level.

The first thought, perfect fit for a Custom Object with multiple record types to support the roles  gives a perfectly normalized structure. But should we really go with this structure when we are dealing with huge data?

My suggestion to it is No. Why?

Here are the reasons –

1. For each record that we add in Salesforce, it takes 2 KB of data space.
2. So, if we seperate them in different structures either through record types or seperate custom objects, we would be utilizing additional 2 KB data space
3. Total data space used = 10 KB, instead if we have additional fields on the same Person object the space used = 2 KB.
4. Salesforce data space count is constant for a single record irrespective of the number of fields that you have populated with data. So, we can best utilize this advantage and optimize the data usage.

However, the solution does not give us the flexibility to seperate the data at each category and run straight forward analytics. But with these simple tips we can still achieve the same –

1. Have seperate sections on the layout to clearly categorize the data
2. Have a mandatory field identified for each category to be used for list views and reports as a differentiator.

When we are dealing with huge data volume and do not want to pay extra for additional data storage, it is always better to follow a de-normalized structure in Salesforce wherever possible.