If a system allows users to save draft (not versioning but kind of like partial save) of their work in database, I can think of 3 ways.

I see similar questions being asked here already but I could not find any with numbers showing performance of each approach. So just wanted to check if someone has evaluated approaches of this sort and already knows performance of each.

  1. Create duplicate tables: We can have 2 copies of each affected table. One with all constraints and one without. Drafts go into one without checks and final version (with all mandatory data) goes in to another. Application code will then look into both tables and do whatever they do with it. This violates DRI and also creates 2 entities on UI for same thing. Plus they are querying 2 tables where they should ideally be querying one.

  2. Same approach as #1 but create a view which shows all records together. Here, application code will only deal with view. This will again violate DRI and also create an overhead of recreation of views at every insert/update.

  3. Have check constraints on all mandatory table along with a flag to tell if this is partial or complete save. This makes life a bit easy but might cause performance impact because check constraint will be evaluated all the time.

This is for a rather small database with 50 odd tables where biggest one would have about 15 columns. There are few tables which also save blob (~500 KB max).

Any suggestions (apart from trying it out myself, of course)?

  • 1
    I'm not sure that I'd want to store drafts in the database, period. This sounds like a better use for local app caching (or another caching layer). Sep 28, 2018 at 11:52
  • @sp_BlitzErik well, the drafts are to be persisted potentially for a long time. So, DB seems a better approach to me
    – danish
    Sep 28, 2018 at 13:21

1 Answer 1


It will depend in part on how long a draft should live:

  • Should it disappear if the user logs out or if user session timeout occurs? Then it would make sense to keep it in memory, e.g. in a Session variable (esp. for Web Apps that often already have support for this).
  • Should it be kept 'forever' until processing is resumed, e.g. an e-mail draft? Then by all means make it persist in the database.

If you choose to persist in the database then personally I would put it in the regular tables, with a Draft/Final flag on the main record. And think about proper cleanup code (perhaps Final records can't be removed, but Drafts can).

Also consider not enforcing all possible rules on it inside the database, instead I would do so using business logic in the Application layer, which can often be done in more flexible ways than what you can (or want to) express in database constraints.

  • Drafts will be persisted until final submission. It can take up to days so DB seems to be viable option. Apart from null/not null, all rules will be handled in application code. I am too inclined towards a flag based approach. I don't think check constraint would harm much.
    – danish
    Sep 28, 2018 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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