我想使用实体 - 属性 - 值（EAV）方法创建患者/样本元数据表 .
Question ：我应该如何根据属性处理值的变化列类型（例如字典表的字符串，数字或外键）？
注意： I am not asking whether or not to use an EAV approach . 我查看了other SO questions和references并认为这是我的用例的最佳方法（例如，我不想为每个属性创建一个单独的列或表 - 可以数百个） . 但是，我会在一个全面的例子中重新考虑其他设计 .
代表性数据
患者/样本（实体）可具有多个元数据属性（例如，实验室位置，存活，肿瘤类型），每个属性具有不同的值类型（例如，分别为 VARCHAR ， NUMBER ， FOREIGN_KEY *） .
FOREIGN_KEY表示该值类型是值的字典表的外键ID（INTEGER）（例如，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列，等等） .
我将执行的查询类型将是典型的（例如，检索给定元数据属性的值列表，检索给定患者（实体）和元数据属性的值列表等） . 我相信在大多数情况下我需要查询值类型，以便知道要查询的列或表 . 还有其他方法吗？
What are the pros and cons for all approaches (performance, query structure, etc.)?
第一次海报，所以提前感谢，请随时评论格式或进一步说明！
2 回答
这是一个众所周知的问题 . 您提到的方法的问题是在查询属性之前需要知道属性的类型 . 它不是世界末日，因为你管理元数据但仍然......
两个可能的解决方案可能是
使用
varchar2数据类型表示已知格式的所有数据类型 . 数字和字符都没有问题，日期值可以以预定义的方式编写（就像在任何OO设计中实现to_String()一样） .使用ANYDATA数据类型 . 我个人玩它但决定不使用它 .
最简单，最高效的方法是将数据库中的所有值转换为字符串 . 诸如所指出的问题通常是显而易见的，即使是良好类型的列也会遇到完全相同的问题，这些问题通常表现为性能问题 .
如果这很重要，您可以保持整理顺序（例如，通过将日期格式化为年/月/日），并且数据库的验证不应该由数据库完成，因为为时已晚 . 负数是一种痛苦，浮点数也是如此，但是通过一个可以是负数或浮点数的数来索引是非常不寻常的，并且内存中的排序通常很快 .
如果数据类型不明显，或者需要下游处理器知道，则添加类型列 .
通常，可以在写入记录之前检查对列值的所有完整性约束，包括代码（好）或触发器（不太好） . 尝试使用具有不同类型的本机功能只会带你到目前为止，并且可能无论如何都不太有用，因为值通常具有许多业务特定约束，例如出生日期必须是非空的，并且在1900年之后 .
为了提高性能，请使用包含实体和属性的复合索引前缀 . 索引可以通过实体属性前缀进行分区，减少索引额外深度的任何影响，并且它们压缩得非常好（前缀将压缩为一个或两个字节），因此大小差异最小 .
从EAV表中查询通常最好在视图中完成，这些视图将为您解包实体，以便可以将结构返回到您期望的状态，但如果您处理不同的列，则可能无关紧要 . 患者形式，其特征在于根据历史记录的大量不同元素 . 然后，在您的业务逻辑中处理可能更容易 .
最后，现在这种数据根本不存储在面向列的关系数据库样式中 . 它通常存储为XML（或JSON）文档（Oracle中的XML类型），并且大多数数据库提供一些本机XML处理功能，以便搜索和操作此类数据 . 这对于正常的形式存储和检索是可以的，但是往往会进行任意查询，例如“给我所有患者超过60名去年患有肺炎的患者”相当慢，或者需要更多参与标记反向索引 . 然而，值得一看的是，面向文档/面向文本的方法是否是更好的解决方案 .
祝好运！