How to Handle Database for Application which Starts from Scratch Annually I am working on an application which will be used by an organisation annually, however each year they will be starting from a clean sheet. Which of the following two approaches (or any other approach) is most suitable:


Run the entire application from one database with a field in all relevant tables for the year it applies for. E.g. User("Mary", "Smith", "mary.smith@example.com", 2012) where 2012 shows that Mary's account is valid in 2012.
Have separate databases for each year and one main database which references which database is related to which year.
Honestly, I'd go with #1 with a twist in all but a rare circumstance.  The rare circumstance that would make me go with separate databases (or, more likely, one database with separate schemas) is if there are fundamental data type changes between years, and the data structure changes so often and completely that it really is a new database altogether.  That's probably not the case, though, because you're talking about a single application.  And because it's a single app, there's probably some common reference data that will remain static (or slowly-changing) through the years, and probably some functionality that they'll want, like the ability to replicate old users (so they don't have to keep typing in the values each year).

So instead, I'd go with one database, one set of tables, and an indicator telling to which year that data belongs.  Your example of a User seems a little odd to me, though.  If you're talking about application users, I'd only put them in once and if you need to break it down by year, have a User-Access table that has your user ID and the year as the two columns.  Then, your application knows only to allow that user access to the relevant year(s), without having to repeat data.

Here's the twist I mentioned above:  in addition to marking yearly data with a year indicator, you might have a second schema for archived data.  Partition each of the relevant tables by year and, as the year ends and you're getting ready for the next year, roll them off into the Archive schema.  The data would remain for historical and reporting purposes, while leaving your application free to handle the current year as though nothing had happened.

Archiving would be optional in this scenario, however.  If you have a lot of data and people rarely go back to look at it, it might make sense to move those off to archive tables, but even if not, you could still stick the old data on slower disks and with table partitioning by year, rarely touch those partitions even if they are in the same table.