database-design - 外键取决于列内容——如何保证完整性？

Question

简短介绍——数据背后的逻辑

我正在研究存储实验数据(拉曼光谱)及其元数据的 PostgreSQL 数据库。除了我们 sample 的“正常”光谱外，还有必要采集和保存特殊光谱以用于仪器校准目的。

每次我做一个实验时，我都会在表 measurements 中创建一个新记录。 ，我在其中指定了 measurement type和相应的元数据(其他表的外键)。

问题是，元数据类型因 measurement type 而异。 :

sample 测量 – 我们必须存储 sample_id从表samples

标准物质 - 在这种情况下，我们从表 substances 中指定一种物质

特殊标准化光源 – source_id必须提供

暗框 – 简单，不需要来自其他表的数据

sample 、物质和光源具有完全不同的属性，因此我们必须将它们分别保存在单独的表格中，链接到表格 measurements通过外键。
我认为我的数据在这里有一个多态关系(如果我错了，请纠正我)。

问题

在当前设计中，存储在列 measurementtype_id 中的值(突出显示的红色)定义哪些外键(突出显示的黄色)必须是 NULL ed 并且必须包含一个值。

Table relationships



我很确定这个设计违反了 third normal form .当错误类型的元数据与测量相关联时，我们可能会遇到一个场景。如何保证数据的完整性？不幸的是，到目前为止我无法找到合理的解决方案......

Answer 1

使用 PostgreSQL，您可以拥有检查约束。现在，我认为您的大问题是，如果您必须添加更多类型，那么随着时间的推移维护可能会成为问题。

但是，您可以相当轻松地执行以下操作:

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

这再次解决了您的直接问题，但稍后会成为维护问题

更好的方法是将相关列拆分为两个单独的连接表，因为这将避免这种头痛。