Fiscal year handling strategies in database design
- by Sapphire
By fiscal year I mean all the data in the database (in all tables) that occurred in the particular year. Lets say that we are building an application that allows user to choose from different years.
What way of implementing this would you prefer, and why:
Separate fiscal year data based on multiple separate database instances (for example, on every fiscal year start you could create a new instance with no data)
Have everything in one database, but with logic that automatically separates records from different years.
Personally, I have "seen" both methods, and I would choose the second. The only argument I can think of for the first method is to have less records in case that these are really big databases - but still, you could "archive" old records by joining them in summaries or by some other way. What do you think?
