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\$

closed as off-topic by 200_success, esote, Toby Speight, Heslacher, pacmaninbw Apr 25 at 13:20

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Lacks concrete context: Code Review requires concrete code from a project, with sufficient context for reviewers to understand how that code is used. Pseudocode, stub code, hypothetical code, obfuscated code, and generic best practices are outside the scope of this site." – 200_success, esote, Toby Speight, Heslacher, pacmaninbw
If this question can be reworded to fit the rules in the help center, please edit the question.

  • \$\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\$ – dustytrash Apr 24 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\$ – dustytrash Apr 24 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\$ – ControlAltDel Apr 24 at 18:07
  • \$\begingroup\$ Why am I getting down voted on this? \$\endgroup\$ – ControlAltDel Apr 24 at 20:31
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\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.