Should draft records be kept in a separate table?

Question

We are creating a simple web system in which someone adds an entry, such as a CMS page, which gets the approval of someone who will respond before being shown on the website.

If subsequently the author decides to edit this page, we want to create a draft based on a live copy, after approval he will replace the old live page.

We thought about full version control, but we believe that we can keep it simpler by simply having 1. Just a draft, 2. Just live, or 3. One draft and one live.

This function is required for several "things", and not just for pages.

Finally, the question is: do you think it would be better to keep these two records in one table or would a mirror table be better?

I guess this probably depends, but I don't like the ideal of having two tables with the same structure. Is there a tradeoff for slower operations (how do we have to request drafts all the time when displaying data)?

Answer 1

Not. One type of object, one table.

Reasons for revision:

• Drafts record the number of live recordings in thousands of copies.

• Security conditions require that some users who access the database directly have certain rights to drafts or live recordings at the GRANT / REVOKE level, but not in comparison with other types of recordings.

The second design to consider is one table for elements and a second table for LiveItems. The second table contains only identifiers for objects that are alive. This way you maintain your single table design, but you can find LiveItems by attaching a single-column table to the main table.

Answer 2

Moving material from table to table when the state changes is a bad idea.

If you want to add additional states to the workflow, you need to add some more tables.

This is just a state change - that is what relational databases are optimized for.

One table, several states - a standard approach.

If you find that everything is terribly slow, and you can prove that the state-based query is the whole reason - you can resort to “materialized views” or similar technology, where the state change (and the resultant move) of the RDBMS being processed.

State table is a bad idea.

• You cannot easily add states. You also need to add tables, which makes them painful. In addition, you need to update the code with the new table names to reflect the new workflow.

  If the state is only a column, adding new states adds new values ​​and new if-statements in the code. State changes are just updates, not delete-paste.

  Data lasts forever, workflows come and go every time a user has a smart idea. Do not punish them for wanting to change the workflow.

• You cannot easily have sub-states. Many state machines are actually multiple, nested, state machines. Adding a sub-state with a table state creates even more tables with even more rules.

  If state is just a column, a nested test is another column with new if-statements in the code. State changes are just updates, not delete-paste.

• You cannot easily have parallel state machines. Many times there are many concurrent changes to the status code. Sometimes there are manual workflows (approvals) and automated flows (archiving, copying to the data warehouse, etc.). When using computers with tabular and parallel states, there is no way to rationally implement it.

  If each state is just columns, parallel state machines are just parallel updates.

Answer 3

Consistent with all comments above: only one table.
With scopes, you can easily get only published posts or drafts.

I would not recommend for this.
But if you really want to have two different models for drafts and published entries, there is another solution: STI .

You will have two models:

class Post < ActiveRecord::Base end class Draft < Post end 

Any draft object is taken from the Post table.
The Type parameter makes it a mail or draft.

Whenever you want to post a message that you will need to do:

 @draft = Draft.first @draft[:type] = 'Post' 

Answer 4

I just made a gem for such a precedent. It stores drafts in a separate table: https://github.com/ledermann/drafting