Saving row versions in MySQL
Do not use an 'active' column as a way of joining a table.


  The other issue is that I have foreign keys on the auto_increment ids
  of both tables


I'm assuming you mean the invariant &amp; variant tables both have PrimaryKeys which are auto_incremented. If so, this is fine. Most tables have an auto incremented primary key.

You could put all the columns onto the variant table (which is where they belong) and have an audit table. The audit table would contain the old value(s) &amp; new value(s) along with the changed date.

If you wish to continue with your unique versioning system, I would still remove the active indicator on both tables, and instead sort by latest date to find the current. If no rows exist on invariant, either variant is 'inactive' or should be deleted.