Good point. If the tables are going to be involved in heavy transaction processing, auditing, data warehousing/analysis processes and the like, retaining the sequence number user_id might be a good idea. This doesn't seem to be a very data-intensive application you're working on though.
joe > -----Original Message----- > From: Ian Vellosa [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, August 27, 2002 11:37 AM > To: Struts Users Mailing List > Subject: RE: user accounts question > > > However, databases are generally a lot more efficient at > joining tables on > integer columns than they are with VarChars. So once the user has been > looked up in the database once using the VarChar all the > joins can be done > with the integer IDs easing the load on the database. > > -----Original Message----- > From: Joe Barefoot [mailto:[EMAIL PROTECTED]] > Sent: 27 August 2002 20:23 > To: Struts Users Mailing List > Subject: RE: user accounts question > > > 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]> > > > > -- > 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]>
