I am a little confused as to what the “best practice” is for archiving records, the way I see it there are two options:

A. Two tables one for active and one for archived records
B. One table with yes/no field to indicate if its archived or active

I choose to use two tables and I am not sure if it was the right choice, here are my reasons:
*The need by the user to view archived data is minimal
*In a multi user environment I figured the smaller the active table was the better the response time (I am using a split database with FE loaded on all users machines)


My active table has about 400 records with about 100 dropping off each week with 100 more being added.

I am interested in other opinions as to if this is a good approach?

Thanks

Both ways can work, depends on the application. Now, if you are going to take the data out of the main table, it is actually better to archive it to another DB. It can be a linked table, but outside the current DB. If the data is not large enough to worry yourself over, leaving it in the current table with a flag is fine also. We have found with large amounts of data, a different DB seems to help performance rather the same Db just a different table.
Just my 2 cents worth

FoFa Thanks for your reply.