Why You Should Replace Your Booleans With Timestamps

[@tinrab]

Hmm, maybe. I use "deleted" as a boolean and "deletedAt" as datetime, at the same time. At a previous job, we had both as well. I can't remember why both were needed. One thing you can do with booleans and not datetimes (i think) is sharding.

[@-taz-]

I've worked for billion dollar companies where that was the rule. Usually the sales people were very successful, and didn't know what they were doing when they hired a bunch of idiots to write their software.

[@dhmacher]

Database professional here: this is _great_ advice. Also, always verify your backups.

[@aghileslounis]

what do you mean by verify your backups? Like test if they are not corrupted and actually contain the correct data?

[@dhmacher]

@@aghileslounis yes. Just because you can make a backup does not mean it restores ok. There could be database corruption (that could get silently written to your backups), storage hardware bugs, disk errors, or even just incorrectly set up backup jobs.

[@planesrift]

I love date, it is the most universal format of all

[@ДмитрийКарпич]

And most complicated format at same time.

[@justusonuoha8224]

I know companies that have audit tables that keep history of when fields got changed.

[@MichaelCampbell01]

This is a very old technique; my job in the 1990's was doing this with triggers in Sybase. Any update to a row wrote the entire row to a "shadow" table before the change with a timestamp of the update. It is a very early kind of an "event streaming", immutable database scheme.

[@-taz-]

@@MichaelCampbell01 I'm not sure I'd say it was early event streaming, but more like the kind of kludges that event streaming tried to address by using a cleaner idiom.

[@m12652]

People still ask for audit trails, especially on safety systems etc.

[@-taz-]

​@@m12652 The audit trail should be designed into the database, not sometning tacked on. Although I swear that 90% of people who think they're database experts would just overwrite a timestamp without giving it a thought. Any idiot can use a database. The job is to design a database so when the client asks for some obvious bit of information 4 years from now, it will already be in the database. Not "oh, you didn't say you wanted to be able to track the previous time this thing happened." It's so stupid it's like when Google Maps says "turn right" and you crash into a car. Hey, you didn't say "turn right unless there's a car in the way!" That's how dumb most developers are, I think (after 30 years of this... and things keep getting worse not better!).

[@m12652]

@@-taz- lol you sound like you've not been doing this long. What makes you think audit trails are generally just tacked on? "Do you want an audit trail with that?" is always one of my first questions. As for most developers being dumb, are you sure you're not just talking about yourself? Personally, I'm old by the way, I built my first database on an Amstrad 8512, and the one thing I've noticed in the 30 or so years since... people that make general assumptions and big angry statements about other people being dumb are usually either talking about themselves or trying to distract people from their own terrible code.

[@ChristopherBriddock]

I would use both a timestamp and also a boolean for soft deletions and a DeletedBy field to capture their User ID. For modified and created, just using a timestamp with a User ID field works for me. In C# I use three ValueObjects, EntityCreationStatus, EntityModificationStatus and EntityDeletionStatus to save on duplicating the code via an interface, e.g. ISoftDeletableEntity. I also like using Temporal tables, which saves any modifications in a history table, with PeriodEnd and PeriodStart fields. Downside is you would have to do this manually in Postgres + EF Core, where SQL Server + EF Core, you can do this with a config option on each entity.

[@StefanoV827]

That's a good advice but not always if we talk about cardinality. With a boolean the cardinality is 2 and you dont need an index to search for that field most of the times. With a timestamp you need to calculate not just the byte you showed, but the index space + the little amount of time wasted during insert/update because of the index. So, yes good advice but never abuse it. If you dont need the time of the boolean, just use a boolean

[@saadzahem]

What is cardinality?

[@StefanoV827]

@@saadzahem cardinality is how many unique values are on that column. With low cardinality you can usually avoid index. For example boolean values (tinyint). With high cardinality, SQL needs an index to search more efficiently and faster. So timestamp are always different generating high cardinality and forcing you to use index, while boolean is always cardinality 2.

[@omkarstha]

$exists for mongodb, also rephrased as sexists

