如何确保日志记录表中的值与其他表中的对象匹配? [英] How do you ensure values from a logging table match objects in other tables ?

问题描述

我有三个桌子.两个基本表列出了对象，第三个表记录了数据库中的更改.这是一个例子.

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);

我想确保log_table中的任何行都对应于单个表或组织表中的现有对象.这意味着插入

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');

仅在表 organization 包含ID为13的记录时有效.

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

如何在PostgreSQL中做到这一点?如果这不可能，那么我想我必须在 log_table 中为个人表创建一列，为 organization 表创建一列.

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.

推荐答案

从当前的设计开始，您可以通过向每个实体表中添加一个常量检查或计算/虚拟表/类型变体/标签列来强制执行声明性的操作，日志表中的FK(外键)(id，表).

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.

您有两种/类型的已记录实体.Google sql/数据库子类型/多态性/继承.或(反模式)将2/许多/多个FK转换为2/许多/多个表.

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.

这篇关于如何确保日志记录表中的值与其他表中的对象匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！