Any trick you play with a fixed number of scalar columns will only let you access the timestamp of the last change of the same type. This won't be particularly useful when there's an edit war among moderators who unpublish and republish the same thing over and over.
A) Knowledge of when a specific event occurred.
B) If something is true or not.
To use cases like "logged_in_at" as the example for why booleans shouldn't be used is essentially a strawman argument.
There are many situations in which a boolean fact does not occur in the time domain or have any possible value. Knowledge of certain facts in certain problem domains can be viewed as timeless even if they did come into being at a discrete point in time. For example, regulatory facts that govern entire industries. You probably never care when a specific regulation started to matter for a situation, just that it does or not. All these timestamps would do is confuse downstream users and bloat extracts of data.
The biggest problem of all is this statement:
> Storing timestamps instead of booleans, however, is one of those things I can go out on a limb and say it doesn’t really depend all that much. You might as well timestamp it. There are plenty of times in my career when I’ve stored a boolean and later wished I’d had a timestamp. There are zero times when I’ve stored a timestamp and regretted that decision.
There is nuance to this problem. A and B are both perfectly valid cases and each have their own representations that make the most sense. The discipline is in identifying these cases appropriately and using the correct tool for the job.
Of course there are nuances and exceptions to every generalization. OP seems to be saying that most boolean flags we care about on a day-to-day basis can benefit from being placed in a time domain. If you're aware of an exception, you can just point it out. There's no need to dismiss the entire argument.
Returning to your example, you probably do care when a certain regulation started to apply to a certain industry, because factories built and products sold before that date may not comply, and may not even be required to comply, with said regulation. You probably also care when it stops applying. Laws are not rules of nature; they change all the time and often come with expiry dates. If you store every regulation with a few timestamps, it's going to be pretty straightforward to find out which entities need to be in compliance but currently aren't, etc.
Besides, the cost of keeping a few unnecessary integer columns around in your database is often negligible compared to the cost of updating the schema and notifying everyone who uses your API several years down the road when you realize that you need it after all. Disk is cheap. RAM is cheap. CPU is cheap. Updating enterprise software is not.
- "Jan 23, 2020"
Sure, I mean. It's Javascript after all.
A model is an approximation. There is no such thing as "100% authentic modeling" for anything non-trivial, and insisting on it grows models that aren't particularly useful. They might _seem_ simple at first glance because of their "purity", but a) they're not _actually 100% accurate, and b) are usually very fragile on revision, becoming, ironically, quite complex.
> "booleans go in as booleans, if you want who, when, why, those are 3 new facts next to the boolean".
...you have now replicated the information 3 times in a way that includes risk of going out of sync. Congratulations.
If you truly believe this to be the case, then you have not tread far enough into the forest of SQL, 3NF, BCNF, and the relational calculus. It is possible to use math to prove that a problem domain is modeled appropriately. With SQL and views, you can construct extremely high-order representations of domains that would otherwise be viewed as pure magic by any outside onlookers. The only way any of this becomes possible is if you have solid foundations and the courage to produce exceptionally clean models.
Yes, you will definitely screw it up a few times. We started over 4-5 times. Plan to iterate. Start with your domain modeling. You can do this shit in excel. No one gets too salty when you have to throw away a spreadsheet.
...no it doesn't. I've deleted the claim that you don't know what a model is from the previous comment, but now it's clear: you don't know what a model is.
> If you truly believe this to be the case, then you have not tread far enough into the forest of SQL, 3NF, BCNF, and the relational calculus.
...you're mixing models with mathematical theories in there, and none of these are "100% authentic models" of anything. Unless by authentic you mean "not copied", I suppose.
> It is possible to use math to prove that a problem domain is modeled appropriately.
...appropriately doesn't mean 100% accurate (which is _I think_ the word you were looking for). That's not how models work.
> The only way any of this becomes possible is if you have solid foundations and the courage to produce exceptionally clean models.
"Clean" doesn't imply "accurate" at all. It does sometimes help with being useful or easy to implement, but it usually _sacrifices_ accuracy. Like the two main and often contrary properties of models are "accuracy" and "usefulness." That's, like, first five pages of any epistemology handbook.
> You can do this shit in excel.
If you start in excel, you ain't getting anywhere. And this is not, by the way, a dunk on Excel.
At the end of the day, as far as computers go what is actually the difference between (TRUE, FALSE) and (MMDDYYTTTTTT/Null) - assuming your environment and storage engine support it.
You’re encoding far more meaning by using a date than using TRUE. Instead of two columns you now have one.
You can have a rock solid system without any room for misinterpretation and confusion while also using this approach. I do it all the time.
The date something occurs in the real-world can be different from when the data entry was done. If you blindly convert Booleans into timestamps without that differentiation, you’ll end up with misleading data.
The immediate impact of this decision is probably negligible as long as you did not need to store a nullable boolean fact, as opposed to a non-nullable boolean fact.
The broader impact of this decision is that you have endorsed a policy of assuming how things will be used in the future and are not interested in a 100% authentic modeling of the problem domain anymore. In a larger team, these "well wouldn't it be nice if..." design decisions are extremely subjective and can beg many further questions that wind up being distracting.
Discipline becomes very important as the complexity of your software project increases. It is easy to collapse the whole house of cards over little incremental things like this. You have to have a stricter policy across the entire team of saying things like "booleans go in as booleans, if you want who, when, why, those are 3 new facts next to the boolean".