What is the best database design for managing historical information? [closed]
- by Emmad Kareem
Say you have a Person table with columns such as:
ID, FirstName, LastName, BirthCountry, ...etc.
And you want to keep track of changes on such a table. For example, the user may want to see previous names of a person or previous addresses, etc.
The normalized way is to keep names in separate table, addresses in a separate table,...etc. and the main person table will contain only the information that you are not interested in monitoring changes for (such information will be updated in place).
The problem I see here, aside form the coding hassle due to the extensive number of joins required in a real-life situation, is that I have never seen this type of design in any real application (maybe because most did not provide this feature!). So, is there a better way to design this?
Thanks.
