0
\$\begingroup\$

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

\$\endgroup\$
4
  • \$\begingroup\$ Why do you need the active column on the invariant table? Sounds like the value can be retrieved based on the variant table \$\endgroup\$ Commented Apr 24, 2019 at 17:49
  • \$\begingroup\$ "and event series it is associated with" Are you sure the event series is invariant? You mentioned you can delete events. \$\endgroup\$ Commented Apr 24, 2019 at 17:55
  • \$\begingroup\$ @dustytrash I think the language I've used is confusing you. I am using the active field in both tables to "delete" records without actually removing them from the database. Does that make sense? To your other question, yes eventSeries is invariant. It is used among other things to ensure that different clients can't see or edit other clients events \$\endgroup\$ Commented Apr 24, 2019 at 18:07
  • \$\begingroup\$ Why am I getting down voted on this? \$\endgroup\$ Commented Apr 24, 2019 at 20:31

1 Answer 1

Reset to default
0
\$\begingroup\$

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 & 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) & 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.

\$\endgroup\$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.