Filling not applicable fields with N/A rather than leaving them empty or Null

Question

I have a database of few million records, many of the fields (call them Ec1 to EcN) have either Null or empty string in them. When I looked at them more closely, I found that for many records, several of this Ec fields are not applicable. So, there should not be any value there and hence nothing is wrong as instance issue.

However, I found several records that should have had value in these Ec fields which don't. So, here are some instance issues in data entry. So, my question is what would be the best approach to distinguish N/A fields from those user failed to enter and avoid further confusion?

One idea is to fill those Ec fields with N/A value and leave the rest with value. In this setting we won't have any NULL or '' field in the entire database. But, it might use more space (in MB scale).

What is cons and pros of using N/A?

Thanks

See Why shouldn't we allow NULLs? for an excellent discussion about this topic. — Hannah Vernon, May 05 '15 at 20:25
Thanks for reply. The question is not about using Null, but how to identify if there is not really a value for the field or the user has failed to enter the value. — Espanta, May 06 '15 at 17:52
The question I referred to is about how to handle nulls, as is this question. Using "N/A" is the same as using NULL except it can be handled far more gracefully in a variety of ways. — Hannah Vernon, May 06 '15 at 21:02

score 0 · Answer 1 · answered May 06 '15 at 01:34

0

Let me change the direction of the question...

If you are making an 'array' of Ec's as columns, that is poor schema design. Instead, you should have another table something like this:

CREATE TABLE Ec (
    id ... -- id in the main table
    ec ... -- one of several Ec values
    PRIMARY KEY (id, ec)
) ENGINE=InnoDB;

There would be any number of rows in this table, depending on the number of Ec values you need. There is no need for NULL or N/A.

answered May 06 '15 at 01:34

Rick James

78,038
5
47
113

I did not design the DB, it is already there, but needs amendment. I don't claim that there is an array of Ec, but these columns must have values for most of the records, however, for some (lets say 10-25%) of records, there might not be value. So, we cannot remove them and put them in another table. It creates redundancy and also make it hard to make joined functions, I guess. – Espanta May 06 '15 at 17:49

Filling not applicable fields with N/A rather than leaving them empty or Null

1 Answers1