I had needed to do something similar some time ago, and the following describes what we end up with.
We have two tables, Item and UnfinishedItem. When the user fills in the data with the wizard, the data are stored in UnfinishedItem table. At each wizard step, the server validates the data entered during that step. When the user is finished with the wizard, the wizard renders a hidden/read-only form in a confirmation page that shows all data to be submitted. The user can review this page and go back to the relevant step to fix errors. Once the user are satisfied with their entries, the user clicks submit and the wizard then submits all data in the hidden/read only form fields to the API server. When the API server processes this request, it reruns all validations that it did during each step of the wizard, and performs additional validations that doesn't fit into the individual steps (e.g. global validations, expensive validations). After all validations are completed, the API server creates an entry in the Item table.
The advantages of two table approach:
in the database, you can have tighter constraints on the Item table than the UnfinishedItem table; you don't have to have optional columns that will actually be required when the wizard is finished.
Aggregate queries across the finished Items for reporting are easier as you don't have to remember to exclude the UnfinishedItems. In our case, we never needed to do aggregate queries between Item and UnfinishedItems, so this isn't a problem.
The disadvantage:
- It's prone to duplication of the validation logic. The web framework we used, Django, makes this a bit more bearable as we used model inheritance with a bit of meta magic to change the constraints that we need to be different in Item and UnfinishedItem. Django generates most of the database and form validation from the model, and we only need to hack in a few additional validations on top of it.
Other possibilities I've considered and why we didn't go with them:
- saving the data in cookies or local storage: user can't continue their submission from a different device or if they delete their browser history
- store the UnfinishedItem as unstructured data (e.g. JSON) on the database or secondary datastore: I'll have to define parsing logic and can't use Django's automatic model/form validation.
- do the per step validation on the client side: I'll have to duplicate the validation logic between Python/Django and JavaScript.