This is an archived post from my previous blog. Some content may be outdated. FYI!

Introduction

In this article, we’ll take a look at a simple yet effective pattern in designing database tables that I feel like isn’t getting used enough, especially by beginning programmers. That is the use of the TIMESTAMP data type both as a DATETIME and a pseudo-BOOLEAN data type. As for most design decisions, a lot of it comes down to "”it depends”“. So we’ll look into some use cases and see the pros and cons of each.

Note that some code samples uses PHP and Laravel, but a customary glance should still drive the point home if you’re familiar with any programming language. Not familiar with SQL data types? Take a quick look at the MySQL implementations of TIMESTAMP, DATETIME, and BOOLEAN.

The Situation

Our good friends at ACME Web Services decided to add a new feature within their internal ticketing system. The higher ups would like to manually pre-check a high priority ticket before passing it to a special ticket queue. The following diagram visualizes the desired system:

Now, take a minute and think how you would implement this feature on the database level. What fields and their respective data type should we use?

Our Options

In a nutshell, we need to make it so that a Ticket, the database entity of interest, can be checked by an admin. This seems like a simple case, just use a BOOLEAN field with something like has_been_checked_by_admin and toggle it to TRUE once an admin has got their hands on the ticket:

Now, it’s reasonable to assume that the date and time that the ticket has been checked is an important detail for this ticketing system (seeing that it’s a queue and all). How about we add another DATETIME field that holds the specific time wherein the ticket has been checked:

With this setup, a ticket can be checked off with a rough API like this:

// PriorityTicketController
public function store()
{
    $ticket = Ticket::findOrFail(request()->post('id'));
    
    $ticket->markAsChecked()    // toggles the boolean
           ->on(now());         // sets the datetime
}

// tickets.blade.php
@foreach ($tickets as $ticket)
    Approved? : {{ $ticket->has_been_checked_by_admin ? 'yes' : 'no' }}
    Date of Approval: {{ $ticket->checked_by_admin_at }}
@endforeach

Now let’s look at the option that this article is pointing to: using a nullable TIMESTAMP to simplify the process.

We can now use a similar API but using a simpler schema:

// PriorityTicketController
public function store()
{
    $ticket = Ticket::findOrFail(request()->post('id'));
    
    $ticket->markAsChecked(); // sets the has_been_checked_by_admin_at to the current time
}

// tickets.blade.php
@foreach ($tickets as $ticket)
    // Both information can be inferred by a single field
    Approved? : {{ $ticket->has_been_checked_by_admin_at ? 'yes' : 'no' }}
    Date of Approval: {{ $ticket->has_been_checked_by_admin_at }}
@endforeach

To clarify, we use the has_been_checked_by_admin_at as a boolean by checking for truthy-ness (it defaults to null, which can be reasoned as false). Once it was set to a datetime, the field becomes truthy and can be used as a good-ol’ datetime for whatever purposes it may serve.

Stepping Back

Okay, was that much better?. I personally think it does. Having a single source of truth for a pair of information that were tightly coupled anyway makes it easier to reason with the data. There’s no "”what if I set the approval date but forgot to toggle the boolean”“ and vice versa.

As for downsides, some may argue that this pattern reeks of a ““hack”” and it would serve us well to use the classic data types for our classic needs (BOOLEAN for a ““what”” and DATETIME for a ““when””). It’s also a valid point that what if we need to separate the field into two down the line? If it ever came to be that we want to set the checked_by_admin_at (which defaults to null) into something without touching the has_been_checked_by_admin field, then yes, we lost that flexibility by using a timestamp. I personally think that’s overthinking the problem, and it doesn’t make sense at all, but I digress.

Conclusion

In this article, we looked at a common pattern of using a TIMESTAMP to infer both BOOLEAN and DATETIME values. We also covered why it’s a good thing and some point on why it might not be a good idea. Now go ahead and try it out on your next project!

Got any feedback or suggestions? Feel free to send me an email or a tweet.
Ciao!