From programming point of view and also to reduce the number of objects in DB could be convinient create just an audit table with a structure like:
- auditi id
- reference_uuid (the key of the main table)
- table_name
- list of audit data
Could work, but is there a way to set a reference key over the uuid of all the tables?
Regarding fk constraints, a single fk constraint can only reference the primary key of a single table.
But, if you want to be serious about audit logs, then you need to keep logs of deletions too, and for those, foreign key constraints would not work anyway.
You may also want to consider bulk insert speed. Foreign key constraint checking can reduce speed.
Laszlo