4 ms·
> You’re supposed to store timestamps instead, and treat the presence of a timestamp as true. I do this sometimes but not always - in my view there’s some value
by ZYbCRq22HbJ2y7 1y ago
> You’re supposed to store timestamps instead, and treat the presence of a timestamp as true. I do this sometimes but not always - in my view there’s some value in keeping a database schema immediately-readable.
Seems overly negative of broad advice on a good pattern?
is_on => true
on_at => 1023030
Sure, that makes sense.
is_a_bear => true
a_bear_at => 12312231231
Not so much, as most bears do not become bears at some point after not being a bear.
- setr 1y agoIf 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
- maxbond 1y agoAudit 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.
- valenterry 1y agoThis. The mere fact that it's much easier to find deleted/impacted entities is worth it.
- mrkeen 1y agoAudit 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).
- tremon 1y agoThis 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.
- mrkeen 1y agoExactly. If they agree, then there was no point to introducing a second source of truth. If they disagree, then which one do you trust?
- RaftPeople 1y 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.
- mrkeen 1y agoSo what do you do if a balance has $30 and the audit table shows two deposits of $20?
- maxbond 1y agoThe 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.
- moebrowne 1y agoIt's well documented that soft delete is more of a headache than it's worth https://brandur.org/soft-deletion https://brandur.org/soft-deletion
- ZYbCRq22HbJ2y7 1y agoMaybe 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.
- seafoamteal 1y agoI think in that situation, you could have an enum value that contains Bear and whatever other categories you are looking at.
- ZYbCRq22HbJ2y7 1y agoSure, but this was for demonstration purposes showing that some data has other meaning that doesn't have an instantiation state dependent on time.
- spiddy 1y agothough why treat booleans as special case and keep timestamps for them when you don’t for integers with this pattern: isDarkTheme: {timestamped} paginationItems: 50 I can see when dark theme was activated but not when pagination was set to 50. also, i can’t see when dark theme is being deactivated either. seems like a poor-man changelog. there maybe use cases for it but i can’t think of anything tbh.
- Lionga 1y agoAll this general advice is quite useless and needs millions of asterix. Good system design is designing a system that works best for the problem at hand.
- FrankChalmers 1y agoThat's even more general and requires another million asterisks.
- urquhartfe 1y agoThis is an utterly fatuous statement
- oftenwrong 1y agoGood system design is designing a system that works good.
- grey-area 1y agoI’d see the booleans as a bad thing in almost all cases, instead of a boolean you can have a timestamp or an integer field (which can expand later). In the is_a case almost always a type or kind is better as you’ll rarely just have bears even if you only start with bears, just as you rarely have just two states for a status field (say on or off), often these expand in use to include things like suspended, deleted and asleep. So generally I’d avoid booleans as they tend to multiply and increase complexity partially when they cover mutually exclusive states like live, deleted and suspended. I have seen is_visible, is_deleted and is_suspended all on the same table (without a status) and the resulting code and queries are not pretty. I’d use an integer rather than a timestamp to replace them though.
- ZYbCRq22HbJ2y7 1y agoYeah, I mean, an integer can definitely hold more data than a boolean. If your data was simple enough, you could have an integer hold the entire meaning of a table's row, if every client understood how it was interpreted. You could do bitwise manipulations, encodings, and so on. Sometimes it is nice to understand what the data means in the schema alone. You can do that with enums, etc. ate_an_apple_in_may_2024 saw_an_eclipse_before_30 These are more of the sort of things I don't see needing enums, timestamps, integers...
- grey-area 1y agoI’ve never seen anything remotely like that in a schema and it seems inappropriate anyway - those should IMO be timestamps like saw_eclipse_at not booleans. You should not encode business rules in the schema (like certain magic dates) because those business rules always change over time.
- ZYbCRq22HbJ2y7 1y agoYeah, need a bit of imagination to walk there with me, but you are saying just use `saw_eclipse_at`. That would require having knowledge of the entity's birthday, the date that the event occurred on, and so on, which in this imaginary scenario, we do not.
- oftenwrong 1y agoA boolean is smaller, which is a relevant consideration for some workloads. For example, you may be pre-aggregating a large amount of data to serve a set of analytical queries which do not care about the associated timestamp. The smaller data type is more efficient both in storage and in query execution. Additionally, there are situations where it is logical to store a boolean. For example, if the boolean denotes an outcome: process_executed_at timestamp not null process_succeeded boolean not null
- maxbond 1y agoIt's unlikely the boolean will result in better utilization, the savings will probably be consumed by padding. Most people don't know how to use structure packing to create a row which is actually smaller after it's been padded (though it's not very hard, anyone could learn). Columns are generally ordered by which features were shipped first and not by alignment (as is necessary to minimize padding). I do try my best to pack my columns, but it's a fragile and likely premature optimization. Better to opt for something defensive at a cost of like, 7 bytes per row (in Postgres).