undefined | Better HN

0 pointsmaxbond10mo ago0 comments

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.

0 comments

9 comments · 3 top-level

mrkeen10mo ago· 4 in thread

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).

tremon10mo ago

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.

mrkeen10mo ago

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?

RaftPeople10mo ago

> 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.

mrkeen10mo ago

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

2 more replies

setr10mo ago· 2 in thread

> 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”

maxbondOP10mo ago

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.

setr10mo ago

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.

1 more reply

valenterry10mo ago

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

j / k navigate · click thread line to collapse

0 comments

9 comments · 3 top-level

mrkeen10mo ago· 4 in thread

tremon10mo ago

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.

mrkeen10mo ago

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?

RaftPeople10mo ago

> 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.

mrkeen10mo ago

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

2 more replies

setr10mo ago· 2 in thread

> 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.

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

maxbondOP10mo ago

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.

setr10mo ago

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

1 more reply

valenterry10mo ago

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

j / k navigate · click thread line to collapse