On Tue, 27 Aug 2002, Joe Barefoot wrote:
> Date: Tue, 27 Aug 2002 11:57:14 -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
>
> Sure thing Craig, using seq. number is fine so long as EVERYONE is clear
> that it's the PK and it's used everywhere. Half the time you'll end up
> with people using the natural key anyway when they write queries, since
> they know/assume it's supposed to be unique. If you have your data
> house in order, this is not a problem...but at my last gig, we had this
> exact situation, and half the queries joined on the seq. number, and the
> other half on the natural key. Really annoying. It really amazes me
> how we developers can be so clever yet still do such stupid things
> sometimes. :)
>
Avoiding problems also takes a clueful DBA that sets up appropriate
integrity constraints for you, as well as creating per-purpose views that
are tuned (by the DBA) to meet exactly your needs.
Oh wait ... this guy is using MySQL ... oh well ...
(Of course, letting Java developers design database schemas the results
are likely to be as bad as letting Java developers design HTML pages --
both realms are better handled by the experts :-)
>
> peace,
> Joe
>
Craig
> > -----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]>
>
>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
