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:
id int series int active boolean
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
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