I'm doing DB for an electrical contractor who has 50 guys who go out in the field every day, each one with multiple work orders. I anticipate that the client might be adding lots of records very quickly, so we will need to come up with a business rule for archiving old records.
Question: Is it best to export old records maybe into an external DB when archiving, or is it best to maybe put just a YES/NO flag for IsArchived. If I have this flag only and put in my queries to not include records where that flag is true, does this make my query searches faster, or will the DB only stay faster if I take the records out of the primary back end ?