How to store different version of data in a relational database?

Question

Here is the example, for example, I have a table called Profile, and have different columns like:

id, firstName, secondName, address

typically, I create a profile, full in the information, and the database will become something like this:

 1| Ted | WONG | Hong Kong |

after that, I may update the data, like this

 1| Ted | WONG | US |

the data Hong Kong will be changed by an UPDATE SQL command, and I lose track of previous data. So, is there any way to let the database keep track of previous data and maintain the current information? Thanks.

Answer 1

Add a version number column that you increase with each update but retain the same id. Then when retrieving the latest row for a given id you need to do a

where versionNo = (select max(versionNo) from table where id = <outerTableAliasOrVariable>.id)