3 ms·
A more specific (and so hopefully less arbitrary) claim for you all: In CRUD apps, database tables with lots of boolean columns are a data-architecture smell.
by derefr 4y ago
A more specific (and so hopefully less arbitrary) claim for you all:
In CRUD apps, database tables with lots of boolean columns are a data-architecture smell.
In my experience, the model encoded by such a record is almost always a — perhaps implicit — finite state machine of some kind. Usually, in CRUD stuff, this is a "lifecycle state" machine. (For example, the BlogComment lifecycle: draft → awaiting moderation → visible → deleted.)
But, because the people who create and evolve data schemas aren't usually familiar with FSMs, there's often no explicit "lifecycle state" enum-typed column in the DB record.
Instead, you get this pile of boolean columns, where each column represents the answer to a predicate that would take the model's lifecycle state as its input. Think e.g. `can_login?(User)` — which really just boils down to `can_login?(UserLifecycleState)`.
If you add the lifecycle-state column, then all the predicate-answer columns can go away. As long as you formally define+document the meaning of each lifecycle state, you can then statelessly recompute all those predicate-answers any time you like, on any layer of the stack you like. And now those predicate-answers will never be able to end up in an incoherent state, because they're never persisted; only the lifecycle-state itself is.
Of course, if you actually go all the way and make your model an FSM on the business layer as well, then you'll also get as a free benefit, the ability to validate lifecycle-state transitions — e.g. "Articles should only be able to enter the Published state from the Reviewed state."
- smegsicle 4y agoyeah nice, way more coherent than that article seemed
- onion2k 4y agoA flag is just a memoized answer to the predicate though.
- derefr 4y agoIf you also explicitly store the FSM state itself, then sure, that's true. But in my experience, in almost all of these cases, the flags are stored in lieu of an FSM state; with the current FSM state implicit as a constraint-solution over the current set of predicate-answers. Which allows for incoherent states, because no FSM is actually being modelled in the business logic — even though the underlying business process the code is supposed to be modelling is inherently one involving state transitions.
- tempodox 4y agoThe risk is that if you have mutually exclusive predicates, a memoized/persisted flag set might be contradictory. You'd need extra code to check that this is not the case. It's just safer to make invalid states unrepresentable.
- eurasiantiger 4y agoIt actually makes code a bit cleaner if data has boolean descriptors instead of enums for describing group or partial identity. Easier to check for thing.isSomething than thing.group === someArbitraryValue. Just don’t allow them to enter illegal states or depend on other state. :)
- derefr 4y agoBut why not `thing.isSomething()` that's just a pure function defined as `return thing.state in STATES_THAT_ARE_SOMETHING`?
- eurasiantiger 4y agoHow do you store that in the database? That’s an ORM-level solution, which just hides/black-boxes the ugly code. Also, I already specified ”no illegal state”, which could just as well be ”no state”.
- derefr 4y ago> How do you store that in the database? 1. A very small dimension table, defining the set of flags in terms of the current state: (user_lifecycle_state, is_a, is_b, is_c, ...). Join it to the fact table when you need it. 2. Computed columns in a writable view wrapping the table. 3. Procedures/functions that take the lifecycle state as input, so you can `SELECT u.id, ..., is_active(u.lifecycle_state) AS is_active FROM users`. > which just hides/black-boxes the ugly code I don't see what's ugly about it. The point of defining something as a FSM is that that's a formal definition on the design level, separate from any particular implementation; you can write a single stateless module that defines these predicates in terms of the lifecycle-states on any or all layers of the stack they're needed, and they'll all work the same, because they're all working off a formal definition of the meaning of each state. Compare and contrast: Postgres has an `inet` type for IP addresses. It has a function `family(inet)`, which returns 4 or 6. Your business layer's runtime probably also has a function like that somewhere. They won't necessarily have the same outputs (i.e. your business layer's version might be a pair of predicates `is_ipv4?` and `is_ipv6?`) — but they'll both answer the same way semantically given the same inputs, because they're both working off of a shared formal definition of what an IP address is.
- gentleman11 4y agoI use data oriented design a bit, and also fsms constantly. I feel like fsms contract dods intentions a little: dod likes to imagine that every function just has a bunch of data it iterates over and that the logic doesn’t have to worry about application state that way. However, that is never true, and you end up adding application state variables all over your data tables so your fsms or other techniques can figure what step in a multi step process they are at for that element. At that point, it feels more and more like oop but without any encapsulation and the paradigm is starting to give me doubts. Honestly curious about this
- cryptonector 4y agoIt starts with one flag. A couple of years later, developments you didn't forecast have led you to adding two more flags, and now you suddenly see you need seven more and wish you'd used an enum. This happens. It's not code smell. It's failure to forecast. An enum for one flag can be silly. Five flags instead of an enum is also silly. The latter doesn't just happen because the programmer didn't know about enums -- it happens organically over time.
- derefr 4y agoWhat's not smelly about it? It doesn't smell of inexperience, sure; but it certainly smells like it needs a refactoring schema-migration to introduce that explicit FSM-state column.
- cryptonector 4y agoYes.