How do you ensure values from a logging table match objects in other tables ?

Question

I have three tables. Two basic tables listing objects and a third table logging changes in database. Here is an example.

create table individual (ind_id integer, age integer, name varchar);
create table organisation (org_id integer, city varchar, name varchar);
create TABLE log_table (log_id integer, object_id integer, table_name varchar, information json, log_date date);

I want to ensure that any row in the log_table corresponds to an existing object in either the individual table or the organisation table. This means that the insertion

insert into log_table (object_id,table_name,information,log_date) values (13,'organisation','{"some":"interesting","information":"on the changes"}','2017-11-09');

is valid only if the table organisation contains a record with the ID 13.

How can I do that in PostgreSQL ? If this is not possible, then I suppose I will have to create one column for the individual table and one for the organisation table in the log_table.

The problem with your current design is that those two can collide. You may have Individual and Organisation with same IDs. — Alexey Soshin, Nov 09 '17 at 20:07
That is why you need to take the table name into account in the validation. — Emilie Picard-Cantin, Nov 09 '17 at 20:07

score 0 · Answer 1 · answered Nov 10 '17 at 11:29

You need an entity table:

create table entity (
    entity_id serial primary key,
    entity_type text check (entity_type in ('individual','organization'))
)
create table individual (
    ind_id integer primary key references entity (entity_id), 
    age integer, name varchar
);
create table organisation (
    org_id integer primary key references entity (entity_id), 
    city varchar, name varchar
);
create TABLE log_table (
    log_id integer primary key, 
    entity_id integer references entity (entity_id), 
    information json, log_date date
);

score 0 · Answer 2 · answered Nov 10 '17 at 11:54

You could also use triggers to solve this problem . Seperate triggers can be made on individual and organisation table which could be on before update ,after update , after insert actions .

You could add one column in log table which would correspond to action performed in base table i.e update or insert .

Also you could add unique constraint on table name and object id .

This would eventually lead to logging every possible operation in table without changing in application code .

Hope this helps !

score 0 · Answer 3 · answered Nov 11 '17 at 04:55

Starting from your current design you can enforce what you want declaratively by adding to each entity table a constant checked or computed/virtual table/type variant/tag column and a FK (foreign key) (id, table) to the log table.

You have two kinds/types of logged entities. Google sql/database subtypes/polymorphism/inheritance. Or (anti-pattern) 2/many/multiple FKs to 2/many/multiple tables.

How do you ensure values from a logging table match objects in other tables ?

3 Answers3

Linked