I am maintaining a database.
Generally speaking, it is medical database tracking treatments for patients. There are current patients, and well.... non-current patients. Patients generally only get treated once but records are kept, but occasional come back so they can't just be archived and forgotten.
My question is how in a database design sense, to "archive" a record into "non-current". I simple check box or calculation field seem to fragile. I would need to filter them out for most actions like bulk emailing and for any related portal etc. Seems like it could go wrong easy.
I simple copy of the patients table seems like poor database design to me. Having to keep all the fields the same into the future, and manage duplication between them.
In a general sense, is there a way to tackle this time of data management?