214-748-3647, Hello…Is it Me You’re Looking For?

A developer, Justin Reese, just shared his own story about using the wrong datatype for phone numbers, over at DailyWTF.  He did this in an XML document, but I see the same mistake being made over and over again in data models and database designs.  In fact, this is a key part of my Data Modeling / Database Design Blunders presentation.  

My client reported that was a strange bug on a certain page in an app I built for them. Where the contact information for a series of offices was being displayed, all the information was correct except for one piece: the phone number. For multiple locations, the phone number displayed was the same: 214-748-3647.

I love reading about his quest to track this problem down and what the issue turned out to be.  I also love that he wrote a DailyWTF about himself.  We all should be doing that: sharing our mistakes so that others can learn from them.  I call that "Free Advice That’s Paid For" in my blog posts. 

In my presentation my first blunder is using numeric datatypes for data values that aren’t actually numbers.  Telephone numbers are one of them.  They may have leading zeros.  We don’t do math on them, usually.  ZIPCodes are another example. Store them as INTEGER and you’ll lose leading zeros.  And many Postal Codes have letters.  Think you have only US customers? You might.  But customers, people who may owe you money, have a way of moving around.  Of course, every design decision comes down to cost, benefit and risk.  So some designs may make a good case for using numeric datatypes for storing values that aren’t actually numbers. But all the protections for data quality and correct retrieval need to be designed in, too.  That’s the trade-off.  Also in my presentation I give a rule of thumb:

If business users call it a number, it ain’t a number. 

Customer Number. Account Number.  Vehicle Identification Number.  Social Insurance Number.  Social Security Number (yeah, it’s all numbers now, but nothing would stop the powers that be from changing that).  This is especially true for numbers that are managed by people outside your organization.  You just don’t know when they might decide to add letter or special characters.

I get feedback from at least one person at each presentation that my blunders are way too obvious or that they aren’t serious mistakes.  As much as I see poor or inaccurate datatype selection, I have to politely disagree.  These are the number one mistakes I see.  They compromise data quality, lead to tragic data errors, even.  Storing numbers that in fact aren’t numbers as INTEGERS or other numeric datatypes is error prone, leads to nasty slow queries due to all the casting and table scans that may happen.  Eventually, those incorrect data values are going to come looking for you.  Usually after work hours, in production. If you’ve never seen them in the wild, then either you don’t get out enough of you’ve been blessed by working with highly competent data modelers and database designers.  And we all know how rare those are.

Thanks to David Maxwell (@dmmaxwell | blog) for the pointer to this WTF

The Cloud? It’s Just Like…

Data Governance and Stewardship Workshop, Chicago, 25 Jul – Join Me, We’ll Have a Blast

On behalf of Embarcadero Technologies, I’m leading a workshop on Data Governance and Stewardship in Chicago, IL on 25 July.   This event is free to attend; all you have to do is register.

We’ll be talking about:
Effective data governance and stewardship is a crucial component of every business. InfoAdvisors’ Principal Consultant, Karen Lopez, will share essential guidance on:

• Leveraging enterprise data as a corporate asset
• Tips, tricks, and traps to avoid when developing a data governance program
• Managing business expectations cost-effectively and time-efficiently

Karen will also reveal highlights on compliance and policy programs from recent discussions with data professionals in the US Federal Government and Industry organizations.

Karen is a Senior Project Manager and Architect at InfoAdvisors with more than twenty years of experience helping organizations implement large, multi-project programs. She specializes in taking practical approaches to systems development and has helped many IT departments choose appropriate methods and standards, based on the department’s culture, experience, and focus. Karen is the Moderator of InfoAdvisors/ITBoards.com IRM discussion groups, an online community of several thousand data management professionals and is also on the Board of Advisors for DAMA International and Zachman International. For more information, visit http://www.infoadvisors.com.

Data Governance Workshop Details

Date:        July 25, 2012
Time:        2-4pm
Location:  Discover Campus

2500 Lake Cook Road
RW2 Platinum Room
Riverwoods, IL 60015

Phone: 224-405-0900

I hope to make this interactive and fun.  There may also be space photos….

The Problem with Standards

I’ve been active in standards bodies, of some sort, for decades.  I think XKCD.com has nailed the biggest issue with standards:

Why can’t we all just get along, anyway?

She’s Cute, So it’s Okay…

…and, yes, this is quote from one of my former DBAs.

Wait…You don’t want to be our customer anymore?

Today I received yet another bill from Allstream, our telecommunications provider for our toll-free number.   It was only for $5, but there was a problem…I’d cancelled my account with them a couple of months ago.  Yet they were still sending a bill.  The call went like this, for the most part:

Me: This is my 3rd call* to cancel my services

Allstream: Your services are all disconnected

Me: But I’m still getting billed.

Allstream: Yes.

Me: Why am I still getting billed?

Allstream: That’s our customer charge**.

Me: What the f….?

Allstream: You didn’t cancel being a customer, so your account is still active even though your services aren’t.

Me: <redacted>

Allstream:…<pause>…..so did you want to cancel that?

Me: Yes, and I’m not going to pay this bill, either.

Allstream: Well, this one time*** we will waive that charge.

Me: You bet it’s one time.

Allstream: I’ve cancelled your customer account.  Is there anything else I can help you with?

Me: No.

Allstream: Thank you for doing business with Allstream.

Me: <click>

* When we tried to cancel the first time, Rob called to do the cancellation, but they had to confirm with me.  So he got me on the phone and I confirmed.  Then days later, while I was on the road, they left a message asking if I really wanted to cancel or did I want to upgrade to something else.  I didn’t return their call, so they cancelled the cancellation. And they continued to bill me. Seems they really didn’t want me to cancel.

**Customer charges are becoming the norm for services, at least here in Ontario.  Business tack on these charges so that they can advertise lower rates for their core services.  All the utilities and service companies do this.  It’s just a charge for the cost of you being a customer.  Because you are a liability to them, not an asset.  They must charge you for the right to charge you.  Or something like that.

*** One time fixes always get me going.  It must be fun to say that only this one time will the company fix their errors.  It really does make me want to run to find their competitor.

IT: The Enabler

I wonder what it’s like to work on project that support these inane business rules.  How do project managers, business analysts, data architects, DBAs, devs et al sit through meetings and write up these requirements?  How do people just sit back and implement these sorts of rules, rules that clearly work against the customers and ultimately the bottom line?  Do you write all your requirements in Comic Sans?  Do you get free sodas and bottled water? A foosball table? Sure, jobs are tight and the economy is bad, but how do you bring yourselves to do this without having to take a 20 minute hot shower when you get home every night?