4 ms·

Numeric data may be missing for any number of reasons. Storing the reason, as opposed to just null, could be useful.

tinalumfoil 5y ago
I'm skeptical of using different types in a dynamically typed language (or db) as a tagged union where each type has a different meaning. IMHO youre better off with two columns with a constraint that exactly one of them must be Null. The issue is someone at some point is going to write code reasonably assuming the value must be an integer, not a string, and when the value turns out to be a string you'll get unpredictable behavior.
jimktrains2 5y ago
That sounds like a nightmare to deal with. You can't use any aggregate as it's either not well defined (e.g. sum) or will be wrong (e.g. count). Kust put the reason in another column if it's needed.
anyfoo 5y ago
Creating a mapping between primitive data type and "kind of content" is ad-hoc, arbitrary, and icky. What if you want your reasons to be numeric as well, then the scheme falls apart? What about aggregates, as a sibling comment mentioned? Copy of another comment of mine: I dealt with a large sqlite database once where a past bug had entered data of a different data type. Instead of blowing up when the rows were created or modified, it blowed up in a much harder to debug way much, much later.
arthurcolle 5y ago
Courageous to post this here, but I emphatically disagree. Any aggregation operation will effectively have undefined behavior. You could make it like a spreadsheet where cells have both numeric data (either static or derived as in the case of a cell formula that sums things up, as an example) or functions, but if you want row/cell metadata per entry then that should be a new database type or some kind of extension. If I have a column that's defined (lol... hinted) as an INT then if I see a STRING or TEXT in one of the rows I'm gonna be pretty pissed. But it's ok, at least this is an exciting novelty to observe and shake one's head at.

I'm skeptical of using different types in a dynamically typed language (or db) as a tagged union where each type has a different meaning. IMHO youre better off with two columns with a constraint that exactly one of them must be Null. The issue is someone at some point is going to write code reasonably assuming the value must be an integer, not a string, and when the value turns out to be a string you'll get unpredictable behavior.

jimktrains2 5y ago

That sounds like a nightmare to deal with. You can't use any aggregate as it's either not well defined (e.g. sum) or will be wrong (e.g. count). Kust put the reason in another column if it's needed.

anyfoo 5y ago

Creating a mapping between primitive data type and "kind of content" is ad-hoc, arbitrary, and icky. What if you want your reasons to be numeric as well, then the scheme falls apart? What about aggregates, as a sibling comment mentioned? Copy of another comment of mine: I dealt with a large sqlite database once where a past bug had entered data of a different data type. Instead of blowing up when the rows were created or modified, it blowed up in a much harder to debug way much, much later.

arthurcolle 5y ago

Courageous to post this here, but I emphatically disagree. Any aggregation operation will effectively have undefined behavior. You could make it like a spreadsheet where cells have both numeric data (either static or derived as in the case of a cell formula that sums things up, as an example) or functions, but if you want row/cell metadata per entry then that should be a new database type or some kind of extension. If I have a column that's defined (lol... hinted) as an INT then if I see a STRING or TEXT in one of the rows I'm gonna be pretty pissed. But it's ok, at least this is an exciting novelty to observe and shake one's head at.