Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

  1. 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.

  2. Have separate databases for each year and one main database which references which database is related to which year.

share|improve this question

2 Answers 2

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.

share|improve this answer
1  
Kevin got it - keep one database for your app. Some day, someone will forget to create the new year's database in advance (I've seen it happen). –  Alain Collins Nov 4 '12 at 6:49

What you need is a database with design in to preservation historical data. First you should ask the users to ensure the structure of the tables are the same from year 1 to year 2, ...

If majority of the tables have the same structures, then you can rely on one database to be utilized to keep the data for different years. You need to investigate if a table needs to preserve the data from year to year, (there might be tables that it is not the case, business users can answer this questions). If the data in a table needs to be preserved, you need to add two columns to the table, one showing the Effective From Date and the other showing the Effective To Date.

The PK of the table needs to be a surrogate key (which is an identity column). It is different from natural key. Then using these three columns you can implement history preservation on the table.

For example look at the balance of this account in a Balance table:

Create Table Balance (

Id int not null Primary Key,

AccountId int not null,

Balance decimal(15,2) not null,

EffectiveFromDate Date not null,

EffectiveToDate Date not null

)

In this table you would have:

1, 100, 1890, 2009-01-01, 2009-12-30

2, 100, 25000, 2010-01-01, 2010-12-30

3, 100, 465, 2011-01-01, 2011-12-30

...

By querying the table above using the effective dates, you can find out what was the balance of the account for each year. You don't have to have separate database for each year :)

It is a vast area and topic, but you can have a look at the links below to start:

http://stackoverflow.com/questions/3874199/how-to-store-historical-data

http://www.drdobbs.com/database/table-patterns-changing-data/184406340?nomobile=1

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.