4 ms·
> Because it represents (and behaves) as unknown and not as absent. It can represent both. For example, in a table of biographical data, a NULL value for DateO
by dghf 9y ago
> Because it represents (and behaves) as unknown and not as absent.
It can represent both. For example, in a table of biographical data, a NULL value for DateOfDeath could represent N/A for someone still living, unknown for a dead person whose date of death is not known, and total uncertainty for a missing person who could be alive or dead.
- ak39 9y agoI've seen this design in the wild quite often. It's most often attributed to the abuse of an intended original design. The original designer most likely intended the value populated in the "DateofDeath" column to imply death. The intention didn't account for someone down the system's evolution to imply "ALLOW NULL" to be interpreted as optional. If your table is expected to answer "how many dead people whose date of death is unknown" then you'll most certainly also need an "IsDead" (true/false) NOT NULL column. (Best separate the table into "Death" with NOT NULL DateofDeath.)
- dllthomas 9y agoOperational concerns might push you otherwise, but I think in terms of correctly representing the world what makes the most sense is a separate table of deaths with a primary key that's also a foreign key into the table of people, and which has a nullable date of death. That lets you represent all three of "they are living", "they are dead but we don't know when they died", and "they are dead and we know when they died".