[thelist] PHP/MySQL: Creating Application Users

[jsWalter]

This is what I'm using in an Auth Class I'm in the middle of.

It uses email as the user ID, tracks user access and has email back id
verification and confirmation.

User data is kept elsewhere, and the userid is generated on that other
table, not here.

CREATE TABLE `authUser` (
  `userid` int(11) NOT NULL '',
  `password` varchar(32) NOT NULL default '',
  `show_email` int(2) unsigned NOT NULL default '0',
  `email_updates` int(2) unsigned NOT NULL default '0',
  `status` varchar(10) NOT NULL default '',
  `signupdate` date NOT NULL default '0000-00-00',
  `lastlogin` date default NULL,
  `logincount` int(11) NOT NULL default '0',
  `confirmkey` varchar(25) NOT NULL default '',
  PRIMARY KEY  (`userid`,`userid`),
  KEY `email` (`email`)
) TYPE=INNODB;

A second table joins the users to the various "teams", and an individual
user can have a different level assigned to them for each team they are
on.

A user can also be "inactive" in one group and "active" in another,
that's what "status" s for.

1 user -> many teams/groups

CREATE TABLE `authUserTeam` (
  `userid` int(11) NOT NULL '',
  `team_id` int(4) NOT NULL '',
  `team_lvl` int(4) NOT NULL '0',
  `status` int(4) NOT NULL default '0'
  PRIMARY KEY  (`userid`),
) TYPE=INNODB;

Hope this helps a bit.

Walter

PS: If any DB gurus out there can tell me where I've gone astray on
this, please do.