Foreign keys depend on the contents of the column - how to ensure integrity?

Question

Brief Introduction - Data Logic

I am working on a PostgreSQL database that stores experimental data (Raman spectra) and their metadata. In addition to the “normal” spectra of our samples, it is necessary to acquire and store special spectra for instrument calibration purposes.

Each time I do an experiment, I create a new record in the measurements table, where I specify the measurement type and the corresponding metadata (foreign keys for other tables).

The problem is that the metadata type differs depending on the measurement type :

• Measurement example - we need to save sample_id from the samples table
• Standard substances - in this case we indicate the substance from the table of substances
• A special standardized light source source_id must be provided source_id
• Dark frames - simple, no data from other tables required

samples, substances and light sources have completely different properties, so we must store them in separate tables associated with the measurements table using foreign keys. I think my data has a polymorphic relationship here (correct me if I am wrong).

Problem

In the current schema, the value stored in the measurementtype_id column (highlighted in red) determines which foreign keys (highlighted in yellow) should be NULL ed and which should contain the value.

Table ratios

I am sure that this design violates the third normal form . We may have a scenario where the wrong type of metadata is associated with a dimension. How can I ensure data integrity? Unfortunately, while I could not find a reasonable solution ...

Answer 1

With PostgreSQL, you can have control restrictions. Now, I think your big problem is that service over time will probably be a problem if you need to add more types.

However, you can pretty easily do something like:

 ALTER TABLE measurements ADD CHECK((measurementtypeid <> 1 OR (... IS NOT NULL ...)) -- CONSTRAINTS FOR TYPE 1 AND (measurementtypeid <> 2 OR (....) -- constraints for type 2 -- etc ); 

Again this will solve your immediate problem, but later will be a maintenance problem

A better approach would be to break the linked columns into two separate join tables, as this would avoid such a headache.