postgresql 图像表可以与两个不同的表相关联

qrjkbowd  于 2023-03-12  发布在  PostgreSQL
关注(0)|答案(1)|浏览(126)

我正在创建一个生物信息学平台的前端,我需要表ExperimentSample。一个Experiment可以由许多单个样本组成,例如一对多关系。
实验表关系如下所示:(id, experiment_name, additional_columns)
样表关系如下:(id, sample_name, experiment_id, additional_columns)
现在,我想为Images创建一个表,该表将与S3 uri和一些关于图像的元数据相关联;然而,图像可以与ExperimentSample相关联。我可以这样做,

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

这种设计的缺点是,将来我可以添加其他表,这些表也可以有与之关联的图像。
如何推广这种设计,以便不必为每个具有图像关联的表添加FK?

kiayqfof

kiayqfof1#

在这种情况下,最正确的设计是创建不带外键的image表,对于与image表关联的其余表,实现OneToManyManyToOne关联。
假设您的experiment表可以包含许多图像,那么您需要通过创建一个额外的表experiment_images来实现OneToMany关联

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

这将防止image表在将来更改。如果您有其他表。
sqldaddy中的演示

相关问题