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

191–200 of 205 posts

Re: You might as well timestamp it

#191

Post by jchw »

dsego wrote: post #106
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 t…

The reason why deleted_at is probably done this way is due to foreign key constraints. Especially for a users table, there are probably a whole lot of things that point to the user's table. A cascading deletion could wipe out quite a bit of unintended data.

Re: You might as well timestamp it

#192

Post by traceroute66 »

twodave wrote: post #181
traceroute66 wrote: post #82

Earlier quoted context omitted.

> 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: Snowflake clone I would humbly invite you to read the note in the KSUID Github, namely:

  *To fit into a 64-bit number space, Snowflake IDs and its derivatives require coordination to avoid collisions, which significantly increases the deployment complexity and operational burden.*
Therefore KSUID remains the best option.

Re: You might as well timestamp it

#193

Post by anaerobicover »

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…

This is feasible if your program is the only one ever reading (let alone writing) the data. When you add a separate frontend and stringly-typed back-and-forth communication (JSON), it becomes a huge headache for whichever platform(s) are not the gatekeeper of the data. They have to a) depend on your rules being modelled in a translatable way, and b) find a rational user experience for handling the inevitable inconsistencies.

Re: You might as well timestamp it

#194

Post by dragonwriter »

anaerobicover wrote: post #193
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…

This is feasible if your program is the only one ever reading (let alone writing) the data. When you add a separate frontend and stringly-typed back-and-forth communication (JSON), it becomes a huge headache for whichever platform(s) are not the gatekeeper of the data. They have to a) depend on your rules being modelled in a translatable way, and b) find a rational user experience for handling the inevitable inconsis…

> stringly-typed back-and-forth communication (JSON)

JSON, while it has a very limited set of built-in types and (outside of add-ons like JSON-schema) no type definition mechanism, isn’t “stringly-typed”.

Re: You might as well timestamp it

#195

Post by ralusek »

anaerobicover wrote: post #193
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…

This is feasible if your program is the only one ever reading (let alone writing) the data. When you add a separate frontend and stringly-typed back-and-forth communication (JSON), it becomes a huge headache for whichever platform(s) are not the gatekeeper of the data. They have to a) depend on your rules being modelled in a translatable way, and b) find a rational user experience for handling the inevitable inconsis…

If you have multiple applications accessing a database, you can have data integrity maintained using something like triggers in the actual DB, as well as constraints.

Re: You might as well timestamp it

#196

Post by 867-5309 »

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 initial state is going to be false before any user input. if the user leaves a field blank, then that still counts as input, just run a check such as 'if blank then null else timestamp' on the insert

Re: You might as well timestamp it

#197

Post by dsego »

sdfhbdf wrote: post #170
dsego wrote: post #106

Earlier quoted context omitted.

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

Or you could have views with `deleted_at` filtered out: https://learnsql.com/blog/sql-view/

Oh, good point. Maybe this could be built into ORMs.

Re: You might as well timestamp it

#198

Post by cutemonster »

cratermoon wrote: post #188
secondcoming wrote: post #186

Earlier quoted context omitted.

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?

There are languages with nice optional types, which maps nicely to SQL null. Not Javascript though and I wouldn't want to use it server side.

Re: You might as well timestamp it

#199

Post by jimktrains2 »

chriswarbo wrote: post #166
jimktrains2 wrote: post #128

Earlier quoted context omitted.

Null is a signifier that a value isn't know. Yes, if you're using null to mean something else you should use another type, but nullable-booleans (and nullable fields in general) can be extremely useful. For instance, if you're collecting information, but wont know everything at creation time, the unknown values are null.

> For instance, if you're collecting information, but wont know everything at creation time, the unknown values are null. I find the idea of 'creating unknown values' to be self-contradictory. I find it much more logical to define a PartialFoo containing only the parts we know up-front, and a fill in the rest later using a function PartialFoo -> Foo. If we need multiple steps, we can put some Optional fields in the P…

> If we need multiple steps, we can put some Optional fields in the PartialFoo, to avoid lots of intermediate types.

You juat described the purpose of SQL's NULL.

Re: You might as well timestamp it

#200

Post by woutr_be »

kaeruct wrote: post #89
woutr_be wrote: post #60

Earlier quoted context omitted.

What's wrong with this? Checking if a variable is defined/null is not exactly uncommon?

In JS, if (!variable) ... coerces the type to boolean, so anything falsy (0, null, undefined, empty string, etc...) will become true. For example if you have a timestamp of 0, it will be counted as false (but is defined and definitely not null)

I understand that. But how would strictly enforced typing solve this? Especially when using TypeScript. An incorrect value being given isn’t going to be caught by the compiler.
Post reply on HN