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.
_______________________________________________
New York PHP Users Group Community Talk Mailing List
http://lists.nyphp.org/mailman/listinfo/talk
http://www.nyphp.org/Show-Participation
