CODEWITHSUNDEEP
databasedatabase-designdatabase-schema
daremkd
daremkd

Reputation: 8424

When to use Null vs. N/A in columns?

Suppose you have 2 columns in a table: FixedSalary and HourlyRate. The value for one of these columns is always going to be missing because an employee cannot be paid both a fixed salary and an hourly rate.

Now, suppose I have another table HairColor column. You're updating a row for a patient who is bald. The value will be missing.

For both examples, we can either use a Null value or something like "N/A" or "Not applicable". I was wondering, is there a best (database design) practice regarding these situations, where there is a clearly lack of value, but that lack of value is for a very clear logical reason?

Upvotes: 3

Views: 10965

Answers (3)

bpgergo
bpgergo

Reputation: 16037

The best practice is the Null value

EDIT

Null value is preferable, because that allows the database engine and client programs to automatically handle this value properly, without writing custom code for that. (think about for example sorting values)

Upvotes: 2

Walter Mitty
Walter Mitty

Reputation: 18940

As usual, it depends. Here, it depends on what message you want conveyed between the application that writes the data, and the application that reads it.

NULL normally conveys the message "there is no value at this location". That's often all the reader needs to know. In particular, in the case of hourly versus salaried, a well formed reader app should not be particularly surprised that one of them is missing, in every instance.

A special value to indicate "not applicable" is only really needed when the reader of the data is expected to behave one way when the data is missing because it's not applicable, and a different way when it's missing for a different reason, like a user omission on an input form. This could be the case for hair color, but it depends on your case.

The best practice is to analyze your case, and design accordingly.

Upvotes: 4

Gokul Nath KP
Gokul Nath KP

Reputation: 15553

As per my database knowledge, Null or null is a keyword. Where as Not Applicable or N/A is user defined. So it all depends on how you like to parse the result set.

What would you prefer, rs.getString(COLUMN_NAME); returning null or N/A.

According to me, null would be nice, because, it is easy to check for != null.

Upvotes: 0

Related Questions