[@ДмитрийКарпич]

psss.... or they change it as "main"

[@EmmanuelOdii80]

Good idea! But another caveat to this approach is that the column MUST be nullable. Take for instance you would want to have FALSE be the default value of the column, it's impossible to fix that with a timestamp :) Wdyt?

[@joonaskrohn6785]

False is already the default value, since null maps to false in this case.

[@EmmanuelOdii80]

@@joonaskrohn6785 Agreed. We just have to think about it properly, hehe.

[@jigani99]

I think this solution only accounts for the positive state. For a Boolean field that can change multiple times from false to true and then back to false. The timestamp trick won't be able to account for the timestamp of a field that was true and later set to false

[@witchmorrow]

you can just set the field to be `null` again when the condition becomes false in your code

[@jigani99]

@@witchmorrow I agree but there's no timestamp information of when the field was set to null again

[@soverain]

More like this please!

[@XavierGoncalves89]

I implemented by myself a few months ago while working on an angularjs codebase, in order to detect if an object’s properties had changed

[@marcusradell7544]

Nice way to dip your toes in event sourcing 👍

[@ucretsiztakipci6612]

It's faster and cheaper to maintain boolen in a high scale. Do some tests in 100m or more for reporting. Specially in real time.

[@mattwandcow]

This is a useful idea to know about. Thanks!

[@peanut-butter-e1r]

bro has built-in spring inside neck that move his head all the time

[@pomprocks]

How about nameChangedAt, nameSetAt, nameRemovedAt, ageChangedAt, emailChangedAt? IMO if you want that data its better to just use the boolean and track it with another system.

[@TheSliderW]

Yes. Dont fill up your table with useless columns that happen to only get used once or twice in a decade. What a waste of ressources. This is what logs are made for.

[@CptWashu]

The other use case is tables that have critical data. But you'd have to check the cost/ease trade off

[@Noceo]

In some (granted rare) cases this could impact performance negatively. It’s not just extra space in the DB. This requires extra computations every time you want to check e.g. if a user is deleted. But it’s definitely a useful tip, if applied with some thought.

[@m12652]

Not at all... if the field is null it is false, not null it's true... what extra calcs? In your example you would have a deletedDate as opposed to a deletedBoolean. Much more useful.

[@ArielZ13]

Why not just updatedAt column?

[@ДмитрийКарпич]

