I have population data in RDBMS. The dimensions are education, Age, nationality, and occupation etc.
For records till 2015, the occupation data is divided in 3 levels say group (g1), subgroup (g2), major group (g3). For records post 2015, the occupation data is divided in 4 levels say group (g1), subgroup (g2), major group (g3), and minor group (g4).
Challenge
Occupation was earlier divided in 3 groups whereas now it i divided into 4 groups. Some elements of old g1 are merged, some new entries are added in g1, similarly some of the old (old) g2 members are now split and for some of them their parent (new) g1s are also changed, g3s are further split into g4...so in nut shell, data has changed their level or even parent.
How do we model this kind of data?
1. Should there be a bridge table so that old records (prior to 2015) can be compared to new records (post 2015)? Will it be a bridge table of dimension?
2. We should have a single fact for data before and after 2015? Or should there be 2 different fact tables for these data should be created?
