Draft version of database table

Question

I have a database with a report table. This table represents report data, and has constraints. Now the application is used by firemen, and they might have to run off and put out some fires. So in the middle of filling out data for this table, they might have to run off. What we do is when the session run out we save the data to a draft table, where we simply serialize the object graph, no constraints (since we can't know if everything is in a valid state). And when the fireman later wants to pull data back out we simply pull out the draft again.

This works fine! However the firemen now wants to be able to search the table and the draft table. So here I would like to maybe store all the data (including the draft) into one table, that I search, so I don't have to consolidate the search from two tables..

Is there some kind of database pattern for drafts? What I want is a patternfor storing data without constraints (in draft mode) and with constraints (in final mode).

Answer 1

I've thought pretty long and hard about this problem, and here is my answer:

1. store drafts and their corresponding validated entities in the same table. have a is_draft column

2. use is_draft=1 to turn off validation in triggers and check constraints, or your ORM validation rules

3. many of your fields will have to be nullable or have defaults

this has several advantages:

1. drafts and their corresponding validated entities are stored in the same table

2. drafts and their corresponding validated entities have the same id . why is this important?

imagine you start a quote for a customer. save it. it gets an id. you are looking at it in a web browser /quotes/id/55 . you start editing that quote, remove a required value because it was wrong. you have to go next door to look it up, but you want to save it. the id number should not change.

it would also be confusing for people if they are collaborating on the same document and its id keeps changing if it moves in and out of a valid state.

Answer 2

I don't know if it this would qualify as a pattern, but I think that the cleanest way to go about this would be to create a view that does a union of the two tables and search against that.

Answer 3

Go with the simple solution: create a View which is just a union of the two tables (which should be fairly straight forward as they I assume they both have (almost) identical structures) and then run searches on that as the source if they want to include both.

I wouldn't actually merge the complete and draft data at any point: the potential for error and accidental use of unvalidated data seems huge.

You wouldn't be working on an interface for the IRS in the UK at the moment, would you? If not, sounds like DK have gone for a similar (in principle) solution to this problem (I used to work for a UK Fire and Rescue Service).

Answer 4

I dont know of a Database pattern for Drafts, but I would suggest keeping your seperate table if you require validation and simply searching on both tables.