database - 如何在实体-属性-值设计中处理不同的数据类型(例如，具有多列的单个表或每种数据类型的多个表)？

Question

我想使用实体-属性-值 (EAV) 方法创建患者/样本元数据表。

问题:我应该如何根据属性处理值的不同列类型(例如字符串、数字或字典表的外键) ?

注意:我不是在问是否要使用 EAV 方法。我看过other SO questions和 references并相信这是适合我的用例的最佳方法(例如，我不想为每个属性 创建一个单独的列或表 - 这可能有数百个)。但是，我肯定会重新考虑其他设计，给出一个综合示例。

代表性数据

一个患者/样本(实体)可以有多个元数据属性(例如实验室位置、存活率、肿瘤类型)，每个元数据属性都有不同的值 类型(例如 VARCHAR、NUMBER、FOREIGN_KEY*)。

*FOREIGN_KEY 表示此值 类型是值字典表的外键 ID(INTEGER) em>(例如 10 种可能的肿瘤类型的列表)。因此实验室位置可以是 VARCHAR，因为我不关心这些值的规范化。但肿瘤类型应该有一定程度的验证。

我的表格布局可能看起来像这样:

CREATE TABLE patients (
  patient_id INTEGER CONSTRAINT pk_patients PRIMARY KEY,
  patient_name VARCHAR2(50) NOT NULL
);

CREATE TABLE metadata_attributes (
  attribute_id INTEGER CONSTRAINT pk_metadata_attributes PRIMARY KEY,
  attribute_name VARCHAR2(50) NOT NULL,
  attribute_value_type VARCHAR(50) NOT NULL -- e.g. VARCHAR, NUMBER, or ID
);

CREATE TABLE patient_metadata (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value ???
);

我认为 metadata_attributes 表中需要一个值 类型标识列 (attribute_value_type) 以了解要查找的列/表。

可能的方法

这是我能想到的两种可能的方法。

方法 1:具有多列的单个 EAV 表

为 patient_metadata 表创建三个不同的列 - 每个值类型一列。

CREATE TABLE patient_metadata (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_varchar_value VARCHAR(50),
  attribute_number_value NUMBER,
  attribute_id_value CONSTRAINT fk_pm_values REFERENCES some_table_of_values(value_id)
);

方法 2:多个 EAV 表

创建三个不同的 patient_metadata 表 - 每个值类型一个。

CREATE TABLE patient_metadata_varchar (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value VARCHAR(50) NOT NULL
);

CREATE TABLE patient_metadata_number (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value NUMBER NOT NULL
);

CREATE TABLE patient_metadata_id (
  patient_id CONSTRAINT fk_pm_patients REFERENCES patients(patient_id) NOT NULL,
  attribute_id CONSTRAINT fk_pm_attributes REFERENCES metadata_attributes(attribute_id) NOT NULL,
  attribute_value CONSTRAINT fk_pm_values REFERENCES some_table_of_values(value_id) NOT NULL
);

其他方法？

还有其他方法吗？

简而言之，我想尽可能地尊重关系完整性，并让数据库知道值 类型，以便它可以执行基本验证。但是，我相信上述两种方法都需要某种类型的手动完整性检查(方法 1 需要检查是否只填充了一个 attribute_value 列，等等)。

我将执行的查询类型将是典型的(例如，检索给定元数据属性的值列表，检索值 给定患者(实体)和元数据属性等)。我相信在大多数情况下我需要查询 value 类型，以便知道要查询哪个列或表。还有其他解决方法吗？

所有方法(性能、查询结构等)的优缺点是什么？

第一次张贴，所以提前致谢，请随时评论格式或进一步澄清!

Answer 1

这是一个众所周知的问题。您提到的方法的问题是您需要在查询之前知道属性的类型。这不是世界末日，因为您管理元数据但仍然...

两种可能的解决方案可能是

1. 使用varchar2 数据类型来表示已知的所有数据类型格式。数字和字符没问题，日期值可以写以预定义的方式(就像在任何 OO 中实现 to_String()设计)。
2. 使用 ANYDATA数据类型。我亲自玩过它但决定不使用