You might as well timestamp it • Hacker News Board

Re: You might as well timestamp it

#61

Post by bob1029 » Sat, Apr 24, 2021, 9:24 AM UTC

danielheath wrote: post #51

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…

I get that there’s a YAGNI aspect to it, but I don’t buy the argument that a timestamp adds complexity over a boolean.

This all provokes confusion over 2 different types of facts:

A) Knowledge of when a specific event occurred.

B) If something is true or not.

To use cases like "logged_in_at" as the example for why booleans shouldn't be used is essentially a strawman argument.

There are many situations in which a boolean fact does not occur in the time domain or have any possible value. Knowledge of certain facts in certain problem domains can be viewed as timeless even if they did come into being at a discrete point in time. For example, regulatory facts that govern entire industries. You probably never care when a specific regulation started to matter for a situation, just that it does or not. All these timestamps would do is confuse downstream users and bloat extracts of data.

The biggest problem of all is this statement:

> Storing timestamps instead of booleans, however, is one of those things I can go out on a limb and say it doesn’t really depend all that much. You might as well timestamp it. There are plenty of times in my career when I’ve stored a boolean and later wished I’d had a timestamp. There are zero times when I’ve stored a timestamp and regretted that decision.

There is nuance to this problem. A and B are both perfectly valid cases and each have their own representations that make the most sense. The discipline is in identifying these cases appropriately and using the correct tool for the job.

Re: You might as well timestamp it

#62

Post by victorp13 » Sat, Apr 24, 2021, 9:27 AM UTC

As the storage cost is very low, why not use both? Not trying to be obtuse, but I genuinely typically have separate columns in my schema design for booleans and the timestamps of these events flipping from false to true (published, edited). Come to think of it: These events warrant saving them to a separate table altogether; booleans represent a current state - there may be 1:n events like multiple edits.

Re: You might as well timestamp it

#63

Post by uyt » Sat, Apr 24, 2021, 9:29 AM UTC

hsbauauvhabzb wrote: post #47

Is there any concept in database engines of an audit trail which logs previous value and a time stamp? I’ve seen the concept using triggers, but never seen an engine native solution

Maybe you're thinking of "event sourcing"?

Re: You might as well timestamp it

#64

Post by bob1029 » Sat, Apr 24, 2021, 9:30 AM UTC

LaGrange wrote: post #56

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…

> are not interested in a 100% authentic modeling of the problem A model is an approximation. There is no such thing as "100% authentic modeling" for anything non-trivial, and insisting on it grows models that aren't particularly useful. They might _seem_ simple at first glance because of their "purity", but a) they're not _actually 100% accurate, and b) are usually very fragile on revision, becoming, ironically, qui…

A model turns into a gamble if you throw your hands up and claim that its impossible to have a 100% authentic model of a domain.

If you truly believe this to be the case, then you have not tread far enough into the forest of SQL, 3NF, BCNF, and the relational calculus. It is possible to use math to prove that a problem domain is modeled appropriately. With SQL and views, you can construct extremely high-order representations of domains that would otherwise be viewed as pure magic by any outside onlookers. The only way any of this becomes possible is if you have solid foundations and the courage to produce exceptionally clean models.

Yes, you will definitely screw it up a few times. We started over 4-5 times. Plan to iterate. Start with your domain modeling. You can do this shit in excel. No one gets too salty when you have to throw away a spreadsheet.

Re: You might as well timestamp it

#65

Post by progre » Sat, Apr 24, 2021, 9:36 AM UTC

ximm wrote: post #43

One important downside: Data protection. This approach of "store it now in case you might need it later" is in direct violation of the principle of data minimisation in GDPR.

GDPR only applies to personal data though? Like you can't store gender info "in case it's usefull later". I really don't see how a timestamp can be used in that way.

Re: You might as well timestamp it

#66

Post by hibbelig » Sat, Apr 24, 2021, 9:37 AM UTC

timzaman wrote: post #46

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…

"triple state boolean", what's next, a double precision fp32?

Long Boolean! Yes, no, don’t know, weekdays only.

Re: You might as well timestamp it

#67

Post by globular-toast » Sat, Apr 24, 2021, 9:37 AM UTC

hsbauauvhabzb wrote: post #47

Is there any concept in database engines of an audit trail which logs previous value and a time stamp? I’ve seen the concept using triggers, but never seen an engine native solution

You can implement an append-only database, where each record is a snapshot of the latest version and a timestamp of when it was updated.

Re: You might as well timestamp it

#68

Post by antender » Sat, Apr 24, 2021, 9:39 AM UTC

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 compressed (or stored as bitmaps), while timestamp indexes contain lots of unique values, so they can't be. This is also the reason why serial IDs are way better than UUIDs for internal IDs.

Re: You might as well timestamp it

#69

Post by watt » Sat, Apr 24, 2021, 9:40 AM UTC

tuukkah wrote: post #59

timzaman wrote: post #46

Earlier quoted context omitted.

"triple state boolean", what's next, a double precision fp32?

Also known as *bool, "Maybe Bool", "Boolean?", "Optional " etc.

yeah, but if you really need to store 3 states, why not make 1 first-class concept instead of awkwardly combining two?

Re: You might as well timestamp it

#70

Post by jpswade » Sat, Apr 24, 2021, 9:43 AM UTC

mewse wrote: post #26

jpswade wrote: post #19

I've yet to find a case where using a timestamp over a boolean hasn't been the better option. This is because turning a boolean on is an event so it'll always have a timestamp. Sometimes it's useful to know when this event happened. The only exception I can think of is if for some reason you're trying to save on bytes, which in this day and age, especially true for web applications, this is practically never the case…

I guess I’m a little confused, as this article seems to be speaking to programmers who are doing stuff that I’ve never done and in programming paradigms I’ve never used, so I’m definitely not in the target audience. But.. if I’m understanding the proposal correctly, this only gives you a timestamp if the value is ‘true’, and not if it’s ‘false’. Is that correct? Is there a reason why we care about when a boolean is t…

It’s a good question, but if you care about the audit trail, then perhaps event sourcing is a better choice.

So yes, this only assumes you care about when the on state happened and you don’t care about the history.