You might as well timestamp it • Hacker News Board

Re: You might as well timestamp it

#171

Post by d0100 » Sat, Apr 24, 2021, 4:18 PM UTC

vbsteven wrote: post #99

bachmeier wrote: post #50

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.

At this point just create a archived_resources table where you store the resource id, timestamp and user id

Re: You might as well timestamp it

#172

Post by marcosdumay » Sat, Apr 24, 2021, 4:20 PM UTC

gmfawcett wrote: post #163

marcosdumay wrote: post #149

Earlier quoted context omitted.

> Serial IDs won't scale 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 confused? Serial means "always increasing and having no holes" -- it's one thing after the other. What you're arguing for sounds like just IDs, not serial IDs.

Well, by your definition what people use as serial IDs isn't serial.

Databases have the concept of sequences, that are closer to your definition, but make no promises about holes (they normally don't generate holes by themselves, but there is no way to guarantee you won't lose numbers upon usage). It is common to use sequences to feed serial IDs, but not all DBMS do that and it's not a requirement in any way.

Re: You might as well timestamp it

#173

Post by cle » Sat, Apr 24, 2021, 4:21 PM UTC

crazygringo wrote: post #168

This is diametrically opposed to the advice to never store booleans, but rather store enums. Experience shows that the initial assumption of two states (false, true) often requires a third, or even fourth, fifth state etc. added down the road. (Business-logic states like "reserved", "pending", "in progress", "confirmed", "processed", etc.) As long as these states are mutually exclusive, it's far more elegant to add a…

> As long as these states are mutually exclusive, it's far more elegant to add another enum value rather than new fields.

In my experience these states are often not mutually exclusive. Boolean encoding is a superset of enum encoding.

Additionally, enums in many programming languages are often over-strict and easy to use in a non-forwards-compatible way.

Any advice that starts with "never" or "always" is suspect advice IMO. Study your domain, and decide whether you want to lock yourself into a mutually-exclusive state space and deal with consumers who may consume it in a way that prevents adding new states. Sometimes enums make sense, sometimes they don't.

Re: You might as well timestamp it

#174

Post by dorgo » Sat, Apr 24, 2021, 4:28 PM UTC

kijin wrote: post #74

BiteCode_dev wrote: post #54

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.

I wonder why they decided to use -1. Why not undefined or NaN or even null? The -1 produces bugs which go unnoticed for a long time.

Re: You might as well timestamp it

#175

Post by ralusek » Sat, Apr 24, 2021, 4:35 PM UTC

crazygringo wrote: post #168

This is diametrically opposed to the advice to never store booleans, but rather store enums. Experience shows that the initial assumption of two states (false, true) often requires a third, or even fourth, fifth state etc. added down the road. (Business-logic states like "reserved", "pending", "in progress", "confirmed", "processed", etc.) As long as these states are mutually exclusive, it's far more elegant to add a…

I have often found that multiple booleans are better than enums.

    processed: true
    confirmed: false
    in progress: false
    pending: false
    reserved: true

It lets you capture more complicated state, such as above where this was processed but never confirmed, but still resulted in a reservation. Maybe you have an admin portal that lets you create reservations without going through the confirmation process, and now the data can capture the difference.

But my actual preferred variant, if the tech stack can support it, is to have things like `confirmations` have their own tables, so you can have a `confirmedByUserId` as well as a confirmation timestamp.

That way you can instead have something like

    computed_processed: boolean(has a related entry in processed table)
    computed_confirmed: boolean(has a related entry in confirmation table)
   etc

Re: You might as well timestamp it

#176

Post by diminish » Sat, Apr 24, 2021, 4:36 PM UTC

crazygringo wrote: post #168

This is diametrically opposed to the advice to never store booleans, but rather store enums. Experience shows that the initial assumption of two states (false, true) often requires a third, or even fourth, fifth state etc. added down the road. (Business-logic states like "reserved", "pending", "in progress", "confirmed", "processed", etc.) As long as these states are mutually exclusive, it's far more elegant to add a…

I think it can also go to a enum/timestamp pair of a state transitions table.

Re: You might as well timestamp it

#177

Post by thih9 » Sat, Apr 24, 2021, 4:41 PM UTC

Note that this doesn’t keep historical values.

E.g.: If we convert ‘synced’ boolean to ‘synced_at’ timestamp and if the sync status changes often, we’re only storing the most recent sync timestamp.

In some cases this might be insufficient.

Re: You might as well timestamp it

#178

Post by Seb-C » Sat, Apr 24, 2021, 4:46 PM UTC

crazygringo wrote: post #168

This is diametrically opposed to the advice to never store booleans, but rather store enums. Experience shows that the initial assumption of two states (false, true) often requires a third, or even fourth, fifth state etc. added down the road. (Business-logic states like "reserved", "pending", "in progress", "confirmed", "processed", etc.) As long as these states are mutually exclusive, it's far more elegant to add a…

My experience is that status fields often end-up being a bad design.

Status is an information you display, but it's not the actual data. Status fields that contains multiple information and features in a single value creates confusing and complex logic for no good reason.

Booleans are way better to store data, and each one can bear a single different concern.

Re: You might as well timestamp it

#179

Post by jeroenhd » Sat, Apr 24, 2021, 4:51 PM UTC

themulticaster wrote: post #143

kijin wrote: post #74

Earlier quoted context omitted.

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

On the other hand, there's strcmp()/strncmp(). I can never wrap my head around the fact that strcmp(s1, s2) == 0 (or even better, !strcmp(s1, s2)) means s1 is equal to s2.

If you look at strcmp like "the difference when you compare the strings for sorting", it makes complete sense. If the first mismatch of strcmp(a, b) is smaller than 0, you put the a before b, otherwise you put b before a.

The fact that an equality check can be made by running a comparison function is useful, but that's not all the method does.

For other methods in much C code, a common mindset is that a method returning a value will return the error code, with error code details in errno. The error code for success is 0, which is fitting of course.

Had C implemented booleans, this problem would never have been a problem, because if(int) wouldn't have been a legal expression, but sadly booleans are implemented as integers in the language instead. I strongly dislike languages that do allow implicit casting from integers and such to a boolean, if(var!=0) is much more readable because of the explicit boolean expression.

Re: You might as well timestamp it

#180

Post by skybrian » Sat, Apr 24, 2021, 5:21 PM UTC

davnicwil wrote: post #7

This is right. I'm a big fan of this sort of embedded audit metadata wherever it makes sense. I do wonder when doing stuff like this though, if this really shouldn't be something that the database gives you for free. I read a few years ago about 'fact based' event stream style databases which store your data as a stream of time ordered ops that can later serve as an audit log, but can be used for even more powerful t…

I’ve been following the progress of Dolt [1] which is a SQL database that works like git. This would give you modification history in a similar way to git. That’s different from recording when events happened, though (and changing your mind about when they happened), so you’ll still need timestamps for that.

[1] https://github.com/dolthub/dolt