Comment by setr | Hacker News Reader

Comment by setr | Hacker News Reader

Parent Full thread

If you take the statement at face value — essentially storing booleans in the db ever is a bad smell - then he’s correct.

Although I’m not even sure it’s broadly a good principle, even in the on_at case; if you actually care about this kind of thing, you should be storing it properly in some kind of audit table. Switching bool to timestamp is more of a weird lazy hack that probably won’t be all that useful in practice because only a random subset of data is being tracked like that (Boolean data type definitely isn’t the deciding factor on whether it’s important enough to track update time on).

The main reason it’s even suggested is probably just that it’s “free” — you can smuggle the timestamp into your bool without an extra column — and it probably saved some effort accidentally; but not because it’s a broadly complete solution to the set of problems it tries to solve for

I’ve got the same suspicion with soft-deletes — I’m fairly positive it’s useless in practice, and is just a mentally lazy solution to avoid proper auditing. Like you definitely can’t just undelete it, and it doesn’t solve for update history, so all you’re really protecting against is accidental bulk delete caught immediately? Which is half the point of your backup

It's well documented that soft delete is more of a headache than it's worth

https://brandur.org/soft-deletion

Maybe well opined on, not well-documented.

There are clients (ORM, etc) that handle soft deletion just fine in many of the scenarios laid out in that article.

Audit tables are a big ask both in terms of programming effort to design and support them, and in terms of performance hit due to write amplification (all inserts and updates cause an additional write to an audit table). Whereas making a bool into a timestamp is free. Including timestamps on rows (including created_at and updated_at) are real bacon savers when you've deployed a bug and corrupted some rows and need to eg refund orders created in a certain window.

Audit tables are a dumb concept because they imply bolting on an actual source of truth in addition to the regular not so source of truth tables, and only if the programmer gets around to it (like documentation or logging or whatever else falls along the wayside).

This doesn't make sense to me-- if the regular tables don't capture the true state, then the audit tables based on them will not magically become a source of truth either.

Exactly. If they agree, then there was no point to introducing a second source of truth. If they disagree, then which one do you trust?

> Audit tables are a dumb concept because they imply bolting on an actual source of truth in addition to the regular not so source of truth tables,

The regular table is the source of truth, the audit table is just a historical record of what changed and when.

So what do you do if a balance has $30 and the audit table shows two deposits of $20?

The same as if you didn't have an audit table and the balance was wrong. You declare some kind of incident and investigate until you find this critical bug. Hopefully you can use the audit trail to determine what the right answer is and fix everyone's balance.

How do you know which one to trust? By reading the code and figuring out what the bug is.

If you’re recording events as intent, then the event table is the source of truth and the state of the database is effectively snapshot of the state. In principle, you should be able to replay the events to produce the current state. If it calculates wrong, go fix your calculator, assume your events table isn’t wrong, and replay.

If you’re storing a log of database changes (what’s usually meant by an audit table), as in

(Table_Name, Column_Name, Old_Value, New_Value, Timestamp)

Then the database state is the source of truth, and the audit table is simply a record of changes ever occurred that reached that state.

In either case, there’s a single source of truth.

This. The mere fact that it's much easier to find deleted/impacted entities is worth it.

> Including timestamps on rows (including created_at and updated_at) are real bacon savers when you've deployed a bug and corrupted some rows and need to eg refund orders created in a certain window.

But that’s my point. You’re making an active decision to record timestamps on important events (and no bool was being converted here); bool —> timestamp everywhere is not the same thing — the bool data type is not a useful signal for whether this change needs to be timestamp-tracked.

Either think and choose to track these particular changes or not, and drop in the appropriate tracking. The mindless Bool—>timestamp change is only ever suggested because it’s a “why not”, not because it’s a good practice that often leads to good things.

The audit table is of course just deciding “every change ever is important”

Setting a boolean is, quite often, an important event you want to keep track of. (Specifically, when it is a flag indicating an event took place, eg, once it's been set it is rarely if ever unset.) Of course it isn't universally applicable to every boolean column; nothing is. Of course you need to understand your schema and the problem you're solving. That doesn't make it mindless or make it any less valuable. It is a good pattern which does lead to good things. If it is mindless, your suggestion of tracking every change in an audit table, and the created_at and updated_at fields you approved of, are equally mindless.

As it stands, I feel I've articulated straightforward and realistic advantages of using timestamps as booleans. But I haven't heard from you why I shouldn't.

The original premise, from TFA, is

    Another is the Twitter-optimized “you’re a terrible engineer if you ever store booleans in a database” clever trick

That is, universal application. My point is: doing that, for the most part, will not the track the things you actually want, because bools are not the driving decision maker on value of tracking (and many things that need to be tracked will not be bools). It may accidentally capture useful tracking, but it’s more by accident than anything else. Thus, universal application is clearly wrong, and TFA is right to apply it conditionally.

You appear to agree on all of these points. I’m not arguing that, should you need to timetrack a bool, bool—>timestamp is a bad way to do it. As I’ve said, the universal application of it is only stated because it’s free to do. But being free doesn’t make it useful, and since you agree on that as well, I don’t know if there’s any argument actually occurring here.

I think we agree more than we disagree on how to go about designing a schema, but that my narrow disagreement is that I think a.) timestamps and enums are better choices than booleans most of the time (without judging anyone who uses booleans to be a "terrible engineer", it's not malpractice) and b.) that's a legitimate trick of the trade rather than something "mindless".

I would agree that throwing a table together based on Twitter vibes without actually designing it will only work like a stuck clock, but I think that goes without saying. I kinda disregard opinions attributed to nebulous Twitter posters and not specific people. I think when you conflate people's opinions together like that you reduce their position to a caricature. If you poll that group of people almost all of them will have a more nuanced opinion.

Reply on news.ycombinator.com