R – Most code-efficient way to handle data stamped as “final” vs. data that may incur changes

activerecorddata structuresdatabasedatabase-designruby-on-rails

I have several models whose records AND associations can have two states that must be persisted. Final, and Draft.

Here's a little more detail: If the model is "application form" and the user submits a final application form, then I need to store and be able to retrieve that final application form.

If, at a later date, the user wants to modify that 'application form', then they need to edit a draft copy of that final record until he submits a new final version. That is, he cannot edit the previous final application form until he checks the "final" box, at which point the draft becomes the new final version.

The catch is that both the final and draft have associations must be stored with their related records. That is, their related records (e.g. an application has many contact persons) must be stored in a manner that is retrievable from the final and the draft versions without confounding a 'final' contact and a 'draft' contact.

Currently I can think of two ways to solve this problem:

  • Use ActsAsAudited and audit only the final records. Querying for the finals whenever I need them. (There's a fork of ActsAsAudited that also tracks associations). Then query the Audit tables for the latest records.
  • Use two parallel data table sets: One that keeps the drafts, and another that keeps only the final copies.

I THINK keeping the finals and drafts in the same table might duplicate the purpose of those tables, and render the structure more difficult to follow.

Do you know of any other schema or strategy that would handle the problem more elegantly? Or that would reduce code complexity?

Here's a similar question that ended up using the second option above:

Draft version of database table

Bernie

Best Answer

The catch is that both the final and draft have associations that must be kept in sync

How can a final version change, ever? Final versions should be immutable.

Do you mean that someone might draft and submit a document whilst other users are making drafts?

If so, source control seems applicable. A wiki-approach for merging documents would be useful, or a simple 'locking' principle so that two users cannot conflict.

As for storing a versioned document, if you discount using the file system and want to use a conventional database, there's two main approaches:

  1. put it all in a single table, with a flag for draft|final, and a flag for the 'head' - the most recent final version of each document

  2. three tables: a table of the 'heads', a separate table of drafts and a third table with the historic final versions

The advantage of one approach over the other will be performance and distribution if the system gets really big.

But if the system is small, I'd advocate the first approach - a single table - possibly with the finesse of three 'views' so as to make migration to the more complicated three-table approach less painful in version 2.0.

Related Topic