Database Design Concept
Posted on 2016-10-14
We have a 4 separate databases for 4 consecutive years (2013, 2014, 2015, 2016). The customers are generally the same, but with more added each year and are identified by a unique customer number. Data changes from year to year.
For example, the unique ID in all 4 data bases is 22 which pulls up Jane. In 2013 she wasn't married, in 2014 she was and in 2015 she had a different phone number and finally there were no changes form 2015 to 2016.
Year ID Name Phone
2013 22 Jane Doe 212-555-1212
2014 22 Jane Smith 212-555-1212
2015 22 Jane Smith 646-555-1212
2016 22 Jane Smith 646-555-1212
The goal is to store all data indefinitely and be able to search the history.
I'm guessing the best thing to do is merge the databases together into one? If that's the case, what is the best way to design the database? I'm open to other ideas.