|Do you know if it exist a document or recommendation to create audit table or metadata elements on a database.
I ask this question because I just saw this :
CREATE TABLE client (
id char(36) NOT NULL,
first_name varchar(255) NOT NULL,
last_name varchar(255) NOT NULL,
creation_date timestamp NOT NULL,
update_date timestamp NOT NULL,
CONSTRAINT client_pk PRIMARY KEY (id)
I don't like the idea to have metadata like creation_date and update_date in a business oriented table. Actually these two fields exists in all tables of this database.
What if I create a audit table like this:
CREATE TABLE audit (
table_name char(32) NOT NULL,
fk char(36) NOT NULL,
update_date timestamp NOT NULL
I don't need a creation date because the creation date is my first record with table_name and fk in this table. I could add a columns to add insert, update, delete information. With thi table I keep all my update dates and not only the last one.
Is this audit table a god practice ?
Can I also use this audit table to avoid conflict. You know when two users update the same record at the same time. It's possible to use the last update time to warn them there is a conflict.