38

We're building a web application for company, which administration existed only in Excel sheets so far. We're almost done by now, but recently I was assigned a task to import all their data from those sheets to our new system. The system is built in Java, but as this import is just one-time thing I decided to write the scripts in Python instead and import it directly with SQL queries. Here comes the problem. The new data models contains some new attributes, which aren't included in their existing data. In most cases, this isn't a problem, I just put a null where I can't find the information. But then I ran into a few attributes, which are booleans and cannot be NULL by default. First I tried to just allow null for those fields in our database, but my senior dev told me to not do it, as it would cause a issues in our system in the future. And now I'm not quite sure what to do. Obvious solution is to default every unknown boolean value to false, but I think that is wrong too, because I actually don't know, whether it is false.

Example: Let's say you have a entity Car which has a hasRadio parameter. Now you need to import data to this data model, but in data there are only columns "Model" and "Color", nothing about it having or not having radio. What do you put in a "hasRadio" column, if it cannot be null by design?

What is the best approach in this situation? Should we just tell the company to manually fill in the missing data? Or default it to false?

  • 70
    For me allowing NULL would be the correct solution. Was your senior more specific than "cause an issue in our system in the future"? If not, ask him for more specific reasons. – larsbe Aug 17 '17 at 9:21
  • 48
    You should default it to FileNotFound, obviously. – You Aug 17 '17 at 13:03
  • 7
    Would it be possible to add a boolean field, "isValidHasRadio" or something, or would that too break things? – hyde Aug 17 '17 at 14:35
  • 9
    The correct solution is to consider the input data garbage and abort the entire transaction, and then demand the task definition to be adjusted if that data must not be considered garbage. There's no other way here. – Sarge Borsch Aug 17 '17 at 16:51
  • 17
    By the way, I'm not big fan of null values. I'd rather use an enum with 'Unknown', 'Has Radio' and 'Doesn't Have Radio'. This way you're covered on your requirements and have room to grow if you have to specify a type of radio in the future, like 'Radio with Integrated TV' or something like that. – Machado Aug 17 '17 at 20:13
131

This is mainly a requirements analysis problem, and it has nothing to do with the fact the data in stake is "boolean". If you have to initialize tables in a database, or in any other kind of data storage, and you have incomplete input for some columns, you first need to find out what the users of the system or your customer think would be the right default value for those columns, and you need to find this out for every single attribute, there is no generally correct answer.

This will typically lead to one of the following cases:

  • there is a good default value for the specific column, users don't mind if the value is initially the same for all records, they can set the correct values easily afterwards when needed

  • there is a rule how to determine the ideal default value from other information, so you can put this rule into code

  • the users or your customer will extend the input data and provide the missing values (maybe manually), before it gets imported into the database

  • there is no good default value for the specific column and/or any record, the data should be imported either, but the users want to know for which of the records the particular value is already initialized and for which not. So they can enter the value afterwards, and track for which records the value is already correctly set and for which not.

