我想使用实体-属性-值(EAV)方法创建一个患者/样本元数据表。
问题: 基于属性,如何处理值的不同列类型(例如字符串、数字或外键到字典表)?
注意:我并没有问是否使用EAV方法。我已经查看了其他SO问题和参考文献,并且认为这是我的用例的最佳方法(例如,我不想为每个属性创建单独的列或表,可能有数百个)。但是,如果给出全面的示例,我一定会重新考虑其他设计。
代表性数据
一个患者/样本(实体)可以有多个元数据属性(例如实验室位置、生存率、肿瘤类型),每个属性都有不同的值类型(例如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列等)。
我将执行的查询类型将是典型的(例如,检索给定元数据属性的值列表,检索给定患者(实体)和元数据属性的值列表,等等)。我相信大多数情况下我需要查询值类型才能知道要查询哪个列或表。还有其他任何方式可以解决这个问题吗?
所有方法的优缺点是什么(性能、查询结构等)?
第一次发帖,提前感谢您的回复,请随时评论格式或进一步澄清!