This question already has an answer here:

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

marked as duplicate by Max Vernon, RolandoMySQLDBA mysql May 6 '15 at 1:48

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • See Why shouldn't we allow NULLs? for an excellent discussion about this topic. – Max Vernon May 5 '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 6 '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. – Max Vernon May 6 '15 at 21:02

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.

  • 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 6 '15 at 17:49

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