database archiving vs timeperiod based tables/fields

Question

I am working on an employee objectives web application.

Lead/Manager sets objectives for team members after discussing with them. This is an yearly/half-yearly/quarterly depending on appraisal cycle the organization follows.

Now question is is better approach to add time period based fields or archive previous quarter's/year's data. When a user want to see previous objectives (not so frequent activity), the archive that belongs to that date may be restored in some temp table and shown to employee.

Points to start with

archiving: reduces db size, results in simpler db queries, adds an overhead when someone tried to see old data.

time-period based field/tables: one or more extra joins in queries, previous data is treated similar to current data so no overhead in retrieving old data.

PS: it is not space cost, my point is if we can achieve some optimization in terms of performance, as this is a web app and at peak times all the employees in an organization will be looking/updating it. so removing time period makes my queries a lot simpler. Thanks

Answer 1

I would start off adding your time period fields and waiting until size becomes an issue. The kind of data you are describing does not sound like it is going to consume a lot of storage space.

Should it grow uncontrollably you can always look at the archive approach later - but the coding is going to take much longer than simply storing the relevant period with your data.

Answer 2

It seems to me that if you have the requirement that a user can look arbitrarily far back in the past, then you really must keep the data accessible.

This just won't be sustainable:

the archive that belongs to that date may be restored in some temp table and shown to employee.

My recommendation would be to periodically (read when absolutely necessary) move 'very old' data to another table for this purpose. Disk space is extremely cheap at this point, so keeping that data around is not nearly as expensive as implementing the system that can go back to an arbitrary time and restore an archive.

Answer 3

Assuming you're talking about data that changes over time, as opposed to logging-type data, then my preferred approach is to keep only the "latest" version of the data in your primary table(s), and to automatically copy the previous version of the data into a archive table. This archive table would mirror the primary, with the addition of versioned fields, such as timestamps. This archiving can be done with a trigger.

The main benefit that I see with this approach is that it doesn't compromise your database design. In particular, you don't have to worry about using composite keys that incorporate the version fields (in fact using time-based fields as keys may not even be permitted by your database).

If you need to go and look at the old data, you can run a select against the archive table and add version constraints to the query.