Often we need track changes for the entire database, so we need design audit system to track changes. There are many best practices, such as separating the auditing table in another database, keep it small and fast, demoralize, etc. But at the end, we are facing how to design the auditing system.
There are a few approaches to design the auditing system:
- Build an auditing table for each base table;
- Build one generic auditing table to track changes of all database tables;
- Write changes to XML files which are stored outside of the database.
Triggers residing on database tables may capture changes and write to auditing table(s) or XML files. There are cons and pros for each one of them. None of them is perfect. Also to use trigger on every table could bring down the entire database performance. Which approach to choose really depends on which goal you want to achieve.
Our design goal is to minimize the impact to source database, keep the storage in a reasonable range. So we don’t want to build 100 auditing tables for 100 source data tables, instead, we want to have only one generic auditing table to serve the purpose.
Let’s see how we design this table (with PostgreSQL syntax):
CREATE TABLE public.my_audit_table
(
id bigint not null default nextval('my_audit_id_seq'::regclass),
table_name character varying(50),
primary_key character varying(300), --Concatenated PK column names with delimiters
primary_value character varying(4000), --Concatenated PK column values with delimiters
column_name character varying(100),
old_value character varying(4000), --before value
new_value character varying(4000), --after value
action character varying(10), --INSERT, UPDATE, DELETE
modifiedby character varying(100), --user id
modifiedon timestamp, --modify time
modifiedusing character varying(30) --modify tool
)
Although many database design best practices recommend not to use composite keys, many databases use them with their own reasons. So we need to handle this situation. In above table schema, primary_key and primary_value support composite PKs. Triggers will check the column data type, convert numeric and date / timestamp values into string and write into this table.
This table may trace who made changes using what tool at which time, and the change occurs on specific table column, with PK column values, before value and after value. This table is compact but enough to collect all important change information.
An elegant solution to keep it small is to use data synchronization tool to ‘move’ all changes to backup database. Please stay tuned for our upcoming high performance enterprise data synchronization tool LiveSync soon. Please feel free to contact us should you have any database design questions.
