Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

You might as well timestamp it

changelog.com

Post reply on HN
Print view View original

101–110 of 205 posts

Re: You might as well timestamp it

#101

Post by whalesalad »

bob1029 wrote: post #45

I don't like this. Yes, you can alias the true/false fact to null/non-null datetime value, but this is missing the point of domain modeling. The immediate impact of this decision is probably negligible as long as you did not need to store a nullable boolean fact, as opposed to a non-nullable boolean fact. The broader impact of this decision is that you have endorsed a policy of assuming how things will be used in the…

Missing the point of domain modeling? I think you’re projecting a scenario or past experience forward on this because there is nothing about this that goes against “classical domain modeling”

At the end of the day, as far as computers go what is actually the difference between (TRUE, FALSE) and (MMDDYYTTTTTT/Null) - assuming your environment and storage engine support it.

You’re encoding far more meaning by using a date than using TRUE. Instead of two columns you now have one.

You can have a rock solid system without any room for misinterpretation and confusion while also using this approach. I do it all the time.

Re: You might as well timestamp it

#102

Post by dvfjsdhgfv »

But these are two very different use cases and just using one for another won't work in many scenarios or will make thing less efficient. It is much, much better to leave the boolean intact for the reasons other people explained and just add another column with the date if you actually need it. This will give you more flexibility while keeping things in order.

Re: You might as well timestamp it

#103

Post by alpaca128 »

davidverhasselt wrote: post #23

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…

What you describe calls for an Enum, not a nullable boolean which is just another way of passing a hardcoded magic value carrying an implicit meaning.

> a boolean column actually has 3 states, a timestamp only has 2.

Going with your logic a timestamp has billions of states, you just have to arbitrarily assign special meanings to certain dates that won't ever be used. Just like using null as another state I wouldn't call it a good idea, though.

Re: You might as well timestamp it

#104

Post by defanor »

As mentioned in other comments, there's a bunch of drawbacks with this approach, but there's a more general term "boolean blindness" [1], which is usually applied to programs (not databases). I find it useful to avoid using booleans where more descriptive types make sense (and can be used), as well as not throwing information away when it may be needed still, but to a reasonable extent.

[1] https://www.cs.cmu.edu/~15150/previous-semesters/2012-spring...

Re: You might as well timestamp it

#105

Post by gpvos »

cdirkx wrote: post #86
davidverhasselt wrote: post #23

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…

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...

Better use something so far in the past that the software certainly didn't exist yet, like 3 January 1970. In fact, you could use all of, say, the '70s to encode lots of flags... gets excited

Re: You might as well timestamp it

#106

Post by dsego »

jchw wrote: post #16

Indeed, this is a bit of wisdom I first encountered when playing with Django and seeing others do it. You can still see it in a couple of the many soft-delete packages available on PyPI, in the form of deleted or deleted_at fields with DateTimeFields. (Though admittedly, I'm pretty out of date on Django these days.) (Though it is worth noting that you sort-of get this for free if you implement a scheme with 'revision…

I've had experience with the soft delete in laravel and I'm not fond of it. Because we had a separate dashboard with manual SQL reports without an ORM to magically filter out all the deleted_at rows. And it gets tedious to remember to filter out the rows with deleted_at timestamps. I would like to see the soft-delete implemented differently, say a second trashcan table for each active table, e.g. users_archive, and the delete operation would move the row there.

Re: You might as well timestamp it

#107

Post by underwater »

davidverhasselt wrote: post #34
underwater wrote: post #31

Earlier quoted context omitted.

I'm struggling to think of an example following the OP's example - discrete events that may have not occured yet - where you'd need to differentiate between a certain `false` and an uncertain `null`.

The OP does not talk about events, OP talks about state and when it was set. There's no where in the article that OP restricts themselves to discrete events that may or may not have occured.

I'm not understanding the distinction. The only thing you get out of this schema is knowing what happened (implicitly defined by the field), whether it's happened yet, and when it happened. That feels like an event to me.

Fields where there isn't an discrete event don't work. E.g. is_dog_owner can become adopted_dog_at, but is_dog_lover can't become loved_dogs_at.

I'd actually even argue that this is not storing state directly. You derive state from knowing an event has occured in the past: deleted_at (event) => is_deleted (state).

Re: You might as well timestamp it

#108

Post by maxlybbert »

joosters wrote: post #28
davidverhasselt wrote: post #23

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…

Perl got this right decades ago with its 'undefined' status for unset variables, so you can tell the difference between false and undef

That make me think more of Javascript, which distinguishes between undefined values (usually means “not set”) and null values.

You’re correct that a Perl scalar can always be set to undef, which is the Perl name for null. But that’s not really unique to Perl. For instance, while a Java boolean can’t be null, a Java Boolean can be.

Re: You might as well timestamp it

#109

Post by isuckatcoding »

forkerenok wrote: post #92
antender wrote: post #68

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…

I think this is still workable with an expression/functional index, where the indexed expression is "happened_at IS NOT NULL".

Seems like a whole lot of extra trouble to be honest. What happens when you need to create a composite index?
Post reply on HN