You mentioned that you needed to use username for Tomcat's JDBC realm, but you could use the artificial key user_id in table user_role_xref and create a view for Tomcat to use that joins tables users and user_role_xref on user_id.
I prefer using artificial keys for tables that may have the possibility of using natural keys because I like the flexibility for future requirements where the natural keys may be abolished. This way you don't have to go through your DB and change all your keys and referential integrity, etc. I have worked on previous a project where Social Security Number was used as a natural key, and then company policy mandated that SSN not be used to identify employees - had a large project of revamping the DB to use new artificial keys. If we had used them from the beginning, would not have been so bad. But I am not sure of your requirements. -----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]>
