Since I been working implementing some triggers in my system recently this suggestion first came into my mind:
create a delete trigger for the user table. Every time a user it deleted, the trigger fires and could for example moves this information to a history table, with date of deletion, etc. This table could then be scanned in case no user was found. > -----Original Message----- > From: Ben Holness [mailto:[EMAIL PROTECTED]] > Sent: Sunday, May 26, 2002 9: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
