Database Design – Should ‘Updated At’ Be Null or Not Null?

auditdatabase-designsql

Currently, we've been working on the architecture team on defining the database models. What has been troubling me is my superior's advice when it comes to working with audit fields.

He advocates for the updated_at and updated_by fields to be Nullable, reasoning that these should be initialized as NULL because the database entry has not been updated yet.

CREATE TABLE example_table (
  ...
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_by VARCHAR(128)             NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NULL,
  updated_by VARCHAR(128)             NULL
);

Additionally, he notes that from experience he has learnt that it is better to leave them as NULL, but unfortunately he has not provided me any concrete examples.

I'm more inclined to making these updated fields NOT NULL because:

  • One makes sure this cannot be left empty by any mistake.

  • One can easily figure out the record is new if the created_at and updated_at fields have the same value.

  • The code that works with these updated fields will be cleaner as it won't have to check for the possibility of them being null.

  • Other teams at work have experienced inconveniences. Some tools that work with the data are incompatible, or don't function properly, with entries that have null updated values.

Here is what I would like to implement:

CREATE TABLE example_table (
  ...
  created_at TIMESTAMP WITH TIME ZONE NOT NULL,
  created_by VARCHAR(128)             NOT NULL,
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL,
  updated_by VARCHAR(128)             NOT NULL
);

I've googled some time, but couldn't find some article that points either choice to be correct, or at least one to be better than the other.

So I'm curious: Am I missing some other point of view? Which approach is better?

My code examples are in SQL, but my question may apply to other database types too.

Best Answer

What has been troubling me is my superior's advice ...

Have you asked your superior these questions with an attitude of being teachable? Honestly this is going to be your best approach.

Which approach is better?

Neither. They represent the same functional amount of data. The questions you have to answer are:

  • Is a null value going to break my reporting?
  • How complex does it make any queries you have to write?

Audit fields are usually not queried often, only referred to when you have to reconstruct events. Both approaches work fine for the intended use case.

Related Topic