Hacker News Reader

Comment by manigandham | Hacker News Reader

No need for both fields. A nullable timestamp column works fine: NULL until the record/row is deleted, at which point it holds the timestamp of when it happened.

You can derive a boolean "is it deleted" flag from that without adding another column to (mis)manage.

View on HN

Because they signify different things. For example, when you want to undo that delete operation, erasing timestamp would actually deprive you of the information that when it was deleted in the first place.

Sure, but that just kicks the can one iteration down the road, and now it's impossible to delete the record a SECOND time without "depriving" yourself of that information. If you need an audit table, use an audit table, but being able to say "deleted_at > 1.month.ago" to grab all records deleted in the past month is super useful.

What is a delete operation that can be "undone"? What is the timestamp supposed to mean in that scenario? What happens when it's deleted again then?

If you need a full record of state changes then just upgrade to an audit log, otherwise a nullable timestamp field is perfectly fine.

That's actually my main point. I edited my top comment to clarify that. A timestamp doesn't implicate "state." A bool, on the other hand, unquestionably does.

Again, the nullable nature of the column denotes both the state and when the state changed. You have more information in a single column, no need for both.

Nullability on timestamp only implies that the timestamp is optional that's all. It doesn't imply that the timestamp decides the state of the entity.

That's very strange. Database schema's don't need to be that literal, they're there to serve the business use-case for the underlying data.

A NULL `deleted_at` timestamp implies that it hasn't been deleted. That's far more simple and logical then 2 fields possibly conflicting or having an "optional" timestamp.

time-series designs are generally better because they hold more information, and you can throw a view on top of them to present a nullable timestamp table if that's what's already been coded against. On the other hand once you go nullable it's difficult to figure out why the field was nulled after the fact.

It's a deleted timestamp. It's null until it's deleted. Why would there be confusion on it being null?

Reply on news.ycombinator.com