Re: Table schema for user login system?

"klenwell" <klenwell@xxxxxxxxx> wrote in message
news:1191012645.721457.124010@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I'm in the process of refactoring the php code base I've amassed over
the last few years into an object-oriented framework. I'm about to
start in on the authentication/login extension and I've been thinking
about different approaches to the mysql table schema that stores basic
user login information. At present, user authentication is keyed to a
table with the following columns:

TABLE: basic_user
uid - int [primary]
handle - varchar [unique]*
email - varchar [unique]
password - varchar
access_lvl - tinyint
acct_status - tinyint
confirm_key - varchar
confirmed_timestamp - int
created_timestamp - int

* handle (user name) is optional -- email address alternatively can be
used as the handle

My strategy is to keep this table as minimal as possible and define it
as a kind of constant or standard for the framework. Then, other
tables can be created on a project-by-project basis to link to this
table and expand a user's information, if necessary. (Two examples
would be a profile table for expanding user profile info and an
activity log table to track user activity.)

I was interested in hearing some of the views of others who have
tackled this issue and how you've addressed it. Are there other
columns you like to include in your basic user table? What's your
table schema like?

I went up and down that development path for years before I realized that,
as you mentioned, the login accounts table should be minimal... more minimal
than you described, certainly.

I try not to make it any bigger than userid and password - putting all that
other stuff into linked tables.
That way, later, if you cange the other stuff, you don't have to mess with
the actual login table.


.