Actually, it`s a strange idea. If you need timestamps for logging only, just add another table for logging. And I`m not database pro, but it seems it`s a terrible decision for indexes and complex indexes at first place.

[@trappedcat3615]

Sound like the database simply needs a single timestamp for all events 😅

[@moggedau]

This isn't feasible in a large application - use a logger and create a audit log function. Then at any part of the application you can log x user did y something at this date time.

[@MrNickP]

I can see a few uses for this but overall definitely not something to do by default.

[@-taz-]

That's right. We need to model the data with purpose. Don't add complexity "just because it might make debugging easier." Debugging is what people do after they've already screwed up. It's like "quality control" in factories 80 years ago: eventually discovered to be pure waste.

[@m12652]

The fact is though a lot of people use booleans when a nullable date would be much more useful... or worse use both eg closedDate and closedBoolean. Not because they're dumb, it was fairly standard in a lot of places years ago because on paper they would tick a box and date it.

[@-taz-]

@@m12652 To me a nullable date or nullable anything seems even less appealing than a Boolean.

[@AustinCameron]

archived_at, deleted_at, sent_at

[@RalfTenbrink]

What a great tip. Thanks

[@creatorsremose]

Sure but why are you crediting Vic when it was Swizec or introduced the idea? Vic just made a meme out of it.

[@adambickford8720]

I promise you the idea is older than that.

[@marcuss.abildskov7175]

I've been using both booleans and timestamps to keep track of a field

[@witchmorrow]

seems like you probably only need the timestamp

[@marcuss.abildskov7175]

@@witchmorrow sure, I could add virtual getters to my entities that returns booleans.

[@Neckhawker]

or you just use logs

[@-taz-]

That's less bad because it doesn't add extra complexity to the database. Software quality can practically be measured by the number of logs, inversely. In 3.5 years of working at one company, there was only one log, and that's for a critical error that took place after a more serious critical error, because there was no way left to recover. No software person could find anything useful to do, with a combined 100 years of C++ experience, so it bubbled up to the dept. head, who said to "log it." That guy was an idiot who got fired for some HR nonsense a month later anyway. If there's a fact that matters to the enterprise, put it in a database or fire off some event. My most recent two companies have been the worst I've ever seen in 30+ years. We're swimming in logs and someone says "log it" at least every 3 days.

[@CarnesSurefire]

@@-taz- logs can be rotated/deleted. There's no need to store log-level data in a database unless that data is actively integrated into the app. Not sure why you're saying that you can store some data in a database but can't log the exact same info...

[@-taz-]

​@@CarnesSurefire If the information is important to the enterprise, it goes in the database. Logs are for, I don't know, system admins in the 1970s? I'm not telling anyone to use logs ever. It's like the saying "when there's smoke there's fire." "When there are logs there are idiots writing software."

[@CarnesSurefire]

@@-taz- now you have to give your most junior devs access to prod data just to debug minor issues. Logs are also designed to be immutable and not required for the system to function. Putting log level info into the database is 1990s software design.

[@-taz-]

@@CarnesSurefire I'm not telling you to out log level data into the database. I just got through writing that you put only the information that matters to the enterprise. If you way of understanding your software is to sift through logs, well, I don't what you're even thinking. I didn't think that way when I was a kid, or in school, nor did I read it in a software engineering book.

[@luisrodrigues6211]

How about the timestamp running out by 2039? what would be the advisable thing to do now to minimize it's impact?

[@danielschwartz516]

good topic!

[@joel-rg8xm]

sounds logic, reasonable and convenient for a 300,000 records table, or isn't it?

[@Naej7]

What about a 16 Billions records table ? :^)

[@JimfromIndy]

Interesting idea.

[@culturehyena]

If you need this level of auditing, you might be better off moving towards event-based systems?

[@lsaavedr]

thanks! I ❤ tricks like that!

[@kalligatorGR]

So at the end you still need the boolean, the difference being you now have to generate it every time instead of having it readily available in your db. Doesn't sound that good of an idea.

[@pixelgoat_]

What about using a jsonb of specifically using Postgres. This type can also be efficiently indexed using GIN. u also wouldn’t need that extra options in the query example I used with two properties in the jsonb.

[@montebont]

Converting the timestamp to base 36 saves a lot of bytes...

[@theguitarslinger1]

Interesting thought... I (Chat GPT) did some math, and while it would be theoretically 10 bytes vs 6 bytes assuming 1 character = 1 byte, in practice, INTs are stored more efficiently than CHARs or VARCHARs in a database. In a typical SQL database, a Unix timestamp would be 4 bytes period. The same timestamp in base 36 would be 6 bytes, and actually 7-8 bytes with overhead. I ran it with base 52 (using both letter cases), and that did not save any bytes over the base 36. Strings also have more memory overhead when working with them, but there could be some minor memory savings depending on the environment. In Python, the base 10 timestamp would be 59 bytes, and the base 36 timestamp would be 55 bytes. You might have been joking and already known this, but I was curious :)

[@cissedesign6358]

We've been doing that in Laravel for years now

[@rickharold7884]

Good idea.

[@avid459]

don't do this, this doesn't work for most booleans where you have the possibility to toggle them back and forth. Your junior devs are bound to abuse them as well.

[@m12652]

Depends on context, for example a deletedDate is more useful than a deletedBoolean....

[@-taz-]

That idea smells bad. It violates SRP and normal form. Timestamps in a database are usually a bad idea, too, such as a last sign-in time, when setting the time erases the previous sign in. What a joke! That should be stored every time. When the reason for something given is "for debugging purposes" we're talking about unconscious incompetence. If you have to watch some code execute to know what's going on, you've already fumbled the ball.

[@ydkme-reborn]

How does it violate SRP and Normal form? He's just replacing a Boolean with a timestamp.

[@-taz-]

@@ydkme-reborn I'm not sure why you're asking because these are both practically by definition. If a field that contains a timestamp is also serving as a boolean, it now has two responsibilities. The timestamp responsibility might be mostly useless, but the software is made more complex regardless. Having a nullable field at all violates normalization. Another smell. The normalized model would use another table to append data optionally. A 1:1 relationship could be used here. But if there's something that happens multiple times, a 1:N would actually model it correctly. And this is probably what's needed if you're tracking clicks, logins, etc. It can also be done pretty cleanly and naturally with event sourcing. There is even a third smell which might not be noticed by JS programmers -- the nullable value problem. By making things more complicated than necessary, errors become more probable.

[@ydkme-reborn]

@@-taz- Thanks for the explanation. I don't think he's talking about tracking things like click though. That would definitely be a really bad idea.

[@iaminterestedineverything]

Remember that the use case being described here is to log when a user verified their email and when the user is deleted.. each event is only going to happen once so what is described in the video is a practical solution and creates an opportunity for analytics above a simple boolean. Over normalisation in circumstances like this is unnecessary and applying SRP here is pointless in my view, especially given you can translate the null/not null into a true/false in a query or view should you want to. If tracking changes on multiple columns in a row, I'd apply something like SCD type 2 or 4 (audit/history table)

[@-taz-]

@@iaminterestedineverything Let's take the registration time / null. Well, in the meantime, before they register, there's a null. That also means there's definitely some mutable data necessity. (So there's a 4th smell.) If someone's going to register, then add a record to the registered collection, or insert into a registration table. Even better, they aren't even a User until they actually register. They're a UnregisteredUser or something like that. Where's the guidebook that user registration is allowed to have bad database design? Hehe.

[@ydkme-reborn]

Just use both

[@lonleybeer]

But.. cant you just log your data..?

[@cb73]

This isn’t new at all. I’ve been doing this for years and it’s been a feature of Laravel/active record ORMs forever.

[@yyanasolyah]

New guitar?

[@marcscherzer]

What about login? I don't think isLoggedIn can be replaced by loggedInAt (different meaning)

[@imornar]

If u store these things in db, just stick with front-end

[@EmmanuelOdii80]

Where loggedInAt could mean the location where the user logged in from 😂

[@m12652]

Actually isLoggedIn could easily be replaced by a nullable date. If it's populated they're logged in, if not... not.

[@zxenon_]

@@EmmanuelOdii80 hehe 🤣🤣

[@RobbPage]

oh look, another game changer. the game sure changes a lot.

[@Alex-KOX]

What frawework is backend written in on video?

[@nomadshiba]

or make mutations witb transactions and keep the transactions history.

[@-taz-]

Much better.

[@atulkumar_zip]

Cool

[@howhello354]

If you know Laravel, you already know this.

[@sarahwbs]

How does this help with boolean that get toggled on and off??

[@teenspirit8714]

such a stupid idea

[@ChristianKolbow]

why? if you write such an assertion, you should also substantiate it

[@andrii6292]

Reasonable. Thanks

[@jamesford3282]

stop making videos with clickbaits, I will do whatever I want

[@АлександрГерасимов-с3щ]

Before I watch: I doubt it will make anything meaningful as number (timestamp) will always be truthy

[@EmmanuelOdii80]

Not sure what you mean :/

[@linela]

Another stupid advice 😮

[@ChristianKolbow]

ok, then explain why. your comment is completely useless.

[@nr7343]

?

[@mhadi-dev]

Reply to my comment if you thought this video is about "record uniqueness" when you saw the title.

[@BsiennKhan]

No thanks. Thank you for sharing.

[@EmmanuelOdii80]

haha

[@cant_sleeeep]

lolzas

[@nico_1106]

What a misinformed advice. It is slow, inefficient and unnecessary 99% of the time. Ever heard of created_at and updated_at?

[@owenwexler7214]

venue.isHotel 🚫 venue.becameHotelAt ✅