You may have some other business need for the user_id that I'm not grasping (like you never delete rows, only deactivate them, thus there can be only one active username, but it is not the PK ), but barring that, I see no reason to retain the user_id field at all. If the username is required to be unique, and the row is deleted when the user is deactivated, it serves as a natural primary key. Using sequence numbers like user_id when a natural PK exists is superfluous and potentially confusing to developers who will be using the tables--I found that out rather quickly at my last job. :)
peace, Joe > -----Original Message----- > From: Dan Payne [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, August 27, 2002 11:18 AM > To: [EMAIL PROTECTED] > Subject: user accounts question > > > All, > > Love using struts and enjoy this informative mailing list. > This email is a > bit off topic but alas, this is the list I subscribe to and I > figure I'll > definitely get an informed response, which is what I'm looking for. > > Designing a database for a client's site. The four tables in > question are > as follows: > > mysql> describe users; > +---------------+--------------+------+-----+------------+---- > ------------+ > | Field | Type | Null | Key | Default | > Extra | > +---------------+--------------+------+-----+------------+---- > ------------+ > | user_id | int(11) | | PRI | NULL | > auto_increment | > | username | varchar(20) | | | | > | > | email | varchar(100) | | | | > | > | name | varchar(50) | | | | > | > | authenticated | date | | | 0000-00-00 | > | > | password | blob | | | | > | > +---------------+--------------+------+-----+------------+---- > ------------+ > > mysql> describe user_role_xref; > +----------+-------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +----------+-------------+------+-----+---------+-------+ > | username | varchar(20) | | PRI | | | > | role | varchar(50) | | PRI | | | > +----------+-------------+------+-----+---------+-------+ > > mysql> describe user_auth_xref; > +-----------+-------------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +-----------+-------------+------+-----+---------+-------+ > | user_id | int(11) | | PRI | 0 | | > | auth_code | varchar(20) | | | | | > +-----------+-------------+------+-----+---------+-------+ > > mysql> describe user_type_xref; > +--------------+---------+------+-----+---------+-------+ > | Field | Type | Null | Key | Default | Extra | > +--------------+---------+------+-----+---------+-------+ > | user_id | int(11) | | | 0 | | > | user_type_id | int(11) | | | 0 | | > +--------------+---------+------+-----+---------+-------+ > > Here's the question: > Should I still use the user_id? Or, since the username will > need to be > unique anyways, should I use the username as the primary key? > If I do, > which seems to make sense, then my user_auth_xref and > user_type_xref tables > might get quite larger in size. Is this really an issue? > Especially b/c if > I forgo the user_id then things will definitely be a bit > easier to code. > Anybody run into anything like this? The user_role_xref > table requires I > use the uesrname instead of the user_id as it's for Tomcat's > JDBC realm for > user authentication. > > Thanks. > > -Dan > > > -- > To unsubscribe, e-mail: > <mailto:[EMAIL PROTECTED]> > For additional commands, e-mail: > <mailto:[EMAIL PROTECTED]> > > -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
