The problem is similar to this question, but not a duplicate. What I'm looking for is a better solution than my current one.
I'm building a web application for a department in my university, and due to how college works, things need to be archived every semester. By archive, I mean marked as not 'current' so that anything that occurred in previous semesters is out of the way, but accessible via something like /projects/archive
if needed.
At the moment, I have a simple boolean archived column for every table that needs to be archived (users, projects, events, etc.). Before the new semester starts, I run a script that flips the switch for all rows where archived = FALSE
. It's not the worst approach.
The problem is that as more and more tables are created, my DRY sense starts tingling, saying an archived column for every table is ridiculous. The script grows (by one line) for every table that needs an archived column.
Fortunately I'm using the Rails framework, so the archive script looks like:
User.where(role: [User.roles[:faculty], User.roles[:student]], archived: false).update_all(archived: true)
Course.where(archived: false).update_all(archived: true)
Enrollment.where(archived: false).update_all(archived: true)
Project.where(archived: false).update_all(archived: true)
Event.where(archived: false).update_all(archived: true)
It only takes a few seconds to run (the tables are pretty small), and only needs to be run 4 times per year. I should note that this is not enterprise-class data I'm dealing with. The largest table is enrollment which only has about 5000 rows added per semester (that's ~125,000 rows after 10 years).
Is this an okay solution, or is there a better one? If, for example, this were Facebook-level data I were dealing with, should the approach be different?