Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking for different ways to keep track of both active and archived data so I can pro and con them.

The system:
I have a computer with a database on it. The database has several tables in it; one of which contains a list of users that can use the computer; and several tables for auditing (user 1 did this, user 2 did that, etc). This database is a slave of a master database in which a Content Management System is used to say, add a new user and see reports on what user did what.

Example:
As stated above, I have a table (lets call it users) that keeps track of all the users that are allowed to use the computer. As time goes by users will be added and removed. The problem is the audit tables keep track of a user ID so if the user is removed I lose the user information because the rows can't be joined. One idea I had was to use MySql's triggers so that if a user is added, an insert trigger is triggered and inserts a copy of the data to an 'archived' user table (lets call it users_archive). That way the computer can use users to determine if the user has permission to use it and reports can use users_archive for reports.

This seems like the easiest and most simple way to do it, but I can't find any other ways via google search to see if there are any other ways to do something like this.

share|improve this question

migrated from stackoverflow.com Apr 4 '13 at 1:33

This question came from our site for professional and enthusiast programmers.

1  
don't use triggers. they are a headache. maybe run a cronjob once a week or once a day if you want. –  nathan hayfield Apr 3 '13 at 21:37
    
That's why I'm asking for other ways to do something like this. I couldn't think of any other way but that doesn't mean there isn't an easier or better way to do it. –  anEveryDayGuy Apr 4 '13 at 15:29

2 Answers 2

Instead of deleting records from Users, why not simply mark the record as deleted with a boolean field. Then you can show only active users most of the time, while still joining on "deleted" users.

share|improve this answer
    
I had thought of that. The problem with going that way is that we have code that takes the database table's information and creates a web form on the CMS so that users can add/remove/change information in the database and this code is used for every table. So if someone wants to delete an uploaded picture, the form will display the picture along with 3 general buttons, reset, submit and delete. Since this was the first case we came across, delete does just that, it deletes the picture and the database entry. This same code is ran for users so I can't change how the delete works. –  anEveryDayGuy Apr 4 '13 at 15:21

The two options that spring to mind avoiding triggers are:

  • Flag the deleted rows as such with an extra field, and update all your existing code to deal with it. This is "simple" but will touch a lot of your code so can be a bug source. Delete is not delete, mearly "set is_achived flag".
  • Flag the deleted rows in the table, and have views for active and archived data. This avoids changing existing code as you can give the combined table a new name (users_all) and the view of active users (SELECT <fields> FROM users_all WHERE is_archived=0) the name of the old table. Code such as audit reports simple need to query users_all directly. You will need to run a few tests to make sure mysql's query planner handles this efficiently though - I've had it work well is MSSQL but don't use mysql myself so have no expectations one way or the other about it's QP's intelligence.
  • Separate tables for active and archived. The delete operation then becomes an insert into archived and a delete from active (be sure to wrap this in an explicit transaction so if there is an error half way through you don't lose records completely or end up duplicating them). For code that needs to see both subsets, either UNION directly in the views/procs or have a view that is a UNION of the two tables and use that. This is likely to be less efficient than the above for those code paths that do need to deal with both subsets, but will not affect other areas of the codebase so may be preferable. One key disadvantage with this option is not being able to use the two tables in FK relationships - you can get around this by adding a third table containing just the identifiers for each row, then all tables that contain links to users (including the active users and archived users tables) would use that as the FK source - the only extra work in your business logic as a result would be to create that row before creating the row in the users table.

Triggers can be very powerful, but you need to design them very carefully as they can be a performance problem if not, and of course silent things happening can be a problem when bugs to arise as it can be difficult to see everything that is going on at first glance. they can greatly simplify some processes though, creating easier to maintain structures as a result, and sometimes having a bit of structural logic in the DB is far more efficient than having the next layer up deal with it, so never discount them out of hand. In this instance though, I'd stick with one of the simple boolean flag options.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.