The Field. You better have the right one.

In ancient times (The 80's and 90's), database consultants told stories around the campfire of projects that went wrong. Here's a tale of what can unfold from storing information in the wrong type of field in your database.

Charlie's IT consulting company relied on the Customer Relationship Management (CRM) database for fast, high value information, including a field displaying the hourly charge rate on each customer. Estimates were easy. Look up the client and multiply the hourly rate by the estimated hours. He had client rates from $150 to $180 per hour.

A new product was released and IT consultancies were scrambling. They called it Windows and it was discounted to get traction in the market, a smart strategy if it worked. If the business world had Windows there would soon be plenty of IT work at normal rates. This was the only time that Charlie would reduce his rates so his consultants were to change the rate field to $100 when estimating a Windows project.

The Windows implementation boom eased and Charlie had some turnover of staff. Now he wanted to bid for bigger, faster and reliable systems, but at the higher rate. On a proposal or tender, his consultants looked up the hourly rate and some accidentally quoted $100 to existing clients. So it could be 1,000 hours x $100 per hour = $100,000, a project taking months to complete. They were landing jobs like you wouldn't believe but if the correct rate was 1000 hours x $180 per hour = $180,000, then you have $80,000 of lost revenue.

Charlie's rate field was a flat file. Being numeric it would hold only one hourly rate. When the Windows consultants changed it to $100 per hour, the client's normal rate was overwritten. If Charlie had realised, he would have had a field that listed all rates ever quoted to the client. Then you would see the earlier rate of $180 per hour for several years suddenly dropping to $100 per hour. Seeing that variation, you would go check the rate. Charlie lost a hundred thousand dollars but learned his lesson. His CRM consultant replaced that flat file field with a "one to many" field, so staff would have the history of each client's hourly rate accruing. It pays to know that different database fields suit different processes that you run in your business.

This story was simplified to make one point clear, as many will understand. It shows how valuable a little knowledge can be to people who don't yet fully understand their CRM. If those people read this today and walk away knowing to ask that one question of their CRM consultant, then the world is a better place.