엔터티-속성-값 설계(예: 데이터 유형당 여러 개의 열이 있는 단일 테이블 또는 여러 테이블)에서 서로 다른 데이터 유형을 처리하려면 어떻게 해야 합니까?

엔터티-속성-값 설계(예: 데이터 유형당 여러 개의 열이 있는 단일 테이블 또는 여러 테이블)에서 서로 다른 데이터 유형을 처리하려면 어떻게 해야 합니까?

EAV(entity-attribute-value) 접근 방식을 사용하여 환자/샘플 메타데이터 테이블을 만들고 싶습니다.

질문:속성에 따라 값의 다양한 열 유형(예: 문자열, 숫자 또는 사전 테이블에 대한 외래 키)을 어떻게 처리해야 합니까?

참고: 저는 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 열만 채우는 검사 등).

수행할 쿼리 유형은 일반적입니다(예: 지정된 메타데이터 속성에 대한 값 목록 검색, 지정된 환자(엔티티) 및 메타데이터 속성에 대한 값 목록 검색 등).쿼리할 열 또는 테이블을 알기 위해서는 대부분의 경우 값 유형을 쿼리해야 합니다.다른 방법은 없나요?

모든 접근 방식(성능, 쿼리 구조 등)의 장단점은 무엇입니까?

첫 번째 포스터입니다. 미리 감사드리며 포맷이나 추가 설명에 대해 자유롭게 의견을 제시해 주십시오!

이것은 잘 알려진 문제입니다.언급한 접근 방식의 문제는 속성을 쿼리하기 전에 속성 유형을 알아야 한다는 것입니다.메타데이터를 관리하기 때문에 세상의 끝은 아니지만 그래도...

두 가지 가능한 해결책은 다음과 같습니다.

1. 용사를 varchar2모든 데이터 유형을 알려진 형식으로 나타냅니다.가 되지 값은 할 수 숫와문문자없며으날, 값미정방다있수작니습식의로성된될으리은자짜는제가ing▁implement▁numbers다it▁likes있▁values니를 구현하는 것과 같습니다.)to_String()모든 OOO 설계에서).
2. 데이터 유형을 사용합니다.저는 개인적으로 그것을 가지고 놀았지만 사용하지 않기로 결정했습니다.

데이터베이스의 모든 값을 문자열로 변환하는 것이 가장 쉽고 가장 성능이 좋은 등의 작업입니다.표시된 문제와 같은 문제는 일반적으로 분명하며, 잘 입력된 열도 성능 문제로 표현되는 동일한 유형의 문제를 겪습니다.

중요한 경우(예: 날짜를 연도/월/일 형식으로 지정하여) 약간의 주의를 기울이면 데이터 정렬 순서를 유지할 수 있으며, 너무 늦었기 때문에 데이터베이스에서 형식 검증을 수행해서는 안 됩니다.마이너스 숫자는 플로트와 마찬가지로 고통스럽지만 마이너스 숫자나 부동 숫자로 색인화하는 것은 매우 이례적이며 메모리 내 정렬은 일반적으로 빠릅니다.

데이터 유형이 명확하지 않거나 다운스트림 프로세서에서 알아야 하는 경우 유형 열을 추가합니다.

일반적으로 열 값에 대한 모든 무결성 제약 조건은 레코드를 쓰기 전에 코드(양호) 또는 트리거(별로 좋지 않음)로 확인할 수 있습니다.다양한 유형의 기본 기능을 사용하는 것은 지금까지 사용할 수 있을 뿐이며, 어쨌든 가치는 종종 비즈니스별 제약 조건을 가지고 있기 때문에 그다지 유용하지 않을 수 있습니다. 예를 들어 생일은 null이 아니라 1900년 이후여야 합니다.

성능을 위해 엔티티 및 특성을 포함하는 복합 인덱스를 접두사로 사용합니다.인덱스는 엔티티 속성 접두사로 분할되어 인덱스의 추가 깊이에 따른 영향을 줄일 수 있으며, 매우 잘 압축됩니다(접두사는 1바이트 또는 2바이트로 압축됨). 따라서 크기 차이가 최소화됩니다.

EAV 테이블에서 쿼리를 수행하는 것은 종종 개체를 풀어주어 구조를 예상대로 되돌릴 수 있도록 하는 보기에서 가장 잘 수행됩니다. 예를 들어 이력에 따라 다양한 요소가 다수 포함되는 환자 형태의 다양한 열을 처리하는 경우에는 이 작업이 관련이 없을 수 있습니다.그러면 비즈니스 로직에서 처리하는 것이 더 쉬울 것입니다.

마지막으로, 오늘날 이러한 종류의 데이터는 단순히 열 지향 관계형 데이터베이스 스타일로 저장되지 않습니다.일반적으로 XML(또는 JSON) 문서(Oracle의 XML 유형)로 저장되며 대부분의 데이터베이스는 이러한 데이터를 검색하고 조작하기 위해 일부 기본 XML 처리 기능을 제공합니다.이것은 일반적인 양식 저장 및 검색에는 괜찮지만, "최근 1년 동안 폐렴을 앓은 60세 이상의 모든 환자에게 제공"과 같은 임의적인 쿼리를 다소 느리거나 태그가 지정된 역색인이 필요하기 때문에 조금 더 관여하는 경향이 있습니다.그럼에도 불구하고 문서 지향/텍스트 지향 접근 방식이 더 나은 해결책인지 확인해 볼 가치가 있습니다.

행운을 빕니다.

언급URL : https://stackoverflow.com/questions/18105644/how-can-i-handle-different-data-types-in-an-entity-attribute-value-design-e-g