Archiving data by semester

Question

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?

Answer 1

This solution is certainly an acceptable solution, looking at your constraints, and the relatively small size of the database after 10 years.

However, the frequency of your archiving shows that the data is inherently time dependent. I'd really put the year and semester or date of validity part of the data. This would avoid the necessity to archive, and archival duplicates (i.e. several data with archived:true and same values, such as for example a student fails in one semester and takes the same course in the next semester).

If you compare to facebook: facebook perfectly manages a time line, and only core identifying data such as name, date of birth, etc.. is not time dependent.

Answer 2

Another approach might be to have a Semesters table. Your core tables that are in use now would take a SemesterID as a foreign key. Put the Active bit on the Semesters table (or query it by datetime comparison, but I'd probably go with the archive bit).

From there, you manage only the Semesters archive bit, and either:

1. Inner Join to "active semesters" on your main queries, or...
2. Create some base views to represent the current core tables, which themselves are inner joined on the active semester.

Point two above provides a bit of abstraction level so in your app/repository, you work with the views and feel like you're working with active data. Management is pretty easy all around.

(after all, the natural normalization of the data should eventually fall back to a Semester entity, and if it doesn't relate there, it probably doesn't have need to be identified as archived/active or not... I've taken this approach in many forms over the years and have found it to work very well)

Answer 3

We had a similar problem with millions of rows per day. I will give an example with just one table and can be done similarly for others.

Say the table was orders. orders would have the data for current table. It was always archived on a saturday and the data would be moved to a weekly table e.g orders_20160409 would have orders data for the week of April 9 2016. An entry was made into an orders_map

order_map

date table_name server_name database_name

20160409 orders_20160409 server1 db1

If the data was big , sometimes the process intelligently would even divide the data and put it in two different tables based on certain rules and make multiple entries. e.g

date table_name server_name database_name

20160409 orders_20160409 server1 db1

20160409 orders_20160409_1 server2 db2

In our database , there were sometimes about 100 entries for a single table due to volume of data.

We never queried the database directly. We used an API which would accept tablee name ( orders) and weekly date ( 20160409).

Internally this query function would go to order_map table , find the servers,database and table name and fire the queries in parallel .

This way the front end had no idea what was done on the backend and data could be restructured on the backend if necessary ( sometimes data was moved from one server to an other server by dba due to performance issues ).

The only thing you will have to do is write and schedule an automate script which runs every weekend/semester and archives the data. Oh and of course query through an API ( maybe web service ) after building the API.

This might be over engineering but just to let you know an approach for huge data volumes.