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

181–190 of 205 posts

Re: You might as well timestamp it

#181

Post by twodave »

traceroute66 wrote: post #82
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…

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

IMO if you’re going this route, may as well just implement a snowflake clone and get the best of all worlds.

Re: You might as well timestamp it

#184

Post by tshaddox »

ralusek wrote: post #175
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 ca…

The most obvious problem with that is that it allows representing impossible states, like in_progress and processed both being true.

Re: You might as well timestamp it

#185

Post by machello13 »

tshaddox wrote: post #184
ralusek wrote: post #175

Earlier quoted context omitted.

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 ca…

The most obvious problem with that is that it allows representing impossible states, like in_progress and processed both being true.

Yep. This is basic data model design.

Re: You might as well timestamp it

#186

Post by secondcoming »

cratermoon wrote: post #165
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…

I've been in software development for 27 years, and I've never seen a case where a boolean column or variable being null was anything but a terrible, confusing, fragile, hack. Remember that in some languages the behavior of null is weird, and can be false, or can be treated as 0. $ node Welcome to Node.js v14.16.0. Type ".help" for more information. > null + null 0 > > 0 == false true > 0 === false false >

What has the ridiculousness of Javascript got to do with a database schema?

Re: You might as well timestamp it

#187

Post by sopooneo »

vbsteven wrote: post #95
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 first picked it up from Rails many years ago. Now almost every SQL table I create has created_at, updated_at and possibly deleted_at or archived_at fields. Many frameworks or ORMs have support for automatically setting updated_at. And it might even be done at SQL level but I’m not 100% sure on that.

I’ve tried that approach of moving users to a “deleted_users” table but ran into the problem of losing the foreign key references to any records pointing at the user.

Re: You might as well timestamp it

#188

Post by cratermoon »

secondcoming wrote: post #186
cratermoon wrote: post #165

Earlier quoted context omitted.

I've been in software development for 27 years, and I've never seen a case where a boolean column or variable being null was anything but a terrible, confusing, fragile, hack. Remember that in some languages the behavior of null is weird, and can be false, or can be treated as 0. $ node Welcome to Node.js v14.16.0. Type ".help" for more information. > null + null 0 > > 0 == false true > 0 === false false >

What has the ridiculousness of Javascript got to do with a database schema?

What good is a database schema if it doesn't have data and isn't used by any applications?

Re: You might as well timestamp it

#189

Post by ralusek »

tshaddox wrote: post #184
ralusek wrote: post #175

Earlier quoted context omitted.

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 ca…

The most obvious problem with that is that it allows representing impossible states, like in_progress and processed both being true.

It's a tradeoff. If you have an explicit finite state machine in which your model can only ever be in one state, then obviously an enum has its benefits.

In order to avoid impossible states, I'll rely on something like hooks or triggers to make sure that setting one value also updates all dependent values.

In other words, setting processed to true will trigger something which always sets in_progress and pending to false.

It's not at all uncommon that I will have chosen an enum for a situation in which I thought I was dealing with a finite state machine, only to unearth new domain contingencies that made me realize that I actually need a more nuanced and flexible model. This is why I prefer booleans, particularly the computed/derived booleans when possible (as I mentioned in my original comment). I'd rather have a model capable of capturing the actual complexity of the state than trying to force a finite state machine that might result in a loss of information, even if that means that I'm forced to rely on declarative hooks/triggers to ensure data integrity.

Re: You might as well timestamp it

#190

Post by ralusek »

machello13 wrote: post #185
tshaddox wrote: post #184

Earlier quoted context omitted.

The most obvious problem with that is that it allows representing impossible states, like in_progress and processed both being true.

Yep. This is basic data model design.

Correct, it is basic data model design in that it is only for handling basic data models. When you are dealing with complicated data models with many contingencies, you aren't always able to have a data model incapable of representing invalid states. Sometimes it's necessary to rely on application logic/stored procedures/index constraints, etc, in order to enforce valid state.
Post reply on HN