Nowhere is the “garbage in garbage out” adage more fitting than in business intelligence. If, for example:
- e-mail addresses are blank or incorrectly entered
- phone numbers do not include the area code or country code, or
- states are inconsistently abbreviated (e.g. Wisconsin is abbreviated “WI”, “Wis”, “Wisc”)
Regional BI reports will be incomplete and sales and service teams will be hard-pressed to follow up on new leads and issues.
Good data is the cornerstone of any business intelligence project, and good data requires forethought. It is not enough to define the fields that will be stored in your database. You also need to monitor entered data to prevent the GIGO effect. Here are five rules that will help you maintain the integrity of the data in your databases:
Database Integrity Rule #1: Avoid Free form Text Fields Whenever Possible
To avoid keyboard-induced errors, use dropdowns to force consistency in fields with finite choices. A dropdown will eliminate the state abbreviation problem mentioned above and different spellings for countries, product names, etc.
Database Integrity Rule #2: If It’s Important, Ask Twice
But what about the cases where you have to use a free-form field? One way to verify consistency is to ask the user to enter the information twice, making sure to disable their ability to cut-and-paste the answer into the second box. You’ll see this best practice often used on forms where the user must enter their e-mail address.
Database Integrity Rule #3: Re-use Data Already Captured
On the flipside, don’t make people re-enter information that’s already been through your validation process. Pre-populating new forms with information you already know eliminates integrity problems and it creates a better user experience.
Database Integrity Rule #4: Don’t Ignore Aesthetics
Speaking about the user experience, the look-and-feel of forms has been proven to affect the quality of data captured. Spice up your forms (and brand them) with a corporate banner, and use colors and borders to highlight important fields.
Database Integrity Rule #5: Give People a Way Out
Although you may groan when asked, “Are you sure you want to do this?”, how many times has this simple question saved you from sending an e-mail riddled with typos? When used on forms, this same question will cause people to re-check their information before committing it to your database.