We want to build a SQL-Database for a system to manage regulations in different countries for different years. The problem is that the regulations change every year for every country. Like for social insurance you need different data in 2015 than you needed in 2014 and we have companies that enter their data to connect with their social insurance informations for every year.

If I want to build my models on this data I would have to create a different model for every year with different attributes. I can't just use migrations, because I have to keep the historical data and information.

Right now I am researching for the right solution and I would like to know if there is a name for this problem to find some solutions?


If you use PostgreSQL probably http://pgxn.org/dist/temporal_tables/ can help you.


All depends on how many changes there are each year of depending by Country. You could build a generic system that allows you to enter it by country and by year in one table. Then another field could be FieldName. At that moment you can enter all data. Now for calculations you build a procedure and a separate formulas table. In this formula's table you enter the dynamic SQL statements. Is a bit strange in the beginning but we had a similar system to calculate weekly filmhires in several countries. Some had citytaxes, other had several author rights, all calculated on different ways. Even in some countries depending on what kind off a contract you had the formulas had to behave differently. But finally worked for 10 years without missing. Just adding the newer formulas each year. Ok, this had to be done by a SQL guy, but changing this towards a more natibe pseudo code would not be that difficult.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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