Sql-server – Storing draft version without any constraint checks

sql serversql-server-2012

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)?

Best Answer

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.

Related Question