I consider booleans a code smell. It's not a bug, but it's a suggestion that I'm considering something wrong. I will probably want to replace it with something more meaningful in the future. It might be an enum, a subclass, a timestamp, refactoring, or millions of other things, but the Boolean was probably the wrong thing to do even if I don't know it yet.

The way I think about it: a boolean is usually an answer to a question about the state, not the state itself.

A light switch doesn't have an atomic state, it has a range of motion. The answer to the question "is the switch on?" is a boolean answer to a question whose input state is a range (e.g. is distance between contacts <= epsilon).

This seems at first like a controversial idea, but the more I think about it the more I like this thought technology. Merely the idea of asking myself if there's a better way to store a fact like that will potentially improve designs.

The enum idea is often wise; also: for just an example that has probably occurred a hundred thousand times across the world in various businesses...

Original design: store a row that needs to be reported to someone, with an is_reported column that is boolean.

Problem: one day for whatever reason the ReporterService turns out to need to run two of these in parallel. Maybe it's that the reporting is the last step after ingestion in a single service and we need to ingest in parallel. Maybe it's that there are too many reports to different people and the reports themselves are parallelizable (grab 5 clients, grab unreported rows that foreign key to them, report those rows... whoops sometimes two processes choose the same client!)... Maybe it's just that these are run in Kubernetes and if the report happens when you're rolling pods then the request gets retried by both the dying pod and the new pod.

Alternative to boolean: unreported and reported records both live in the `foo` table and then a trigger puts a row for any new Foos into the `foo_unreported` table. This table can now store a lock timestamp, a locker UUID, and denormalize any columns you need (client_id) to select them. The reporter UPDATEs a bunch of rows reserving them, SELECTs whatever it has successfully reserved, reports them, then DELETEs them. It reserves rows where the lock timestamp IS NULL or is less than now minus 5 minutes, and the Reporter itself runs with a 5 minute timeout. The DB will do the barest amount of locking to make sure that two UPDATES don't conflict, there is no risk of deadlock, and the Boolean has turned into whether something exists in a set or not.

A similar trick is used in the classic Python talk “Stop Writing Classes” by @jackdied where a version of The Game of Life is optimized by saying that instead of holding a big 2D array of true/false booleans on a finite gameboard, we'll hold an infinite gameboard with a set of (x,y) pairs of living cells which will internally be backed by a hashmap.

Booleans also force the true/false framing.

E.g. a field called userCannotLoginWithoutOTP.

Then in code "if not userCannotLoginWithoutOTP or otpPresent then..."

Thus may seem easy until you have a few flags to combine and check.

An enum called LoginRequirements with values Password, PasswordAndOTP is one less negation and easier to read.

For me enums win especially when you consider that you can get help from your environment every time you add/remove stuff. Some languages force you to deal with the changes (i.e. rust) or you could add linter rules for other languages. But you're more likely to catch a problem before it arises, rather than deal with ever increasing bool checks. Makes reasoning about states a lot easier.

Tangential: I was recently wishing that bitwise flags had better support in Postgres. For now, bools are just easier to work with

This is all well and good until you need to represent something that happened on Jan 1 1970 00:00 UTC.

32-bit UNIX timestamps are often signed so you can actually go before that, but most UNIX timestamps are 64-bit now, which can represent quite a larger range. And SQL datetime types might have a totally different range.

Not that it really matters; deleted_at times for your database records will rarely predate the existence of said database.

It's not about the scale, it's that `if (0)` will evaluate to `false` in many languages.

In addition to the sibling comment, which is exactly right (you should be using a nullable column here, if you're using SQL, for multiple reasons) I reckon this a design issue in the programming language that is largely unrelated to how you model the database. It's pretty easy to run into bugs especially if you compound it with other quirky APIs, like strcmp: `if (strcmp(a, b)) // forgot to do == 0; accidentally backwards!` -- So really, you just don't have much of a choice other than to tread carefully and enable compiler warnings. Personally in this case I'd use an Optional wrapper around the underlying timestamp type anyways, if I needed to be able to represent the UNIX timestamp at 0 as well as an empty state.

So you're still fine as long as you're not tracking things that were deleted on that exact instant 50 years ago, a safe assumption, for instance, for things that happened in your application that has only existed for less time than that. That said, I haven't ever seen this implemented in a way that casts. It's implemented with scopes in the ORM, usually.

    MyModel.nondeleted.where(<criteria>)

etc.

which generates a query with "WHERE deleted_at IS NULL"

1-1-1970 is fine.

Leave it null for non-deleted items.

« Anytime you store Boolean, a kitten dies » Nobody has ever said that but nobody wants any kitten to die so nobody has ever challenged me anytime I use that statement.

This one little change alone can bring such huge benefits later.