CODEWITHSUNDEEP
sqldatabase-design
kazinix
kazinix

Reputation: 30093

Alternative to NULL in RDBMs

What if I have a varchar field in a database which is not required? Middle name for example.

But what if I want to be clear on the reasons why the data is not available?

But what if the data type is not text? Salary for example, I can't put 'Unspecified' or 'Not applicable' (unemployed).

I could think of solutions but I want to know whether the ideas are good or bad.

1) create flag (tinyint) columns for different reasons why it is null:

| Salary Unspecified | Salary Not Applicable |

2) create a varchar column for the reason why a field is null:

| WhySalaryIsNotAvailable |

Both solutions look weird to me. I haven't seen it before so I'm not sure whether to do any of them.

How do you deal with this problem?

Is any of the solutions above okay?

Upvotes: 3

Views: 279

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

If an attribute is a string, then it is relatively easy to allow for n/a and unknown. However, the following example (design) is more general.

-- Employee (EMP) exists.
--
Employee {EMP}
     KEY {EMP}


-- Employee (EMP) receives regular salary.
--
SalariedEmp {EMP}
        KEY {EMP}
FOREIGN KEY {EMP} REFERENCES Employee {EMP}



-- Employee (EMP) receives salary of $ (SALARY).
--
EmployeeSalary {EMP, SALARY}
           KEY {EMP}
   FOREIGN KEY {EMP} REFERENCES SalariedEmp {EMP}



-- Employee (EMP) does not receive regular salary.
--
UnSalariedEmp {EMP}
          KEY {EMP}

-- derive as a view
CREATE VIEW UnSalariedEmp AS
SELECT EMP from Employee
EXCEPT
SELECT EMP from SalariedEmp
;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You should probably compromise and settle on NULL. It is handled throughout the database system, in comparisons and so on.

You can add the reason in another column. For your example with names, I might be inclined to have multiple columns (including optional ones such as nickname and honorific), along perhaps with a name "format".

The particular example of name does not resonate. I'm not sure I really know the difference between not having a middle name and a middle name not applying.

Note: You can always include this information in another column. But your code for comparison can start to get rather complicated. For instance, if you put a blank (empty string) in for the middle name, but had different reasons, then:

on x1.middlename = x2.middlename

would match, unless you included the reason.

Upvotes: 3

Related Questions