Databases: BITs vs DATEs

databases - bit_vs_dates.md

BITs vs DATETIMEs for flags

My background has been more in the MSSQL (Microsoft SQL Server) side of relational databases in the past, so I'll use DATETIME and BIT as the data types of the discussion, but it should work the same regardless of DBMS.

Take for example this table:

CREATE TABLE blog.article
(
  article_id    INT NOT NULL,
  title         NVARCHAR(100) NOT NULL,
  published     BIT NOT NULL,
  created_utc   DATETIME NOT NULL,
  updated_utc   DATETIME NOT NULL,
  CONSTRAINT    PK_article PRIMARY KEY CLUSTERED (article_id),
  CONSTRAINT    DF_article_published DEFAULT 0,
  CONSTRAINT    DF_article_create_dt_utc DEFAULT GETUTCDATE(),
  CONSTRAINT    DF_article_update_dt_utc DEFAULT GETUTCDATE()
)

For the most part, this table has all the essentials, a PK (Primary Key), no nullable columns and many columns have defaults. The published field, is a BIT field, and in this case it indicates a state for this article with the default being 0.

Publish the article using the BIT flag

UPDATE  blog.article
SET     published = 1, updated_utc = GETUTCDATE()
WHERE   article_id = @article_id

Now this gets the job done, the article is live, and we have a rough idea of when it occurred via the updated_utc date field. If we care to keep track of when an article was published, we could set up an audit table, that keep track of these changes.

A better approach

Let's change the published field to a datetime

CREATE TABLE blog.article
(
  article_id    INT NOT NULL,
  title         NVARCHAR(100) NOT NULL,
  published_utc DATETIME NULL,
  created_utc   DATETIME NOT NULL,
  updated_utc   DATETIME NOT NULL,
  CONSTRAINT    PK_article PRIMARY KEY CLUSTERED (article_id),
  CONSTRAINT    DF_article_published DEFAULT 0,
  CONSTRAINT    DF_article_create_dt_utc DEFAULT GETUTCDATE(),
  CONSTRAINT    DF_article_update_dt_utc DEFAULT GETUTCDATE()
)

So now, we have a new published_utc field that is a nullable datetime value. A NULL value would indicate that the article is unpublished, and a datetime value would mean published.

Publish the article using DATETIME

UPDATE  blog.article
SET     published_utc = GETUTCDATE(), updated_utc = GETUTCDATE()
WHERE   article_id = @article_id

Now, when the article is published you will always know for sure what date & time the article was last published, as opposed to before, where this was really more guesswork based on the updated_utc field, or required some kind of audit table.

In terms of reporting, just using a datetime over a bit here, enabled more reporting options as well.

Performance

Whether or not this will have a performance impact will likely depend on your DBMS. Some concerns could be...

• NULL possibly not being indexed
• Changing NULL to datetime causing "row migration" issues

All that will depend on your system, but I find the extra data gained, over just having a on/off switch worth the while, and especially in smaller scale systems, it would likely not be noticeable, and definitely beats having a trigger/audit table setup in my opinion.