Relationship between Concepts (Maps to, Is a, RxNorm has ingredient...) and the Ancestor-Descendant hierarchy tree. The most important table for ETL mapping and hierarchical analysis.
Introduction
If CONCEPT is a "dictionary", then CONCEPT_RELATIONSHIP is a "map" that connects words together, and CONCEPT_ANCESTOR is a "family tree" that shows ancestor-descendant relationships. These two tables are extremely important: CONCEPT_RELATIONSHIP is used for ETL (mapping ICD-10 → SNOMED), CONCEPT_ANCESTOR is used for analysis (finds all "diabetes" codes including type 1, type 2, gestational...).
-- Tìm Standard Concept từ ICD-10 code
SELECT
c1.concept_code AS source_code,
c1.concept_name AS source_name,
c1.vocabulary_id AS source_vocab,
cr.relationship_id,
c2.concept_id AS standard_concept_id,
c2.concept_name AS standard_name,
c2.vocabulary_id AS standard_vocab,
c2.domain_id
FROM concept c1
JOIN concept_relationship cr
ON c1.concept_id = cr.concept_id_1
AND cr.relationship_id = 'Maps to'
AND cr.invalid_reason IS NULL
JOIN concept c2
ON cr.concept_id_2 = c2.concept_id
AND c2.standard_concept = 'S'
AND c2.invalid_reason IS NULL
WHERE c1.concept_code = 'E11'
AND c1.vocabulary_id = 'ICD10CM';
1.4. "Is a" — SNOMED hierarchy
Diabetes mellitus (concept_id = 201820)
↑ Is a
├── Type 1 diabetes mellitus (201254)
│ ↑ Is a
│ ├── Type 1 DM without complication (435216)
│ └── Type 1 DM with ketoacidosis (443727)
│
├── Type 2 diabetes mellitus (201826)
│ ↑ Is a
│ ├── Type 2 DM without complication (443732)
│ └── Type 2 DM with peripheral angiopathy (318712)
│
└── Gestational diabetes (4058243)
-- Tìm concept cha trực tiếp
SELECT
c_parent.concept_id,
c_parent.concept_name
FROM concept_relationship cr
JOIN concept c_parent ON cr.concept_id_2 = c_parent.concept_id
WHERE cr.concept_id_1 = 201826 -- Type 2 DM
AND cr.relationship_id = 'Is a'
AND cr.invalid_reason IS NULL;
-- Tìm concept con trực tiếp
SELECT
c_child.concept_id,
c_child.concept_name
FROM concept_relationship cr
JOIN concept c_child ON cr.concept_id_1 = c_child.concept_id
WHERE cr.concept_id_2 = 201826 -- Type 2 DM
AND cr.relationship_id = 'Is a'
AND cr.invalid_reason IS NULL;
2. RELATIONSHIP table
Column
Type
Description
relationship_id
VARCHAR(20)
PK
relationship_name
VARCHAR(255)
Relationship name
is_hierarchical
VARCHAR(1)
1 = hierarchy
defines_ancestry
VARCHAR(1)
1 = create ancestor
reverse_relationship_id
VARCHAR(20)
Reverse relationship
relationship_concept_id
INTEGER
FK → concept
3. CONCEPT_ANCESTOR — Full hierarchical tree
3.1. Table structure
Column
Type
Description
ancestor_concept_id
INTEGER
FK → CONCEPT (ancestor)
descendant_concept_id
INTEGER
FK → CONCEPT (descendant)
min_levels_of_separation
INTEGER
Minimum Distance
max_levels_of_separation
INTEGER
Maximum Distance
3.2. Compare CONCEPT_RELATIONSHIP vs CONCEPT_ANCESTOR
CONCEPT_RELATIONSHIP
CONCEPT_ANCESTOR
Content
Direct contact
All ancestors-descendants
Example
Type 2 DM → Is a → DM
Type 2 DM → all ancestors
Levels
Only 1 level
Includes n levels
Used for
ETL mapping
Hierarchical analysis
Includes self
No
✅ (min_level = 0)
3.3. Why is CONCEPT_ANCESTOR needed?
CONCEPT_RELATIONSHIP only has a "1 level" relationship. If you want to find all types of diabetes (type 1, type 2, gestational, neonatal...), you have to browse the tree many times. CONCEPT_ANCESTOR is pre-computed (pre-computed transitive closure).
-- Tìm TẤT CẢ concept thuộc nhóm "Diabetes mellitus"
-- Bao gồm bản thân + mọi hậu duệ
SELECT
ca.descendant_concept_id,
c.concept_name,
ca.min_levels_of_separation AS levels
FROM concept_ancestor ca
JOIN concept c ON ca.descendant_concept_id = c.concept_id
WHERE ca.ancestor_concept_id = 201820 -- Diabetes mellitus
AND c.standard_concept = 'S'
ORDER BY ca.min_levels_of_separation, c.concept_name;
-- Kết quả: ~300+ concepts bao gồm mọi loại & biến chứng
3.4. Analytical applications
-- Đếm BN có BẤT KỲ loại tiểu đường nào
SELECT COUNT(DISTINCT co.person_id) AS dm_patients
FROM condition_occurrence co
WHERE co.condition_concept_id IN (
SELECT descendant_concept_id
FROM concept_ancestor
WHERE ancestor_concept_id = 201820 -- Diabetes mellitus
);
-- So sánh: KHÔNG dùng ancestor (chỉ bắt 1 loại)
SELECT COUNT(DISTINCT co.person_id) AS dm_type2_only
FROM condition_occurrence co
WHERE co.condition_concept_id = 201826; -- Chỉ Type 2 DM
-- → Bỏ sót Type 1, gestational, neonatal, with complications...!
4. SOURCE_TO_CONCEPT_MAP — Custom mapping
Used when vocabulary does not have mapping (eg: internal code of Vietnam Hospital).
Column
Type
Description
source_code
VARCHAR(50)
Source code
source_concept_id
INTEGER
Source concept (0 if not available)
source_vocabulary_id
VARCHAR(20)
Custom vocabulary ID
source_code_description
VARCHAR(255)
Description
target_concept_id
INTEGER
FK → Standard Concept
target_vocabulary_id
VARCHAR(20)
Vocabulary destination
valid_start_date
DATE
valid_end_date
DATE
invalid_reason
VARCHAR(1)
-- Tạo mapping cho mã ICD-10-VN nội bộ
INSERT INTO source_to_concept_map (
source_code, source_concept_id,
source_vocabulary_id, source_code_description,
target_concept_id, target_vocabulary_id,
valid_start_date, valid_end_date
) VALUES
('E11', 0, 'VN_ICD10',
'Đái tháo đường type 2',
201826, 'SNOMED',
'2024-01-01', '2099-12-31'),
('METFORMIN500', 0, 'VN_DRUG',
'Metformin 500mg viên nén',
1503328, 'RxNorm',
'2024-01-01', '2099-12-31');
5. CONCEPT_SYNONYM — Synonym name
Column
Type
Description
concept_id
INTEGER
FK → CONCEPT
concept_synonym_name
VARCHAR(1000)
Synonym name
language_concept_id
INTEGER
Language (4180186 = English)
-- Tìm concept qua tên đồng nghĩa
SELECT DISTINCT c.concept_id, c.concept_name
FROM concept_synonym cs
JOIN concept c ON cs.concept_id = c.concept_id
WHERE LOWER(cs.concept_synonym_name) LIKE '%heart attack%'
AND c.standard_concept = 'S';
-- Tìm được: Acute myocardial infarction