141–150 of 205 posts
There is a downside which I've experienced: if you want a triple-state boolean (null, false, true) then having a boolean column allows for that while a timestamp-as-boolean column does not (you lose the "null" value because that equals `false` in timestamp-as-boolean). Having a distinction between `null` and `false` can be handy for values that are optional or have a dynamic default. If it's `null` you know it is not…
Earlier quoted context omitted.
That's like my washing machine which has a Normal setting, a Standard setting, a Wash setting...
Do you mean as in "If the washing machine has a Wash setting, what does it do if it is not set to Wash?"
Earlier quoted context omitted.
If the timestamp is a creation date after your system went on, which it is here, you can always store 0 for false.
-1 can be useful, too, especially if you've already assigned a certain meaning to 0. Javascript developers are used to certain functions returning -1 if there's no match, so -1 shouldn't feel strange as long as it's well documented.
There is a downside which I've experienced: if you want a triple-state boolean (null, false, true) then having a boolean column allows for that while a timestamp-as-boolean column does not (you lose the "null" value because that equals `false` in timestamp-as-boolean). Having a distinction between `null` and `false` can be handy for values that are optional or have a dynamic default. If it's `null` you know it is not…
Earlier quoted context omitted.
Also known as *bool, "Maybe Bool", "Boolean?", "Optional " etc.
Well, in browsers there's `HTMLMediaElement.canPlayType()` which returns one of the following strings: - "probably" - "maybe" - "" Reference: https://developer.mozilla.org/en-US/docs/Web/API/HTMLMediaEl...
AFAIR I just said fuck it and made playback as permissive as possible (i.e., only prevent media playback if canPlayType returned ""). I don't know how advisable that is but the bug got off my back anyway.
I dunno what makes this so difficult, why we can't get at least a definite "yes" even in 2021.
Earlier quoted context omitted.
The obvious solution is to encode it like this: `null` is `null, `yes` is the timestamp when it was set and `false` is a timestamp somewhere in the future. Now t You just have to pick your `false` timestamp somewhere far into the future, let's say something arbitrary like 03:14:07 on Tuesday, 19 January 2038. The software won't be around for that long anyway, so it will never be a problem...
Hehe, that's creative. But I'd say - if you're making it that complex, just use a boolean column and a timestamp column separately instead, if you really need the timestamp, and make it explicit and simple to understand.
Earlier quoted context omitted.
Tri-state booleans are also very ugly. What happens if you suddenly need a 4th state? Use enums (or any equivalent) for states that are non-boolean.
If you are using a nullable bool, you do not need a fourth state. True, False, Unset. Very useful in a lot of cases. It's actually not ugly but very elegant.
If you're going this route, it's hard to understand why you wouldn't just store all the information you want explicitly in a string. "true [timestamp]" "false [timestamp]" "unset [timestamp]" That's more information than described in the article and it's easier for future you to understand what's going on, without implicit assumptions on the meaning of an undefined variable. Furthermore, you can keep a complete recor…
This makes querying harder. You cannot use “is not null” or “= true” for where clauses with this. You’ll need to parse the string value. When going the explicit route I would recommend an is_archived (nullable) bool column combined with an archived_at timestamp column.
There is one thing in favor of storing the full history in a single string - you might not query the full history very often, and you can keep a lot of information around without adding another table. I've occasionally stored the full history of objects (short notes mostly) in an sqlite database as a string in json form. Pretty convenient if you're keeping it there just in case you want to go back in time and don't make a large number of changes.
Also, from the point of query optimisation this is a really bad idea. Usually you DO actually care about size of fields in SQL databases, because something like BOOLEAN is usually stored as single byte (or bit in a bitfield) vs 4 bytes or even 8 in case of timestamp. This not only multiplies on disk usage by at least 4 times, but also makes ALL indexes using this field way bigger. Also boolean indexes can be compress…
> This is also the reason why serial IDs are way better than UUIDs for internal IDs. There are three core problems with that: a) Serial IDs are a nightmare for database merges, clustering or anything like that b) Serial IDs won't scale c) Serial IDs require management, whilst UUIDs can be produced anywhere (in DB, in frontend etc) There is the KSUID[1] if people want a time-sortable thing that is near-enough to a UUI…
You mean scaling into several machines? Yes, they do scale. Nothing requires that the values are always increasing and have no holes, so you can slice and cluster them at will (and many DBMS do exactly that).
I'm not really down with assigning meaning to NULL. NULL means "unknown", full stop. This is why SQL doesn't like if you compare to NULL using equality, because nothing "equals" NULL. Similarly wouldn't I want to know when a true value became false? This post seemed very strange in that regard. Count me in as storing a boolean as a boolean (or as mentioned elsewhere, an enumeration) and if I need auditing on that, th…
Hum... Null means whatever the data design says it means. We are talking about mathematics here, not religion. Rules don't come written in stone from the havens.
Using it as "not applicable" is even way more common than "unknown".