In a HR database i need to do statistics for each year, is it a good practice to create a database for each year?
It really depends how your HR/, Payroll application presenting it to the users. I can imagine that can be done in completely ergonomic way, so basically it would be only integration problem for some kind of external analytics. And it probably may have advantage, past year databases may be put on read only mode, and that current year databases would be smaller than big, aggregate database.. But saying this, I would like to say, I've never seen any good system for such kind of database model. Some random thoughts:
- It is troublesome in every day use, as it is quite common some operations have to change databases records even in the past, at least for short time during years change
- It is very hard to use such systems in external analytics, based on normal tools: instead writing queries on database with time periods as filters, you have to manage several database connections and aggregate statistical data outside, probably using temporary tables or even additional data warehouse
- Some reports may be required, for time periods which not agree with years change. For example for accounting purposes.
- Exporting duch data into other systems, for example when changing HR platform to other products, is nightmare in such scenario.
- Upgrade. Usually is performed on present database. Will you upgrade historical ones as well? If yes - what I advantage of having it separately? If not - you will end with several incompatible database schemas.
- Maintenance. It may be a lot of effort put to maintain several databases instead of only one. It is known issue.