2

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.

  • My opinion: NOT NULL and CURRENT_TIMESTAMP as default value. But since I see you have columns named 'created_by' and 'updated_by', I suggest you to create a seperate table 'example_table_updates', containing the columns 'example_table_id', 'updated_by', and 'updated_at'. This way you could keep track when multiple users update the entry. – JoeriShoeby May 25 '18 at 17:44
  • @JoeriShoeby that is a good suggestion. It also troubles me not using a default value for the fields, but that is another question, hehe. Now, about keeping track of the updates, on some instances we also have a complementary log table, but only when it is important =) – cavpollo May 25 '18 at 18:03
2

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.

  • I don't often get the chance for the opportunity of teachable moments, but nevertheless I'll give it a try. I really like the questions you propose, that helps a lot on deciding the approach. =) – cavpollo May 25 '18 at 20:30
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.

  • Most database vendors have specialized auditing tools (3rd party tools) that can do full change tracking on every column in a table. You can even create "shadow" tables and triggers on the main table to track this stuff. I wouldn't worry too much about either of the OP's approaches. – Greg Burghardt May 25 '18 at 18:33
  • 1
    @GregBurghardt I get what you are saying. All of this should be in the journal anyway. But if someone is going to depend on that or a tool, why bother with the audit columns? They don't really tell you much. If you are going to put something like this in place, you might as well get something useful. – JimmyJames May 25 '18 at 20:26
2

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.

  • I'm not sure if you understood the fault laid at the tools and not my peers, or if your definition of inconvenience is the same as being incapable of working, but leaving your sarcasm aside, thanks for your thoughts and time. – cavpollo May 25 '18 at 20:27
  • I'm not sure about "it is neither harder nor easier to test for NULL". When I first started working with databases, I'm sure 90% of the bugs I created were due to not understanding that I needed to do things like "where foo != bar and foo is not null". Null values in columns can really complicate things significantly. – JimmyJames May 25 '18 at 20:31
  • @cavpollo: well, what you wrote could mean both (people or tools, or both), but see my edit. – Doc Brown May 25 '18 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.