7

The database I'm currently building is pretty traditional relational database stuff, but there is one requirement that is not clear to me how model appropriately.

There are many business rules which are translated into database consistency checks (i.e. date A must be between date B and C or this field must be not null when another field is a specific value). My problem arises because a user must be able to work on a "draft" version on the data, being allowed to save their changes even if the consistency checks fail.

Eventually, a user can commit their draft, but only once all the consistency checks pass.

The main reason the client wants this functionality is so that they can query the database directly for reports, and be sure that they are using the most recent version of the data that is "consistent", but not restrict the user from entering partial data.

I've played around with storing the changeset to the data in JSON in a document store, and then applying the changeset in code when dealing with the user is dealing with draft data, but this makes it hard to manage things like relationships and the creation/deletion of records.

Is there a common way to accomplish this goal? Or does anyone have any suggestions about how I may approach this problem differently?

5

This is going to sound crazy but I'd create a separate service that stored the data in another set of tables, but this other set of tables had no integrity checking. The user would do all the partial work there, and when they were ready you could move their data into the checked schema in a single transaction.

  • if you must have this at database level, I'd say this is the right solution. E.G. when 'drafting' object A, you save it to the 'objectDraft' table, and if you are ready you commit it to the 'object' table. Does add some complexity though. But you can query your actual tables without fear of inconsistencys. – Nanne Jul 21 '14 at 19:18
4

Use some sort of identification field for draft mode (IsFinal) on your records. This value can then be taken into consideration when applying any finalization criteria:

If IsFinal then //do a bunch of checks

You can do this in your code, database constraints or triggers.

  • 1
    This seems much simpler than the accepted answer, which violates the DRY principle in a pretty big way. – Jon Bentley May 1 '17 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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