I conceptually agree with such an approach, especially if the number of users is big (although it's trivial to keep those two table synced in PostgreSQL for small amount of users). But the actual primary keys have to be some sort of synthetic keys (something like SERIAL or a generated key, guaranteed to be unique) rather then 'hand written', which might create problems down the track. For instance, user 'jack' decides that he's going to be 'tom' from now own, but his primary key is still 'jack'. Then another 'jack' comes along and you have a clash of primary keys, which makes it difficult for the database maintenance program (i.e. you have to resort to finding the next one up or something).
Is your user database so big that you can't use REFERENCES ... ON UPDATE/DELETE CASCADE to keep those things in sync? Bojan On Thu, 2002-03-07 at 01:25, John Gregg wrote: > I assumed increased flexibility + backwards compatibility would be welcome. > After all, Tomcat has to serve the needs of many different users. Some of > those users, including me, are working for clients who aren't willing to > spend the money on things like WebLogic or Oracle. Instead, we use Tomcat, > which works great, and databases like PostgreSQL and MySQL. As you've shown > with the views, as a PostgreSQL user, I don't absolutely NEED the patch I've > provided, but I consider it an improvement nonetheless because I value > flexible designs. I would expect the MySQL users to appreciate a solution > that works for everyone, not just everyone else. > > After this patch, I was considering cleaning up some of the exception > handling issues in JDBCRealm, but from now on I'll confine myself to > ordinary bug fixes and not design "improvements." > > john > > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > g]On Behalf Of Ignacio J. Ortega > Sent: Tuesday, March 05, 2002 1:43 PM > To: 'Tomcat Developers List'; '[EMAIL PROTECTED]' > Subject: RE: [PATCH] change JDBCRealm to add flexibility in table layout > > > ( Syntax aproximated maybe sql-92, more or less ) > > create table users > ( > user_id integer not null primary key, > user_name varchar(15) not null , > user_pass varchar(15) not null > ); > > > create table user_roles > ( > role_id integer not null primary key, > user_id integer not null, > role_name varchar(15) not null > ); > > > create view user_roles_jdbcrealm > as > select u.user_name,ur.role_name > from user_roles ur join users u > on (ur.user_id=u.user_id) > > > and use this view in jdbcrealm.. > > Please add what you need to be able to understand you.. > > Saludos , > Ignacio J. Ortega > > > -- > 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]>