The last case requires something like NULL to represent the uninitialized or unknown state, even for a boolean value, if your senior likes it or not. If there is some obscure technical reason which forbids the use of a NULL value for a specific column, you need to simulate the "unknown" state in a different way, either by introducing an additional boolean column (like hasRadioIsUnknown), or by using a 3-valued enumeration instead of a boolean (like HasNoRadio=0,HasRadio=1, Unknown=2). But speak to your senior again, after you made a thorough requirements analysis, to make sure such a workaround is really necessary.

  • 29
    You should also note that the same answer applies to the other columns where you conveniently used NULL. You should verify whether this is the correct default value. If, for instance, some other column says "processingIsFinished" and you import old data from customers' order history (thinking of a webshop) you might need to set the value to "true" rather than "NULL" to avoid some processes being triggered when they encounter entries not yet processed (according to their interpretation of that column). – Frank Hopkins Aug 17 '17 at 13:29
  • 1
    This is a functional issue. Due to the models (excels and the new one) doesn't match, the migration process should be reviewed taking in account these cases. The only that can say how to proceed is/are the stakeholders (customer or whoever). Technically you can solve this in many ways, but functionally just in only one. The right. – Laiv Aug 17 '17 at 13:33
  • 12
    I like this breakdown. My distaste for null in this context is mostly due to it's lack of clear meaning. Unknown is clear. But does null mean unknown or not applicable? How would anyone know? Just because it makes sense to you doesn't mean everyone else will see it the same way. – candied_orange Aug 17 '17 at 13:53
  • Option 4: Records missing a particular column value are actually useless and should be excluded from the import. Option 5: Someone needs to correct all the incoming data before it gets imported. Lots of options, just depends on needs and budgets. Importing old data is always a huge mess. – jpmc26 Aug 18 '17 at 8:04
  • @jpmc26: well, I did not include option 4 since I wanted to stick what the OP literally wrote (a case where the missing data is definitely not included in the import data, for no record). Option 5 is indeed worth mentioning, since it is another way of avoiding the necessity for NULL values. Edited my answer accordingly. – Doc Brown Aug 18 '17 at 8:39
39

This isn't a technical question; it's a business rules question. So, you need to ask "the business."

Approach the product owner and/or stakeholder(s) and say something like:

We have incomplete data for one of the fields you requested in the application. Would you like us to use a default value? Would you like us to add "unknown" as a valid value? Or, would you like someone on your team to correct the data before the import?

Some discussion will probably ensue. But, that's basically it. The technical solution will flow naturally from the more fleshed out business rules.

9

The general problem is a whole subarea of programming called data cleansing which is part of a larger subarea called data integration. Avoiding these sorts of issues is likely a large part of the reason for the migration from Excel sheets and why the senior dev doesn't want to allow a field to become nullable. I don't think it's unreasonable to say that this is one of the larger sources of complexity in data migrations.

Just choosing to use NULL whenever you could is likely very much the wrong thing to do, let alone changing the data model to make yet more fields nullable. Excel has weak or no integrity checking which is likely the cause of many of these issues. The wrong thing to do is to remove the integrity checking in the new database and dump garbage into it. This just perpetuates the problem and adds significant complexity to future integrations which have to somehow deal with nonsensical data.

Some of the difference is likely due to data model mismatch. Dealing with this is largely a matter of being (intimately) familiar with both data models and knowing how to map the old one to the new one. As long as the new one is capable of capturing the old one. (If not, your team likely has a very big problem.) This can easily require doing more work than just copying columns. Darkwing gives an excellent example of this (as well as why blindly inserting NULLs is the wrong thing to do). Elaborating upon it, if the old model had a ReceivedDate and an InProgress bit and the new model has a StartDate and ProcessingEndTime, you will need to decide if and how to set the ProcessingEndTime. Depending on how it's used, a reasonable (but arbitrary) choice might be to set it to be the same as the StartDate (or shortly afterwards if that would cause problems).

However, some of the difference is likely due to data that "should" be there that is missing or corrupted. (Most likely from data entry errors or poorly handled past migrations or bugs in data processing systems.) If no one on your team anticipated this, then you (collectively) have set yourselves up to spending 20% of the time of the project being "almost" done. (That was a made-up number, but it can be far worse than that, or better. It depends on how much data is incorrect, how important it is, how complex it is, how easy it is to get involvement from those responsible for the data, and other factors.) Once you've determined that the data is "supposed to be" there but is missing. Usually you'll attempt to determine the extent of the problem by querying the old data sources. If it's dozens or hundreds of entries, then it's probably data entry errors and the customers responsible for the data should manually resolve it (i.e. tell you what the values should be.) If it's millions of entries (or a significant fraction of the data), then you may need to reconsider whether you correctly identified that it "should be" there. This might indicate a modeling error in the new system. When you ask the people using the data about the missing data, they are often somewhat aware of it and have ad-hoc ways of dealing with it.

