NYCPHP Meetup

[Gary Mort]

After a number of....odd fights with user tables I'm coming the 
conclusion that Mitch is right.

The typical user table starts with 3 bits of information:
A userid
A username
An email address.
A one way encrypted password

Maybe....MAYBE some user status info...

And it demands that the first three of those fields be unique.  If you 
don't make those fields unique, Bad Things will happen when other apps 
that assume their unique find out their not.

If you have 3 fields, userid, username, and email address - and they 
each need to be unique....well then, that is completely redundant and 
there is no need for it.

Get rid of 2 of them.  And the one that makes the most sense to keep is 
username.

Oh, and get rid of that password as well.  It doesn't need to be in that 
table and because it is assumed to be there, I have to do some really 
hackish things when one wants to authenticate in some other way.


"But, but but.....  what if they want to receive a password reset 
request?" - take that email address and put it in a contacts table.  Tie 
it to their username and flag it as the primary.

What if they want to change their username?
Keep an aka table and timestamp it.  Everytime a username is set, mark 
it in the aka table with a start time and a null end time.  Get in the 
habit of checking the also known's when checking user data.   Oh, and to 
make things not take forever, when a user updates their username, set 
their new name immediately and fire off a queue process to check through 
the database and clean up existing data.

What?  No password?  - yes, no password.  Instead, have an "access key" 
table tied to....you guessed it, username!  An access key table can 
specify the username, the process to use to verify access, and any 
supplemental information you may need.  This means you can handle API 
keys, REST keys, user passwords, etc all from one set of data.    I've 
had to fight a number of times to prevent an existing system from using 
the default password/username method of logging users on because a 
client wanted a different authentication method.   And it's always 
frustrating not knowing if some /other/ app that will be installed might 
bypass the API and check directly.  By moving this data out of the user 
table, you make it so everyone has to explicitly check for 
authentication methods.


Lastly, in there is one problem with getting rid of userid's.   When a 
dumb American is trying to troubleshoot a user problem, sometimes it is 
very difficult over the phone to get correct spelling.   But that is 
easily handled by the aka table.   Assign everyone an identifier in that 
table for those times when you need something short and easy.  Just make 
sure if you do that to make it something easily communicated over the phone.