Comment by crazygringo | Hacker News Reader

Comment by crazygringo | Hacker News Reader

This is diametrically opposed to the advice to never store booleans, but rather store enums.

Experience shows that the initial assumption of two states (false, true) often requires a third, or even fourth, fifth state etc. added down the road. (Business-logic states like "reserved", "pending", "in progress", "confirmed", "processed", etc.)

As long as these states are mutually exclusive, it's far more elegant to add another enum value rather than new fields.

So no, don't timestamp it. Stick to enumerated values rather than booleans, which will generally be of far greater benefit.

If you need to store a log of actions, then create that explicitly. Otherwise, it seems pretty silly and arbitrary to have booleans record their timestamp but not strings, integers, etc.

Edit in response to comments below: of course there are times when you need values that aren't mutually exclusive, so obviously you add another column. It's just that you very often do add another state that is mutually exclusive, and so using an enum keeps your data cleaner, more intuitive, and prevents accidental invalid combinations of booleans as well.

> As long as these states are mutually exclusive, it's far more elegant to add another enum value rather than new fields.

In my experience these states are often not mutually exclusive. Boolean encoding is a superset of enum encoding.

Additionally, enums in many programming languages are often over-strict and easy to use in a non-forwards-compatible way.

Any advice that starts with "never" or "always" is suspect advice IMO. Study your domain, and decide whether you want to lock yourself into a mutually-exclusive state space and deal with consumers who may consume it in a way that prevents adding new states. Sometimes enums make sense, sometimes they don't.

I have often found that multiple booleans are better than enums.

    processed: true
    confirmed: false
    in progress: false
    pending: false
    reserved: true

It lets you capture more complicated state, such as above where this was processed but never confirmed, but still resulted in a reservation. Maybe you have an admin portal that lets you create reservations without going through the confirmation process, and now the data can capture the difference.

But my actual preferred variant, if the tech stack can support it, is to have things like `confirmations` have their own tables, so you can have a `confirmedByUserId` as well as a confirmation timestamp.

That way you can instead have something like

    computed_processed: boolean(has a related entry in processed table)
    computed_confirmed: boolean(has a related entry in confirmation table)
   etc

The most obvious problem with that is that it allows representing impossible states, like in_progress and processed both being true.

Yep. This is basic data model design.

Correct, it is basic data model design in that it is only for handling basic data models. When you are dealing with complicated data models with many contingencies, you aren't always able to have a data model incapable of representing invalid states. Sometimes it's necessary to rely on application logic/stored procedures/index constraints, etc, in order to enforce valid state.

It's a tradeoff. If you have an explicit finite state machine in which your model can only ever be in one state, then obviously an enum has its benefits.

In order to avoid impossible states, I'll rely on something like hooks or triggers to make sure that setting one value also updates all dependent values.

In other words, setting processed to true will trigger something which always sets in_progress and pending to false.

It's not at all uncommon that I will have chosen an enum for a situation in which I thought I was dealing with a finite state machine, only to unearth new domain contingencies that made me realize that I actually need a more nuanced and flexible model. This is why I prefer booleans, particularly the computed/derived booleans when possible (as I mentioned in my original comment). I'd rather have a model capable of capturing the actual complexity of the state than trying to force a finite state machine that might result in a loss of information, even if that means that I'm forced to rely on declarative hooks/triggers to ensure data integrity.

This is feasible if your program is the only one ever reading (let alone writing) the data. When you add a separate frontend and stringly-typed back-and-forth communication (JSON), it becomes a huge headache for whichever platform(s) are not the gatekeeper of the data. They have to a) depend on your rules being modelled in a translatable way, and b) find a rational user experience for handling the inevitable inconsistencies.

> stringly-typed back-and-forth communication (JSON)

JSON, while it has a very limited set of built-in types and (outside of add-ons like JSON-schema) no type definition mechanism, isn’t “stringly-typed”.

If you have multiple applications accessing a database, you can have data integrity maintained using something like triggers in the actual DB, as well as constraints.

My experience is that status fields often end-up being a bad design.

Status is an information you display, but it's not the actual data. Status fields that contains multiple information and features in a single value creates confusing and complex logic for no good reason.

Booleans are way better to store data, and each one can bear a single different concern.

I think it can also go to a enum/timestamp pair of a state transitions table.

Reply on news.ycombinator.com