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_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
updated_atfields 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.