Several months ago I read a blog post suggesting the practice of always storing a timestamp rather than a boolean. It’s a short post; you should go read it, but the key quote is as follows:
Why is that? Because any time a piece of data is important enough to store its truth/falsehood in your database, it’s likely important enough that you’ll eventually want to know when that state was set. It’s like the exact opposite of YAGNI. You Ain’t Gonna Regret It? 🤔
However, this week I was going to store a timestamp, and almost immediately I decided it was a bad idea, changed my mind, and decided to store a boolean instead. The reason in this case has nothing to do with performance; rather there is an element of transparency to the user and therefore potential confusion involved, and a boolean is simply more intuitive.
Background
There’s a database table called TeamRosterPhotos. Rows in this table represent photos of team rosters (the table is very descriptively named); they’re sorted either by SortDate or a join to Tournaments and the date of the tournament from that table. Team pages display the most recent roster photo if a photo for that team exists, and if no photo is found, they display nothing.
What was missing was a mechanism to hide a roster photo altogether (i.e. to act as if no photo exists) if the roster has changed too much since the last available photo; for example, if a team drops out of a premier league and there are no more roster photos provided, we want the roster photo section to be empty rather than outdated.
The new field
Originally, I was going to make a field called ExpirationDate. This field would be filled in by a user upon expiration of the image, but in practice it would function as a boolean (at least for now - maybe eventually the timestamp-ness of it would become useful). If there was an expiration date, the image was expired, because we can’t really know when a roster change will happen until it actually occurs, so an expiration date would never be entered preemptively. (Of course, the logic would still check (1) “is there an expiration date?” (2) “is it in the future?” for safety, but this thing is basically a boolean.)
The confusion then arises because a user may see ExpirationDate in the list of available fields and think that it should be filled in ahead of time - even though that’s clearly impossible as described above. So I changed the field to be a boolean, IsExpired.
After additional consideration that this name might also be confusing - as it’s not required to be populated on roster photos that have been replaced by more recent versions (thanks Spoonz for this observation!) - I renamed it again to Hide. The final field is therefore now a boolean called Hide which, when true, causes an image to be hidden. Extremely straightforward!
Conclusion
When clarity to users is involved, you may find yourself making tradeoffs between what goes in your code and what you do for UX. Usually UX should win. It’s possible that one day I’ll regret the lack of timestamp in this table, but that seems pretty unlikely, and my users are certainly going to benefit from a more straightforward naming scheme and set of fields.
