Image Table can be associated with two different tables

Question

I am creating a front end to a bioinformatics platform and I have to tables Experiment and Sample. An Experiment can consist of many individual samples, e.g. a one-to-many relationship.

Experiment table relation looks like: (id, experiment_name, additional_columns)

Sample table relation looks like: (id, sample_name, experiment_id, additional_columns)

Now, I want to create a table for Images that would be associated with an S3 uri and some metadata about an image; however, the images can be associated with either the Experiment OR the Sample. I could do something like,

CREATE TABLE images (
    id serial4 NOT NULL,
    s3_image_uri text NULL,
    FOREIGN KEY (experiment_id) REFERENCES experiment (id),
        FOREIGN KEY (sample_id) REFERENCES sample (id),
    CONSTRAINT images_pkey PRIMARY KEY (id),
    CONSTRAINT images_image_url_key UNIQUE (image_url),
);

The downside of this design is that in the future I could add additional tables that could have images associated with them as well.

How can I generalize this design so that I don't have to add an FK for each table that has an image association?

I think so! So Experiment and Sample correspond to Question and Answer respectively and the Comments table is the Image table in my example; what is the Post table (from a name perspective)? — spitfiredd, Mar 11 '23 at 01:22
Ah, naming things... one of the hardest problems in Computer Science! Especially hard because you don't know yet what other types of entities will be added in the future. How about `EntityWithImages`. — Bill Karwin, Mar 11 '23 at 01:29

emrdev · Answer 1 · 2023-03-11T05:31:11.043

In this case, the most correct design would be to create the image table without foreign keys, and for the rest of the tables that are associated with the image table, implement the OneToMany or ManyToOne association.

Assuming that your experiment table can contain many images, then you need to implement the OneToMany association by creating an additional table experiment_images

  CREATE TABLE experiment (
       id INT NOT NULL,
       PRIMARY KEY (id)
  ); 
  CREATE TABLE image (
       id INT NOT NULL,
       PRIMARY KEY (id)
 ); 

 CREATE TABLE experiment_images (
      experiment_id int NOT NULL,
      image_id int NOT NULL,
      PRIMARY KEY (experiment_id, image_id),
      CONSTRAINT fk_experiment FOREIGN KEY(experiment_id) REFERENCES experiment(id),
      CONSTRAINT fk_image FOREIGN KEY(image_id) REFERENCES image(id)
 );

This will keep the image table from changing in the future. if you have additional tables.

Demo in sqldaddy

Image Table can be associated with two different tables

1 Answers1