Database Auditing: "Updated At" Null or Not Null?

Question

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.

Answer 1

Already good answers here, but let me respond to your "NOT NULL" arguments:

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

An error by letting these fields empty when an update occurs is not more or less likely than forgetting to update the fields to the right values when an update occurs, so this argument is a logical fallacy.

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

Sure, but it is neither harder nor easier to test for NULL. Ok, for some cases one has to take special care for NULLs in queries, but that's hardly a difference important enough to generally forbid NULL columns.

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.

That depends heavily on the language and db framework which are used, but honestly, NULL value support is not rocket science and should not make the code that more complicated.

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.

So you are saying there are other teams having problems to deal properly with NULL values? Seriously? If they really have some tools which have problems with that, how do the tools handle NULLable columns in other tables? I am sure you don't have a company-wide rule to forbid such columns (otherwise you would not have asked the question in the first place), so they better fix those tools, since sooner or later they will have to deal with NULL values.

In short, you (and probably your superior, too) are heavily overthinking this, both approaches are equally valid, and the best thing you can do is to flip a coin.

Answer 2

Either approach is workable and there's nothing inherently wrong with either. I would tend to prefer the approach you are advocating mainly because nulls are a pain to work with in SQL due to three-value logic. In general, it's better to avoid them. As you say, updated = created tells you the same thing as having the updated field be null for new records.

On a side note, the overall design of this could be improved. In this approach, you can see when the record was created and when it was last updated. You can't tell when else it might have been modified in between or what the values were at each step. A more robust approach would be to use an insert-only approach. In that case you only track the created timestamp. Then you have the entire history of all the changes. You can use a view over this table to simplify for clients that only need the current data.

Answer 3

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.