HISTORY OF DBSCHEMA CODE
It is okay if code which modifies the table is burdened by having to refer to the table as "NAME_HISTORY" instead of "NAME", because that code will presumably also have to take account of the fact that it is not updating the table, it is appending new historical records to it. Rename each table from "NAME" to "NAME_HISTORY" and then create a view called "NAME" which presents to you only the latest records. If you must keep historical information in the database so as to be able to execute queries against it, then I would recommend using views. Nothing needs to be added to the actual data tables, and no additional complexity needs to be added to the queries. So, what quite often suffices is to simply log each modification that is made to the database, for which you only need a log table with a date-time field and some variable length text field into which you can format human-readable messages as to who changed what, and what the old value was, and what the new value is.
![history of dbschema history of dbschema](http://flamory.com/content/intergration/apps/screenshots/dbschema-preview-large.jpg)
Quite often, the history of changes does not have to be structured, because the history is needed for auditing purposes only, and there is no actual need to be able to perform queries against the historical data.
HISTORY OF DBSCHEMA HOW TO
However, I meant this question asked in a more general form, not specific to any particular engine, though suggestions how to solve this in certain engines are appreciated, too, in the general interest. Which DB? I am currently using sqlite but plan to move to a server based DB engine eventually, probably Postgres. I've come up with a few ways, which I'll add below as answers, but I wonder if there are better ways (While I'm a seasoned code writer, I'm rather new to DB design, so I lack the experience and already ran into a few dead ends). However, I also like to avoid having to build very complicated SQL queries for retrieving only the latest version of each person's records (while this may be easy with a single table, it quickly gets difficult once the table is connected to other tables). I like to keep the history in a way that when I look at a person's record, I can quickly determine that there are older recordings of that person's data as well. Instead of simply updating the single person record to the new values, I like to keep a history of the changes. Sometimes, the contact information changes. Consider a database that maintains a list of persons and their contact information, including addresses and such.