title: MySQL history table design and query
body: <p>TL;DR: Is this design correct and how should I query it?</p>
  
  <p>Let's say we have history tables for <em>city</em> and <em>address</em> designed like this:</p>
  
  <pre><code>CREATE TABLE city_history (
    id         BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    name       VARCHAR(128)    NOT NULL,
    history_at DATETIME        NOT NULL,
    obj_id     INT UNSIGNED    NOT NULL
  );
  CREATE TABLE address_history (
    id           BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    city_id      INT             NULL,
    building_no  VARCHAR(10)     NULL,
    history_at   DATETIME        NOT NULL,
    obj_id       INT UNSIGNED    NOT NULL
  );
  </code></pre>
  
  <p>Original tables are pretty much the same except for <em>history_id</em> and <em>obj_id</em> (<em>city: id, name; address: id, city_id, building_no</em>). There's also a foreign key relation between <em>city</em> and <em>address</em> (<em>city_id</em>).</p>
  
  <p>History tables are populated on every change of the original entry (create, update, delete) with the exact state of the entry at given time.</p>
  
  <p><em>obj_id</em> holds id of original object - no foreign key, because original entry can be deleted and history entries can't. <em>history_at</em> is the time of creation of history entry.</p>
  
  <p>History entries are created for every table independently - change in city name creates <em>city_history</em> entry but <strong>does not</strong> create <em>address_history</em> entry.</p>
  
  <ol>
  <li><p>So to see what was the state of the whole address with city (e.g. on printed documents) at any <em>T1</em> point in time, we take from both history tables most recent entries for given <em>obj_id</em> created before <em>T1</em>, right?
  With this design in theory we should be able to see the state of signle <em>address</em> with <em>city</em> at any given point of time. Could anyone help me create such a query for given address id and time? Please note that there could be multiple records with the same exact timestamp.</p></li>
  <li><p>There is also a need to create a report for showing every change of state of given address in given time period with entries like "city_name, building_no, changed_at". Is it something that can be created with SQL query? Performance doesn't matter here so much, such reports won't be generated so often.</p></li>
  <li><p>The above report will probably be needed in an interactive version where user can filter results e.g. by city name or building number. Is it still possible to do in SQL?</p></li>
  <li><p>In reality <em>address</em> table and <em>address_history</em> table have 4 more foreign keys that should be joined in report (street, zip code, etc.). Wouldn't the query be like ten pages long to provide all the needed functionality?</p></li>
  </ol>
  
  <p>I've tried to build some queries, play with greatest-n-per-group, but I don't think I'm getting anywhere with this. Is this design really OK for my use cases (if so, can you please provide some queries for me to play with to get where I want?)? Or should I rethink the whole design?</p>
  
  <p>Any help appreciated.</p>