Re: Archiving data in a database

At 10:20 AM 23/11/2000, Sanders, Nicholas wrote:

Hi, I'm designing a staff database and I've got about 20 tables some of them with 1 to many relationships i.e A member of staff can have multiple email addresses. What I'm trying to work out is what to do when I delete a member of staff I don't want to loose the data so how do I archive it. Should I create a replica of all the tables, or one table for each member of staff deleted? Any Ideas ?

I guess you could impliment a field in the main table, setting members of staff to either active or inactive. In any of your scripts to list staff members, add in the ability to only display entries that are set to active. That way, all the previous staff members records are not lost, and there is no need to replicate another table. Just only show active members in the normal lists, and you can also then make another display script showing all past members of staff and their details (or modify your current scripts to show either/or. I use this on a database I wrote for my dirtbike riding club. Any members who join, they are added to the database instantly and their status is automatically set to Pending. They dont display in the Members list until someone from the site admin comes along and approves the record. Once its approved, the entry is set to Active and its instantly in the full list. Then when someone leaves, the site admin sets their account to Closed and they are out of the list again (but still in the table with all their details) I dunno if this is an efficient way of doing it, but it works for me. Chris --

      Chris Aitken - Webmaster/Database Designer - IDEAL Internet

email: chris@ideal.net.au phone: +61 2 4628 8888 fax: +61 2 4628 8890

            --------------------------------------------

      Unix -- because a computer's a terrible thing to waste!