For example, imagine an invoice that had quantities and per item totals (but not unit price), except that some of the quantities were inexplicably missing. Talking to the person who processes such invoices might produce one (or more) of the following scenarios: 1) "oh, a blank quantity means a quantity of 1", 2) "oh, I know those items go for around $1,000 so, clearly this is an order for 2", 3) "when that happens, I look up the price in this other system and divide and round", 4) "I look it up in another system", 5) "that's not real data", 6) "never seen that before".

As suggested, this can indicate some ways of automatically resolving the situation, but you have to be careful that the solution applies to all cases. It is common for other systems to be involved that can cross-check the data, and this is a good thing. However, it's often a bad thing insofar as it can be difficult to gain access to and integrate with these systems to perform the cross-checking, and it often comes to light that the systems conflict with each other not just by one missing some data. Some manual intervention is often required, and depending on the scale, may well require tooling and interfaces to be created specifically for the data cleansing task. Often what is done is the data is partially imported but rows with missing data are sent to a separate table where they can be reviewed. Often this will need to be done at an appropriate granularity for consistency in the new system (i.e. reject invoices not individual line items even if most of the line items are fine in a particular invoice) and it can lead to cascades (if I can't import a client, then I can't import any invoices for that client).

  • 14
    In summary: if you think dealing with legacy code is unpleasant, try dealing with legacy data. – Peter Taylor Aug 17 '17 at 16:10
0

Change the datamodel.

You can normalize out the hasradio and then you won't have any nulls anymore.

If you can't determine a boolean value, then don't use a boolean.

By allowing a boolean value to become null it stops being a boolean. A boolean can have 2 states: False, True.

What you need is 3 states: False, True, Unknown.

Do you have the option to change the datamodel?

( And another thing I thought of, if in python or java you retrieve the data from your database. You retrieve the record, check the hasradio field, what will happen if you check whether it's true or false and it happens to be null? )

  • 2
    By changing the data model and "normalizing out hasRadio", I assume you mean something like adding a new table CarFeatures, with fields Car_ID, Feature_ID, Has_Feature? Seems like a good idea. – jpa Aug 17 '17 at 13:33
  • 2
    @jpa it's a bit of a tricky situation. You have to be very clear in what you do, because the absence of a record in our situation means unknown. While often the absence of a record means it doesn't have the feature. – Pieter B Aug 17 '17 at 13:51
  • 1
    You are looking at it wrong, Pieter. Nobody says a bool has more than two values, because, as you have said, it does not. A bool is either true or false. However, in OPs case, OP is not dealing with a bool directly, but rather an Option<bool>/Maybe<bool>, which can have Some -> true/false or None. – Andy Aug 17 '17 at 14:01
  • @DavidPacker my argument is that because of that it's a Maybe<bool> you should stop calling it anything remotely similar or you will get confusion. And if you insist on using a boolean then find a safe way to do it. – Pieter B Aug 17 '17 at 14:09
  • 4
    In my opinion, nullable boolean is completely fine. I have never had problems with null values, although I have met developers who did. – Andy Aug 17 '17 at 14:36
-1

As others have pointed out, what you have here is a boolean value which is not truly boolean and the issue is to either force it to be boolean or handle it otherwise.

What you could do is, instead of having a single boolean result, to have two boolean results. These could either agree or disagree. If they agree, then you have a straightforward true/false result.

If, however, they disagree then you have an indeterminate result and you have a chance, depending upon the circumstances in which it arises, to decide on how to handle that. In some cases an indeterminate result might be best interpreted as true, whereas in others, the same indeterminate result might best be interpreted as false, according to the safest option.

This would still though allow the result to be reported as indeterminate, so this additional nuance of the value would not be completely lost, up until the point where the value can be definitively resolved and reset.

protected by gnat Aug 18 '17 at 20:42

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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