7

I have encountered this problem a number of times. Let's say you have a large object that you want to store in the database. Perhaps it's a dating profile for a user, and it has 100 fields. All of these 100 fields should have a value, once the profile is complete.

However, you want to avoid forcing the user to enter all 100 fields in one go! Perhaps they can create their profile section by section, but it is not considered "complete" until all 100 fields have a value.

It would be nice not to allow these fields to be NULL. After all, NULL is not a valid value for them. But if the fields are NOT NULL, you can't build the row up bit by bit, saving a few fields each time.

"Solutions" I have used in the past:

  • Just make the fields nullable. This is not great, as you are losing some guarantees of data integrity. A bug in your app leads to inconsistent data.
  • Split the 100 fields into several tables. Make all the fields NOT NULL. This adds complexity to queries, and seems a bit wrong - a completed profile should not have a row in one table but not another, but the DB schema allows this.

What are some good ways of handling this?

  • 5
    If your uses can avoid entering some values until later, then NULL is appropriate. Your business logic might not let them do things until the profile is complete, but thats not something to enforce with the DB design. The same data can often be used in different contexts, and what's value in one is not valid in another. – Andy Sep 22 '17 at 1:21
  • 5
    You said that "NULL is not a valid value for them" but this is not true because elsewhere in your question you said that users are not required to complete these fields. You can't have these fields both required and not required at the same time. – bikeman868 Sep 22 '17 at 7:14
  • 1
    Splitting columns across tables is known as vertical partitioning. It is a legitimate technique. You could have one partition per section. Either a section is complete or it is not stored. All columns are NOT NULL. Note, however, that a full profile constructed from not-yet present partitions will have NULLs. – Michael Green Sep 22 '17 at 12:00
  • 2
    If the business rule is "field X can be NULL under certain circumstances," you have to put in a check that the circumstances are met. Unilaterally declaring the field NOT NULL won't work. – Blrfl Sep 22 '17 at 13:51
  • 1
    if your business requirements state that a value can be null... then the database must reflect this. I really don't see what is so wrong with null values that one has to go through hoops and take ginourmous detours just to avoid a perfectly valid business wise null value just because someone once said "null is bad". Null values in this case is the simplest and easiest means to tackle this problem. – Newtopian Sep 22 '17 at 14:00
8

Two Tables and a View

Have two tables with mirrored schemas, differing only in the nullability of the columns. Once all the data is present, you move it from the pending table to the complete table. Have a view that presents the union of the two (the schema of the view will match the schema of the pending table)

  • 1
    You'll want to be careful with views (at least in MySQL) because you will lose your index and thus have much slower reads. – beattyac Sep 21 '17 at 16:45
  • A UNION is a pretty expensive operation if the tables are large, I'd be disinclined to take this route. – e_i_pi Sep 22 '17 at 0:28
  • I like this solution. If it weren't for a possible performance issue, this would be a both elegant and robust solution. – Neil Sep 22 '17 at 7:44
  • 1
    @e_i_pi but UNION ALL isn't. Depending on your platform, you can index a view just a easily as a table. – Caleth Sep 22 '17 at 8:12
  • This seems like a good solution to me. For many use cases it may not even be necessary to retrieve data on complete and incomplete profiles at the same time - in which case possible performance issues wouldn't be a consideration. – Will Sep 22 '17 at 9:42
5

You might keep the fields nullable (or not nullable, but allow empty/default content if that's applicable), and add another field that's a flag indicating if all the fields are filled out. Default that to 0. Once the fields are successfully filled out and/or meet the criteria to be considered 'finished', set the flag to 1. This would make it easy to filter queries on only completed entries.

  • As an aside, you can well allow the field to be null, but have a constraint that says if it's marked as finished, the field must not be null (eg, with CHECK ((might_not_exist IS NOT NULL AND finished = 1) OR (might_not_exist IS NULL AND finished = 0))). – Kat Sep 26 '17 at 19:41
5

How about storing the incomplete dating profile in something simple, like a key/value table where the large profile object is simply serialized into JSON/XML. You'll only need to use the key to get the profile out as they are editing it piece by piece, and once it passes all your business validations, at that point, insert it into your permanent, normalized database table and delete it from the key/value "incomplete" table.

This gives you a clean separation of incomplete/complete profiles, plus you won't have to maintain the incomplete key/value table if fields change, since its just serialized data in a single column. You can query that JSON for stats if you really needed to, it just probably won't be as performant as the SQL version but I doubt that'll be a concern.

3

Just make them nullable

If you can... if you cannot then, and only then start looking for some magic trick to get around this. Simplest of which is probably default values which will behave pretty much exactly as null values would. Get the data, check for (null|default) and react accordingly.

If you cannot use default values then try one of the more complex alternatives proposed here. Ask yourself though, is the added complexity worth the trouble vs just convincing the powers that be to just make them null to begin with.

It's not because something is technically possible that it's necessary a good idea.

  • I guess the reason for this question is that this approach ("Just make them nullable") has come back to haunt me several times in the past. I'd prefer to spend time up-front making sure fields have values, than try and fix broken data as a result of some application bug. – Will Sep 22 '17 at 15:54
  • making fields nullable systematically will in no way help fix broken data bugs. In this particular case the fields should be nullable because they are in effect nullable business wise. I do admin that making a field null does add some overhead that MUST be dealt with in some way. If your business case does not warrant this overhead then the fields should not be nullable, making them in this situation will inevitably come back to bite you. But if your business case states that the fields can be null at any point in time, however short that may be, then that overhead cannot be escaped... – Newtopian Sep 22 '17 at 16:03
  • ... You MUST deal with this fact. Simplest way to do that is ... well.. to accept that the database can return null. Any other tricks you can try amounts to dealing with the possibility of a null value in some form or another. So the question becomes... How far are you willing to go just so you keep the fields not nullable in the database ? Refusing to use null in this situation amounts to trying to use English without the letter T. I'm quite sure it's possible but is the trouble worth the effort ? – Newtopian Sep 22 '17 at 16:11
  • Is the trouble worth the effort? In some cases, sure! The solution suggested by Caleth might take a couple of hours to set up. I have spent countless hours sifting through DB records that have null values that shouldn't be there in an old job. I want to learn from my mistakes. – Will Sep 22 '17 at 16:34
  • 1
    Still, Caleth's solution does not remove nullables, the view and the pending still have nullable in them that you will have to deal with as the user fills his profile. Tough technically possible I fail to see how it addresses the nullability issue you are having, true the completed profile has no null in them, but in exchange you get a view and an extra table both of which you will have to deal with nulls. Unless I've misunderstood the problem to begin with, I fail to see what this extra complexity gains here, to me it's a clear negative sum – Newtopian Sep 22 '17 at 16:44

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.