project_phenotype table?

[ekcannon]

I would like to attach a set of phenotype records to the study (project) that generated them, but don't want to use the nd_experiment table:

project --- nd_experiment_project --- nd_experiment --- nd_experiment_phenotype --- phenotype

This is because it is challenging to maintain data integrity due to both the length of the connecting chain, and the lack of constraints on the nd_experiment table.

CREATE TABLE project_phenotype ( project_phenotype_id BITINT SERIAL NOT NULL, PRIMARY KEY (project_phenotype_id), project_id BIGINT NOT NULL, FOREIGN KEY (project_id) REFERENCES project (project_id) ON DELETE CASCADE INITIALLY DEFERRED, phenotype_id BIGINT NOT NULL FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE INITIALLY DEFERRED, CONSTRAINT project_phenotype_c1 UNIQUE (project_id, phenotype_id) ); CREATE INDEX project_phenotype_idx1 ON project_phenotype (project_id); CREATE INDEX project_phenotype_idx2 ON project_phenotype (project_id);