Saving row versions in MySQL I just want to confirm that I am versioning my "versionable" fields correctly, and that there isn't another, better way to do this.

I have a table event, a record of an event in time. It has some fields that I want to be invariant, like it's ID and event series it is associated with. It also has some fields that I want to be editable, like the date or the description. The other issue is that I have foreign keys on the auto_increment ids of both tables, which is why I think I need two tables and not just one, or?  But I also want to keep the history of the variant fields, So I created two tables:

invariant

id int
series int
active boolean


and

variant

eventID int //foreign key to the invariant ID field
id int //this table needs its own id which serves as a foreign key on another table
date Date
description varchar(255)
active boolean


When an edit is made to the variant fields, I am switching the active Boolean on existing rows with the same eventID to false. then when I insert my new version, I can get the just latest version on the invariant - variant join by specifying where active=true. 

If/when I want to delete the event entirely, I am setting active to false in the invariant table.

As I said at the top, I just want to confirm that this is an optimal solution for the specified requirements, or if there are better ways or things I am not understanding
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.