I agree to not using MySQL until it can do views - I use PostgreSQL right now for my OpenSource DB needs.
-----Original Message----- From: Craig R. McClanahan [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 11:54 AM To: Struts Users Mailing List Subject: RE: user accounts question On Tue, 27 Aug 2002, Joe Barefoot wrote: > Date: Tue, 27 Aug 2002 11:22:39 -0700 > From: Joe Barefoot <[EMAIL PROTECTED]> > Reply-To: Struts Users Mailing List <[EMAIL PROTECTED]> > To: Struts Users Mailing List <[EMAIL PROTECTED]> > 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. :) > One traditional use case for using sequence-number things as PKs, instead of the "natural" value, is that it gives you the option to change the "natural" value (username in this case), just like any other data property, without screwing up the data relationships in the database. Even if you don't think you'll ever need to, it's nice to be able to. Regarding the Tomcat restriction -- if you used a database that supported views (such as Postgres) instead of MySQL you wouldn't need to worry about this kind of thing - you could create a view that did a join of users and users_roles (based on the shared user_id). MySQL has many cool features ... but lack of views is a critical missing piece for me, so I won't ever use it. > peace, > Joe > Craig > > -----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]>
