The Musings of Chin - Best Practice: Correct Data Formats

This week, Chin covers the best practices for entering correct data formats in custom fields.

Let's learn about the importance of correct data formats!

WHAT? and WHY?

When storing any information into any system, it's important to understand what type of information you're dealing with and to format it accordingly. The purpose of this is to store information in a meaningful way so that you can perform calculations or analysis on it. Take the example of someone's name; the easiest way to store this would be in a single field called 'name'.

However, problems occur later on if any manipulation or analysis of this name field is done. It cannot be separated into first/last name, meaning contacts cannot be sorted by last name if one so chooses. The name field cannot simply be parsed and separated by looking for an empty space between the names either because we cannot be sure of the format of the data contained within the field. Users may have entered their middle name as well, or perhaps their first name is composed of two separate words...etc. resulting in multiple space breaks in our name field. To correct this problem, multiple input fields need to be used for a name. These fields force the user to be specific and enter the data as such. This can be done by using separate fields for first name, middle name, last name, and even a title field for Jrs/Srs.

In order for information to be meaningful and useful, it must be formatted and stored correctly. It is unsafe to try to manipulate unstructured and unformatted data into another format.

Use the following steps to ensure that data is stored and represented correctly:

1) Design your architecture and schema in a meaningful way to begin with (i.e. using multiple fields to store a name in the example above).

2) Force the data input into a specific format of your choosing (i.e. using data validation and transformation techniques, validating that users can only enter alphabetic characters, apostrophes, and hyphens as their name input).

WHERE? AND HOW?

The main area to consider is the custom fields that you create and how you design them. Below are some specific examples using the validation and formatting option of a Single Line Text field:

1) Date/time: Date/time values should always be stored as a single Date/time value. The year, month, date, day as numbers or words, as well as the time, etc. can all be extracted from this.

Consider how the standard date input fields are broken up into a date, hour, and minute input but the system actually stores all this data as a single field. MySQL (and most other areas for easy sorting) uses the format "YYYY-MM-DD HH:MM:SS". By using the Date validation on a custom field, uniform date formatting is stored in the system regardless of how each individual user specifies the way they wish to view dates.

2) Numbers: Numbers always need to be entered accurately. Consider how you would store currency data. When you use the Numeric validation and Currency formatting on a custom field, the system actually stores the number value to the full number of decimals and without a currency symbol (i.e. "12.948576"). However, when the system displays this field, it is rounded to two decimals and with a currency symbol (i.e. "$12.95").

3) In General: It's important to have a specific format for any standard data. Take for example, the Canadian postal code which one could enter as "M5V 2H1" or "M5V2H1" (among other variants using lower/upper case letters). The best practice for this example would be to enforce that the user always enters one specific format with upper case letters. This can be done using the validation configuration of a custom field.