I'm in the planning/design stages of a new project, and I'm having trouble coming up with a good way to handle one of the requirements:
Users must be able to create a new record and save it as "Incomplete" without filling out all required fields.
This is a requirement that will apply to almost all user-editable record types in the application.
Records have a number of potential states, and in any state other than "Incomplete" they must have all required fields filled out.
It's straightforward enough, but I'm having trouble figuring out how to store it in a way that won't sacrifice the ability to enforce data correctness on "completed" records.
The simplest way to handle this requirement would be to make all the fields of the business object and its database table nullable, and then have strict validation checks run against records any time they are moved from the Incomplete state. However, I dislike the idea of enforcing most of the data correctness requirements purely in application-level code rather than in the design of the database, so I'm trying to come up with some other option.
Maintaining a separate schema for incomplete records is an idea that's been discussed, but there's concerns that it will add too much overhead, since we now have a separate schema to extend every time something changes and we'll need to manage moving records from one to the other as they're completed.
Is there a best practice for this sort of problem? Should we just bite the bullet and make all the fields nullable?
(The technology stack we're using is ASP.NET WebAPI on top of SQL Server 2014, but it seems pretty applicable to any stack backed by a relational database)