3 ms·
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
by manigandham 4y ago
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.
- sedatk 4y agoBecause 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.
- nightpool 4y agoSure, 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.
- manigandham 4y agoWhat 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.
- sedatk 4y agoThat'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.
- manigandham 4y agoAgain, 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.
- sedatk 4y agoNullability 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.
- manigandham 4y agoThat'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.
- temp2022account 4y agotime-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.
- manigandham 4y agoIt's a deleted timestamp. It's null until it's deleted. Why would there be confusion on it being null?