Part-saving a form and continuing later [closed]

Question

I've done some digging and can't find a similar question, I've found some reference to some of the bigger names in 'online forms' like SurveyMonkey etc being capable of 'partial saving' but haven't been able to find out any information as to how the achieved it.

My scenario: Form is for back-office staff, said staff member is completing a form for something, they come across a field which they need to do some investigation into in order to complete, rather than force them to go and find it right now (it may be too late in the day to start looking for said information), I want to offer the ability to save the form in it's current state, and be able to recall it later once the adviser has the information to continue.

I can make the form, I can make the part-save button, and be able to recall it later with PHP/MySQL no problem, but what's the best way of tackling the partial save from a SQL perspective?

The user will always be authenticated with the system, so saving their information so they can later resume is not an issue.

I found this question which sent me looking into GarlicJS, but I want the staff member to be able to have multiple forms active at once, so they can start a new form without being forced to finish the current one first.

Some thoughts of how I could tackle it...

• I could create a boolean flag in the table which is 0 for partially complete and 1 for complete, but this means all of my fields need to be nullable, is this a design flaw?
• Save the partially complete form in another table, then remove it from this temporary store once it's been submitted fully and add it to the main table.

So my question is, what's the best approach? Anyone had any experience in doing something similar?

Answer 1

In my opinion having a column with flag "completed" (true / false) is the best and simplest idea. There is no problem with having nullable values from technical point of view. It is not worse in any way.

If there is a logic problem, you can -- before changing this flag -- forbid NULL values. This could be done by PHP (validating form data) or by SQL (triggers).

This is much simpler than having another table, because you'll need good management system for this so you ensure yourself that there is nothing common in both tables. Having everything within one table allows you to SELECT all records, all finished, all not finished without this magic JOIN and UNION stuff.