Hacker News Reader

Comment by sedatk | Hacker News Reader

If you need to hold timestamp, hold `deleted` and `deleted_at` together.

If you can't add a separate field, just add a log table.

Don't optimize for fictional requirements that haven't surfaced, and don't add shortcuts.

EDIT: Let me clarify my point here. A timestamp is just a timestamp. Giving a timestamp a role on signifying the state of an entity is a semantic shortcut, and semantic shortcuts lead to problems. I believe that DB structures should be as clear as possible. For example, it's impossible to know whether a timestamp is the authority on an entity's state by looking at a table structure. But a boolean field like "is_deleted" or "deleted" doesn't have that problem.

View on HN

> For example, it's impossible to know whether a timestamp is the authority on an entity's state by looking at a table structure

This is just a matter of convention—if your codebase is consistent about it and there's no other option for "is this record deleted?", then yes it will be absolutely clear that "deleted_at" is the right column to use.

What's actually worse for making the database structure clear is to have "vestigial" columns that store old data, like you proposed for "deleted" and "deleted_at". Now, if they disagree, which one should you trust? Maybe some code is using one, while some code is using the other. Having a single source-of-truth column—no matter what the name is—is always going to be better then having two columns that could get out of sync or disagree.

Log tables are a super good idea in a lot of cases. And if your DB support it add a JSON column to that log table. Being able to dump either stricter data from our own logging or the reponcess from third part APIs in invaluable. You never know in advance what is going to be useful.

If you start generating too much data, add a cron job to purge older items or scale back the logging.

An example from our e-commerce platform, the "order/cart" tables have corresponding log table that log all changes, user interactions, and all requests/responses from Stripe during the checkout process.

It's super useful both for tracking down bugs, but also confirming what happed when customers contact support.

Yeah I much prefer keeping the deleted column as a boolean and then having a log table. I've done the timestamps instead of boolean thing before and it leads to two problems:

1. What happens when the record is "un-deleted?" Now the timestamp is null again and history has been lost. It's not the end of the world but if you're asked to create an account history report it's an awkward conversation. "I thought we tracked when records were deleted..."

2. Similar to the end of the last problem, it creates an expectation that the times that changes are made are tracked but it doesn't quite live up to that expectation. I've learned that business people love a feature that works well (like a log table), they can deal with a feature not existing at all (like a boolean and no logging), but a half-working feature causes a lot of suffering.

Just do logging right from the start if you are writing business software where the data and transaction volume are small compared to the value of the data.

Nullable timestamp columns (64-bit integers) are what I prefer.

I don't know how much of a performance improvement I'd get if I had a separate indexed bool column for tracking just the truthiness, but I do know that it probably wouldn't be enough to matter, even if SQL was a meaningful bottleneck.

I really enjoy working with these as nullable DateTime in .NET as well:

  if (!record.CompletedUtc.HasValue) { /* completion logic */ }

One source of truth also means only 1 thing to update each time. It makes many kinds of problems impossible.

Indexing a 64 bit versus a 1 bit column actually sounds like a potentially really good win to make the index much much smaller, which could help loads with keeping it in hot memory or cache.

On the other hand, on postgres & maybe others, one could perhaps index an expression deleted_at is not null, which would let one have the data shape they want while letting the index shape suit the typical access pattern.

Make deleted_at nullable and put an index on it.

An audit log table isn't a bad idea, though. You can capture rich entity edit histories, attribution, etc.

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.

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?

I think you should have log table anyway because if you need that information storing it as column(s) next to the rest it’s not going to work well.

anyways i prefer to have nullable timeshamp columns instead of two like you suggested.

Reply on news.ycombinator.com