1

Simplified example of a data modeling problem that I have:

I have a domain model with an image table, a camera table, a machine table, a foreign key constraint between image and camera (taken_by_camera), and a FK between camera and machine (installed_into_machine).

image taken_by_camera camera
camera installed_into_machine machine

All of this is based on the data, we get from customers.

However, in our application we utilize the data almost always in terms of sets of images. So, we also have a set table. Each image belongs to a set and for that we have a belongs_to_set FK on the image table.

image belongs_to_set set

My problem is that I would like to restrict the sets on containing images of a specific machine only, e.g.

set restricted_to_machine machine

With just these FKs in place, I have no constraint that ensures that all images that are connected via camera to a specific machine also end up being connected to the same machine via the set.

image_1 taken_by_camera camera_1
camera_1 installed_into_machine **machine_1**
image_1 belongs_to_set set_1
set_1 restricted_to_machine **machine_2**

How can I model something like this (logically but also in postgres).

PS: It should be possible to have images taken by different cameras in a set as long as these cameras are installed in the same machine.

1 Answer 1

0

You need to make set have a FK on machine, and this should be a secondary composite unique key along with its main primary key.

create table "set" (
    id serial not null primary key,
    machine_id int not null references machine (id),
    unique (id, machine_id)
);

You also should do the same for camera

create table camera (
    id serial not null primary key,
    machine_id int not null references machine (id),
    unique (id, machine_id)
);

Then change FKs on image so that they reference those new unique composite keys

create table "image" (
    id serial not null primary key,
    machine_id int not null,
    taken_by_camera int not null,
    belongs_to_set int not null,
    foreign key (taken_by_camera, machine_id) references camera (id, machine_id),
    foreign key (belongs_to_set, machine_id) references "set" (id, machine_id)
);

Now any row you insert into image does not reference the machine table directly, it references the camera and set tables via its machine_id column, thereby ensuring that a camera or set which it references has the same machine_id as the image does.

db<>fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.