I fully agree with being flexible, but I find that not being able to set up views is extremely inflexible and causes great potential for denormalization of data, impacting data manageability. I haven't really restricted any particular DB, just necessary DB functionality - like views. AFAIK, MySQL is the only widely used DB product that cannot do views.
I don't refrain from using other DBs (in my day job we use MSSQL), but for my side projects (where cost is a huge factor) I prefer to use Postgres because I find it has more of the "commerical-level" functionality that my projects need compared to MySQL. (I did qualify my original statement by saying OpenSource DB needs) ;) -----Original Message----- From: Joe Barefoot [mailto:[EMAIL PROTECTED]] Sent: Tuesday, August 27, 2002 1:45 PM To: Struts Users Mailing List Subject: RE: user accounts question That's fine as long as your not selling your web app. as a product to a customer who wants, for instance, to use Oracle as their DB. :) Web app. *products* must aim for maximum flexibility with the DB because of customer issues like these. > -----Original Message----- > From: Jason Rosen [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, August 27, 2002 1:37 PM > To: 'Struts Users Mailing List' > Subject: RE: user accounts question > > > 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]> > > -- 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]>
