How can I store incomplete records but enforce data correctness?

Question

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)

Answer 1

You could have a separate table for works in progress. You would use more or less the same table scheme, just with each field set as nullable.

When a record is initially created, it goes into the incomplete records table (you could have a check to put first time completed records directly into your completed records table, of course). Once a record has been completed, you would copy it to your completed table and delete it from incomplete.

Of course, this would force you to join tables in the case that you did want to run queries over complete and incomplete records, but that's hardly the end of the world [Citation Needed]

Answer 2

One possibility, not yet mentioned, is that you don't stick an incomplete record in the database at all - you just hold it as a blob (serialized by some means) somewhere (could be in the database, could be elsewhere).

It rather depends on what, if anything, is done with incomplete records (this is about workflow and related). Specifically if 90% of the code loads data with variations of '''WHERE NOT status = incomplete''' then basically incomplete records are just noise. If, on the other hand, value starts to accrue even though the record is incomplete then this is not necessarily a good solution.

Either way simply asking the question may aid better understanding

Answer 3

The requirement is self-contradictory: the required fields must all be optional. But okay, let's laugh at the person who wrote the spec and then move on.

I think creating two tables is a very bad idea. This would mean that all the field definitions must be duplicated. It's likely that many queries would have to be duplicated -- your basic insert at a minimum. So any time there's a change to the schema, you have to be careful to make it in both places. Sooner or later somebody's going to forget, or you'll have a programmer working on it who doesn't know about the two tables, and then if you're lucky it blows up in testing and not in production. If you have processes that apply to both complete and incomplete records, now you have to do UNIONs, which if the query is simple may not be a big deal but if the query is complex would add another layer of complexity and create potential for error. You may have to worry about duplicates between the complete table and the incomplete table. Etc. All around, sounds like a giant pain.

If the validation for a complete record is simply "not null", then okay, doing it in one table forces you to write some extra code. This doesn't sound like all that big a deal to me. You could easily throw in an add/update trigger that says that if complete=true and (name is null or foo is null or bar is null), throw an error.

I'd think you need this validation in the code anyway. You likely have some sort of data entry screen where the user has to type all this in. I'd think you'd want to give an error message like "Error: Last name cannot be blank" or something of that sort. You don't just rely on a SQL exception to tell you that data is invalid, do you? Sure, when a field is really required, I'll put a not-null constraint in the DB as an extra guarantee against bad data. But the code should be checking it before writing a record anyway, so if the field is really only conditionally required, I put that test in code.