5

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)

  • I assume that you have some method or property on the record that you can call which allows you to change the state from "Incomplete" to "Complete." Simply validate your object when that call is made, and refuse the change if the data does not validate. Put the validation code *in the object itself;" the resulting encapsulation should alleviate any concerns about "enforcing most of the data correctness requirements purely in application-level code." – Robert Harvey Jan 20 '16 at 3:54
  • The requirement that users must be able to make incomplete records means you can't do your validation in the database, unless you can think of some fancy way to create such constraints based on the value of the "state" field ("complete" or "incomplete"). Perhaps you could write a trigger? – Robert Harvey Jan 20 '16 at 3:57
5

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]

  • By all means, go with the duplicate table. Your SQL schema should be an explicit part of the code base anyway, so running off a second copy of a table without NULL constraints is easily done. – Kilian Foth Jan 20 '16 at 7:18
3

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

1

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.

  • 2
    It's not a self contradictory requirement. It's a quite common requirement actually. – RubberDuck Jan 20 '16 at 10:19
  • @RubberDuck You don't see the contradiction in saying "these fields are required, but they can be omitted"? They have to be supplied, but they don't have to be supplied? Yes, the REAL requirement is something more like, "these fields are required for the record to be marked as 'complete'" or something to that effect. – Jay Jan 20 '16 at 14:41
  • Exactly! It's only a contradiction on its surface. – RubberDuck Jan 20 '16 at 16:28
  • Sure, that's why I said we can move on. The requirement as written is a contradiction, but we can reasonably infer what the person really meant. Requirements that make no sense as written and that have to be "interpreted" happen quite a lot in this business, actually. That's why I'm not afraid of being replaced by an AI any time soon. – Jay Jan 20 '16 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.