Phone number and wife haven’t changed in 11 years


There’s a sporting goods store that I’ve visited on occasion over the last 11 years. It’s a membership cooperative, which mails a percentage rebate check back to customers annually. I signed up as a member 11 years ago, which meant yet another card to keep in my wallet. For convenience, the store could recall my membership via my phone number, so the card eventually lost its wallet position.


 


Over the years, outdoor recreation lost its priority to parenthood, so the store visits and the purchase volume was quite low. On those rare occasions, upon request for my membership number, I’d give my phone number. At some point the search for my membership failed, but I couldn’t be bothered to pursue the issue for the $10 annual purchase.


 


A few weeks ago, while preparing to bring my family for a ski trip, there was a fairly large purchase and I pursued the matter. I went to customer service and gave information like my address and phone number, wife’s name, etc. I have had only one address, one phone number, and one wife for the 11 years I’ve lived in Washington, so I thought any database program worth its salt would succeed.  I’ve only visited their one store (it moved once in 11 years), but I’ve never purchased items in other store branches. In addition, our last name isn’t the most common: surely my account could be found for the several hundred dollars I was spending there.


 


To further my chagrin, my wife had gone to the store the week before and purchased something. The sales clerk then apparently somehow found our membership account, confirming its existence.


 


After a few minutes of failure, another sales person, apparently with more experience, offered her assistance. She asked my phone number yet again, typed in a few keys, and instantly found my record!


 


I asked what her secret was: she used my same phone number but with the older area code: The entry had the 206 area code, but that changed to 425 on September 1, 2000. In this case, evidently experience does help: about 6 years of it! I asked her to change my phone number area code, so this problem won’t occur again for a while.


 


In any case, it seems to me that searching for my customer record via various attributes should have been less painful.

Comments (13)

  1. Travis Owens says:

    That’s definetly bad maintance on their part, assuming the store is immediately near the area code you live in (why else would you be going there, right?)

    When the area code changed, they should have done a database wide change to fix the area code issue.

    The most likely reason is their database is poorly normalized and phone number is a text or numeric field, so making a change to everybody’s area code was near impossible (without fancy regex or replacemend requiring something more than a simple SQL statement).

    Proper 1st Normal Form would have told them in 111-222-3333 that each one should have been a seperate column. That way when the phone company makes a change (it does happen now and then) everybody could be fixed with a SQL query. Arguably, most people will believe that these fields should be foreign keys but I should point out that more often than not, the phone company will create a new area code for some people and only change those people. It will be much easier to perform an UPDATE on the database by using a WHERE clause on the exchange (222).

    This is a perfect example where taking first norm form too literally isn’t a good idea, but following it loosely is the best method. Even if it’s not a big deal, from an administration view, it doesn’t make a big difference if you have to change 1 row (via a foreign key) vs changing 10,000 rows via a single UPDATE statement, even though purists will turn up their noses to UPDATE queries that affect massive amount of rows.

  2. David Fung says:

    I think we need a smart salesperson to get the job done! 🙂

  3. Brian says:

    I think this a difficult problem. For example, My wife ( then fiance) and best friend’s family lived in a in relatively close proximity to each other (30 miles). My wife family lives in a small NC town population maybe 900 while my friend’s family lives in a Greenville NC (a booming metropolis) by comparison. Both places at one time shared the same area code lets say (123). The phone company decided to split the area codes with one remaining what was before and the other assigned a new area code. To the naked eye this wasnt some easy split (i.e. Town A you are now area code 123 and Town B you are now area code 456). It was more like

    – Town B and Parts 1,2 and 3 of Town A you are still Area code 123.

    – The rest of Town A ( Parts 4,5,6,7) is now 456.

    While normalizing this info might help in retrieval. Unless we have data on how the phone company decides to carve up the area code already stored in our database ( In my case zip code wont work either) Im not sure what can be done .Offering an advanced fuzzy search (i.e. look for phone number like 123-4567) might work but would potentally be costly (resource wise).

  4. Brian says:

    No one should develop an application with only one way to search.An application with multiple search methodologies is the best option (i.e. Last name, First Name, Phone number, Street and Town or some other slowly changing criteria). Sales people who know how to use said application are important also. I occasionaly have people say to me months after I build a search form. "Wow I didnt know you could search like that!" To which I reply .’ The drop-down list on the form shows all the fields that you can search on, the email I sent out had screen shots and a couple good examples, the ‘how to search’ directions are in the help file , and the new tips forms ( using semi transparent forms) list ‘changing search criteria’ as one of the tips when you start up the search form ‘. Either way Im working on making the search forms more intuitive and generic.

  5. Tamar says:

    In fact, when the phone company splits area codes, generally there’s a list of exchanges (the first three digits of the phone number) that are changing and exchanges that are not. In my experience, they publish that list, so you can use that to drive the update.

  6. JAPHspam says:

    I just like spam! I’m collocting junk email…

  7. JAPHspam says:

    I just like spam! I’m collocting junk email…

  8. JAPHspam says:

    I just like spam! I’m collocting junk email…

  9. shajahan says:

    Dear sir,

    My telephone number is 965-924-4809, I want to make this number as numeric numebr.I am waiting for your king advise.

    Thanks & Regards

    shajahan

  10. Calvin_Hsia says:

    shajahan:

    To see what words your phone number represents,

    see http://search.msn.com/results.aspx?q=calvin+hsia+phone+number&FORM=QBRE

    In particular, there’s a link to a program that you can run that does this here: http://blogs.msdn.com/calvin_hsia/archive/2004/08/03/207286.aspx