Why not have the application check for existing usernames, if one exists,present the info the admin user and ask if this is the user they are trying to create, if yes, then change the active flag, if not, tell them they need to choose a different username. That way your auditing and your unique indexing is preserved.
Andrew Hazen -----Original Message----- From: Ben Holness [mailto:[EMAIL PROTECTED]] Sent: Sunday, May 26, 2002 3:48 PM To: [EMAIL PROTECTED] Subject: Indexing Question Hi all, I have developed a system that lets people send messages to each other, with a MySQL database and PHP. Among the tables that exist, one is called "Users" and contains information such as Username, password (md5'd of course), email address etc. This table has a primary key "UserID" that is auto-incremented. When I created the table, I also indexed username, to ensure that no two users had the same username. I should also point out that there are two types of users, admin and normal. Admin user are able to add, modify and delete users that "belong" to them. A little further down the line I have realised that I need to extend my auditing capabilities. Currently, if a message is sent and then a user is deleted, there is no way to tell who sent the message. I am looking for advice on how to manage this, but here is what I have come up with: Add a field called "Active", default value=1. When a user is deleted, the value of "Active" is set to 0. When a user logs in, as well as checking the user name and password, the system checks that the user is active. Works fine, but it means that if an admin user creates a user "Ben" then deletes that user, they cannot re-create the user because "Username" is indexed. Because the users still exist in the database, auditing is solved. 1. Does this seem like a good way to do it (the only other alternative that I could think of was to create a "Deleted Users" table that the rows are moved to, but this seemed to have too many cons compared to the other idea) 2. Assuming that this is a sensible approach to take, how do I un-index the Username field? I know how to alter a table to make it unique, but not how to undo it :) Cheers, Ben --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
