1

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

Espanta
  • 173
  • 1
  • 1
  • 7

1 Answers1

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.

Rick James
  • 80,479
  • 5
  • 52
  • 119