title: SQL schema pattern for keeping history of changes
body: <p>Consider a database that maintains a list of persons and their contact information, including addresses and such.</p>
  
  <p>Sometimes, the contact information changes. Instead of simply updating the single person record to the new values, I like to keep a history of the changes.</p>
  
  <p>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. 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).</p>
  
  <p>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).</p>
  
  <p><strong>Which DB?</strong> I am currently using sqlite but plan to move to a server based DB engine eventually, probably Postgres. 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.</p>