One of the more useful design patterns to emerge from the MSSQL world is the createdon, updatedon timestamps that are added to each record. This functionality can be similarly achieved using the database schema. Using the DB saves the dev time in populating the fields and in many cases negate the need to deal with timzones – you use the db server time.
created_on TIMESTAMP default CURRENT_TIMESTAMP,
updated_on TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
created_on will be populated when the row is inserted.
updated_on will be populated when the row is inserted and each time the row is updated the new timestamp is added on.
What you don’t get is a easily viewable history of changes to the data.
There used to be a createdby , updatedby where the user who did the update will be populated. In todays world, this is rather unnecessary as a web GUI will be used to enter and update the data and good practice will be to use the GUI to change data as checks and business rules will be adhered to. So keeping track of the user which did the update is rather redundant as the user will be mostly the web application user.
Using the Data Project as an example, such an implementation on a SQL table will be below.
create table if not exists dataset
(
dataset_id bigint not null auto_increment primary key,
dataset_guid nvarchar(128) not null,
dataset_name nvarchar(512),
organisation_id bigint not null,
description nvarchar(5120),
last_seen_dt TIMESTAMP default CURRENT_TIMESTAMP,
first_seen_dt TIMESTAMP default CURRENT_TIMESTAMP,
active tinyint default 1,
created_on TIMESTAMP default CURRENT_TIMESTAMP,
updated_on TIMESTAMP default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
);