|
|
Posted by Jerry Stuckle on 09/29/07 12:57
klenwell wrote:
> 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
>
Tom,
Yes, and I've got similar tables on other systems.
I keep virtually all information related to the user in the user table.
That's what it's there for. Sure, it means a bunch of columns. But
the row is still typically much less than 1K long. And databases handle
this quite well.
It's different if you've got a large amount of data associated with a
user, i.e. a 20K avatar, 100K biography, etc. Those I find work better
in a separate table. But for the run-of-the-mill stuff, one table
works better.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|