java eav模型_如何在实体 - 属性 - 值设计中处理不同的数据类型（例如，每个数据类型有多列或多个表的单个表）？...

我想使用实体 - 属性 - 值(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.)?

第一次海报，所以提前感谢，请随时评论格式或进一步说明！