How do we distinguish between "not filled in" and "unknown" in our data store?

Question

In our domain model "not filled in" and "unknown" are two different concepts. For example, time of death may be missing or, on the other hand, may be filled in as "unknown"

How do we honor that distinction in our data model (e.g. in Oracle DB)?

I figure we can reserve NULLs for "not filled". Then how do we describe "unknown"? Should we change the column type to some custom EXTENDED_DATE that has a special value for "unknown"? That would be a huge undertaking

CREATE OR REPLACE TYPE date_type AS OBJECT (
    type VARCHAR2(20)
);

CREATE OR REPLACE TYPE extended_date AS OBJECT (
    the_date DATE,
    type date_type
);

Answer 1

I always prefer an explicit domain model - have an enum or similar KNOWN_DATE, UNKNOWN_DATE, NOT_FILLED and then a separate date column. The primary reason you might move away from this is for performance reasons, only you can determine if that's important for your use case or not.

Yes, it will be a lot of work. But there's no way round that, if it's important for your business then you need to do it.

Answer 2

There's no simple "do it that way and you'll be fine" solution.

This is the approach that I would probably take:

1. Determine the actual use cases (entering and displaying data, storing into and retrieving from the database, searching and sorting). Apparently you've done a bit of that (you list "not filled in" and "unknown" as possible exceptional values which need to be entered and displayed), but there may be more, and it is not clear how these exceptions would affect search operations which is where mapping data into database concepts can get tricky.
2. Consider whether there are plausible future extensions that you don't need to implement now but which your design should not make too difficult to implement later. Depending on the domain, you may need to represent inexact or estimated dates (genealogy software has this a lot) or dates in different calendar systems (that might be interesting for historical events in different areas of the world). Don't overthink this but also don't ignore obvious plausible extensions.
3. Map into database concepts. As you already know, SQL databases typically have NULL as the only exceptional column value, so you will likely need more than one column to represent your dates. Using an enum which tells you whether your date is regular or one of a set of exceptional values would be pretty straightforward as long as you can satisfy your requirements with it, especially search and sort operations.

The result would likely look much like what @PhilipKendall wrote, unless there are more requirements which you don't detail here.

Answer 3

It sounds like what you're essentially trying to do here is tease out the various different meanings of NULL and treat them systematically.

The problem is that there are not only a number of possible specific meanings of NULL in different contexts, but there also often remain ambiguous cases where it isn't possible to definitively categorise, or dual cases where the meaning depends on the processing being applied to the data (not on the intrinsic meaning of the data).

I use the example of a dog found injured on the street by a stranger and brought to a vet. From the vet's point of view, not only is the owner unknown, but whether the dog even has an owner is unknown (it could be a stray). If the dog has no owner, then the record of an owner is "inapplicable". If it has an owner who has not had chance to present themselves yet due to the circumstances, then the record is "missing". The problem is that these two cases are themselves ambiguous and unresolved - there is missing information about whether the owner information is missing or inapplicable. You then end up with a third "pending" case which could eventually collapse to one of the other two.

For records which withstand updates, there is also a possible distinction in some contexts between "missing-for-now" (action is being taken to seek the missing information) and "missing-permanently" (the missing condition is considered final).

You will often end up introducing a large amount of extra complexity to deal systematically with what are usually a spuriously small number of cases or relatively unimportant distinctions between unrecorded information.

If someone else has already determined a data model in which there are these distinctions and they make sense and are usable in context, then a solution to storing the different cases in SQL will often depend on desired performance and efficiency characteristics.

For example, simply storing a string value is the most flexible approach, since this can accommodate dates in string form plus any non-date special values. But if you need dates typed as dates when possible, you'll inevitably end up with at least a second field to hold an encoding of the special values which aren't part of SQL's date type.

Answer 4

A timestamp has two statuses: null and not null.

You are trying to infer three or more statuses from a datatype which can only communicate two.

What you can consider is another field which articulates the confidence of the timestamp or its absence.

Imagine a dataset more like this:

id  | time_of_death | time_of_death_confidence | translation (illustrative purposes only)
----|---------------|--------------------------|-----------------------------------------
0   | null          | unspecified              | is dead, time not specified, yet?
1   | null          | unknown                  | is dead, time forever unknown
2   | 20:34         | estimated                | is dead, estimated
3   | 10:55         | confirmed                | is dead, pinpointed
4   | 14:26         | null                     | this shouldn't happen...

Answer 5

One solution that I have seen for this in practice is to create a 'dates' reference table. Your table will have a row for each actual date used as well as special dates. This allows you to have 'unknown' (known to be unknown) and 'unspecified' (which aligns with your "not filled in"). This can also be used to support invalid dates which is useful if you have to record submitted data regardless of whether it is valid.

This complicates things a fair amount such as by introducing a lot of joins but it does allow for a fully correct data model in situations like this.

Per comments, here's a really simple (and probably naive) example of this kind of table:

key | date       | name             |
------------------------------------|
0   | null       | no value         |
1   | null       | unspecified      |
2   | null       | unknown          |
3   | 2024-10-15 | Oct 15, 2024     |
4   | 2024-10-16 | Oct 16, 2024     |

So instead of the actual date in your data table, you would have a FK constrained column pointing to the key of this table.

I want to be clear: this is not fun to work with and you shouldn't do this if you don't really need it.

Answer 6

You either know the date of birth or you don’t. You may know a partial birthday (someone was born in 1967, or their birthday is 6th of October). I may not have a birthdate for many reasons. Unknown (to the person themselves), never filled out, refused, a hospital might keep track of (yet) unborn babies, a statue has no birthdate, whatever i cannot think of.

You want one date field and one character field describing your information as soon as you have more than one kind of birthdates not in the database.

Don’t use out-of-band information. Many years ago people used 9-9-99 for non-existing birthdates, that thing is going to byte you with names, there is an actress named Rachel Null who was problems with databases all the time for obvious but stupid reasons.

Answer 7

I would challenge your domain model:

• If the information is unknown, one can't fill it in.
• If the information is not filled in, your system can't be sure if it is because the information is unknown or for another reason.

This implies that vases with "unknown" are in reality a subset of the cases "not filled in". Storing them differently makes no sense, as they are not mutually exclusive.

You may object that in your case your system is able to make the difference. But how?

• By deduction from another information? But then you already have what you need and there is no reason to create a redundant "unknown" information, that you would have to maintain in sync.
• By allowing user to specify it? then you need to foreseen here another information from which your system can deduce if it's unknown. Go to first dash ;-)

P.S: Hardcore logicians would even claim that when an information is not filled in there is a probability that it is nevertheless known and another probability that it's unknown, and if both probabilities are derived from different information sources, the total might not necessarily be 100%: welcome in the world of multivalued logic.

Answer 8

One option is to choose a specific date (e.g. 3000-01-01) which means "unknown".

This fulfils your brief but the use cases will be clumsy. Every time you use the field you will have to insert logic to say "but not those dates", in addition to any null handling.

Your code will be easier to read if you create additional field to hold this kind of special information, as suggested in the other answers.