[thelist] Asking for email address twice

[Luther, Ron]

The estimable Mr. r937 asked:

> don't make email address the primary key in your valid user table.]
>>whyever not?  because it might change? what's wrong with updating a primary key?


Hi Rudy,

Rats.  Once again, my words may not have precisely captured my intent!  You're quite right.  I don't have a problem with updating primary keys.  But then again, I also happen to know that you and Matt are both familiar with and comfortable using composite and compound primary keys.

So let me try again.  I was attempting to recommend against the use of email address as a simple unique key in systems where folks intend to use that email address as-if-it-were an 'account number' or 'global id'.  I have two reasons why I think that's a bad idea, but mostly I recommend against it because I like to keep things simple!  ;-)

The first reason is non-technical.  I've gone through several corporate mergers where my work email address has been forcibly changed from 'Ron at smallcompany.com' to 'RonNumberSix at ReallyBigCompany.com'.  [1]  Those changes generally happen over a weekend, and with no advance notice.  Because of that, I actually _have_ had to argue on the phone with helpful customer service representatives telling me that they would be more than happy to change my email address in their records for me ... and "ALL-I-Have-To-Do" is send them an email from my old 'Ron at smallcompany.com' account requesting that change.  (So they can be sure it's really truly me and not some lowlife identify thief.  You understand.)

@#&*!  

"Sorry Sir, that's corporate policy.  Is there anything else I can help you with?"

Now I can't *prove* that those folks were using email address as an account number.  Maybe they weren't.  It smells that way to me, but I could be misspeaking here.  These kinds of helpful 'corporate security policies' may or may not have technological underpinnings.  But on the other hand, storage is cheap.  There really isn't any reason not to set up separate Account_NBR, and Email_Addr_1 thru Email_Addr_n fields.  My thinking was that the separation of account_id from email_address *should* make it simpler to design systems that prevent issues like the above from becoming end user usability problems.  It's no guarantee, but I think it's a step in the right direction.

[Granted, in a B2B setting you may sometimes need to also construct and maintain a hierarchy of account_ids in order to properly relate and aggregate disparate divisions or subsidiaries of the same customer ... and when one of your customer companies sells one of their subsidiaries to another company that is also a customer of yours ... well ... it can be great good fun keeping your historical records in order!  {Particularly when that subsidiary has a multi-year purchase contract with you and those years cross this change boundary!  Unbounded loveliness!} ... But that's peeking ahead.]



Reason #2.  Scope.  When you have complete end-to-end control over the system architecture and implementation - sure - you can set it up any way you want and make it work.  Absolut-a-tively.  I agree 103.5%.  But you don't always have that kind of control.  Sometimes your MySQL web storefront is feeding an SAP order management system that is driving data through a JD Edwards MRP engine that is outbounding information to Peoplesoft Financials and a hundred downstream reporting systems using a multitude of technologies and archiving strategies.  In that situation the update cascade doesn't quite reach into those archived mag tapes three or four system hops removed from your frontend.

... and then Accounting (or Legal) (or Regulatory) (or Customs) (or Homeland Security) (etc) goes to one of those downstream system and demands x years of historical data on some customer ...

Sorry.  I'm chicken.  I'm not going to paint a target on my chest by doinking with changes to customer account id values in the frontend system (or anywhere else in the chain).  Especially without knowing how it's going to play through the rest of the world.  Nope.  Not worth it.  So yes, I will update a primary key.  But no, I'm going to be extremely wary and conservative futzing about with customer account ids and not change them at all unless it's both absolutely necessary and very very well documented and signed off on. [Mergers are one situation where sometimes you need to do this ... But you still need to be weawwy weawwy carefuw.]



We have some seasoned veterans here on thelist, but we also have some rookies.  Someone setting up their first 'customer' table and establishing a 'unique key field' could easily be tempted to use email address.  ... Hey!  An email address is unique, right?  I'll use that!  What could possibly go wrong?  ... I probably should have been a bit more verbose earlier, but my intent was to recommend not using email address to uniquely identify your customers.  Whew!  Hope that's clear now.  ;-)



Cheers,
RonL.

(Who is still a member of and, once in a while, receives email threads from a technical Yahoo group {because of some crazy forwarding rules somewhere evidently} ... but can no longer contribute to the discussion in those threads - or even unsubscribe - because his email address changed. [Don'tcha love non-moderated discussion lists?])

[1] More a nod to The Prisoner than Battlestar Galactica actually.