Posted by klenwell on 09/29/07 04:42

On Sep 28, 5:39 pm, "Sanders Kaufman" <bu...@kaufman.net> wrote:
> "klenwell" <klenw...@gmail.com> wrote in message
>
> news:1191012645.721457.124010@22g2000hsm.googlegroups.com...
>
>
> 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.

Interesting approach. What parts of a user's account or identity
would you consider fixed or essential?

I always thought of a user's email as the binding factor as that's
usually how significant account changes get validated. And I assumed
that either the email address or the user name would have to remain
fixed to maintain a continuous record.

But I guess the primary integer key (what I term uid) would be the
account constant and any other variable could be changed as long as it
maintained an association with that.

By way of reference, if anyone's curious, here are the user tables for
drupal and wikimedia:

CREATE TABLE `users` (
`uid` int(10) unsigned NOT NULL default '0',
`name` varchar(60) NOT NULL default '',
`pass` varchar(32) NOT NULL default '',
`mail` varchar(64) default '',
`mode` tinyint(4) NOT NULL default '0',
`sort` tinyint(4) default '0',
`threshold` tinyint(4) default '0',
`theme` varchar(255) NOT NULL default '',
`signature` varchar(255) NOT NULL default '',
`created` int(11) NOT NULL default '0',
`access` int(11) NOT NULL default '0',
`login` int(11) NOT NULL default '0',
`status` tinyint(4) NOT NULL default '0',
`timezone` varchar(8) default NULL,
`language` varchar(12) NOT NULL default '',
`picture` varchar(255) NOT NULL default '',
`init` varchar(64) default '',
`data` longtext,
PRIMARY KEY (`uid`),
UNIQUE KEY `name` (`name`),
KEY `created` (`created`),
KEY `access` (`access`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user` (
`user_id` int(10) unsigned NOT NULL auto_increment,
`user_name` varchar(255) character set latin1 collate latin1_bin NOT
NULL default '',
`user_real_name` varchar(255) character set latin1 collate
latin1_bin NOT NULL default '',
`user_password` tinyblob NOT NULL,
`user_newpassword` tinyblob NOT NULL,
`user_newpass_time` varbinary(14) default NULL,
`user_email` tinytext NOT NULL,
`user_options` blob NOT NULL,
`user_touched` varbinary(14) NOT NULL default '',
`user_token` varbinary(32) NOT NULL default '',
`user_email_authenticated` varbinary(14) default NULL,
`user_email_token` varbinary(32) default NULL,
`user_email_token_expires` varbinary(14) default NULL,
`user_registration` varbinary(14) default NULL,
`user_editcount` int(11) default NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_name` (`user_name`),
KEY `user_email_token` (`user_email_token`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Tom

[Back to original message]