Three additional clinical tables: DEVICE_EXPOSURE for medical devices (stent, pacemaker), SPECIMEN for specimens (blood, tissue), NOTE and NOTE_NLP for clinical notes and NLP processing.
Introduction
This article introduces three additional clinical tables: DEVICE_EXPOSURE (implantable/patient-mounted medical device), SPECIMEN (specimen used for testing), and NOTE / NOTE_NLP (free-text clinical notes). Although less common than Condition/Drug/Procedure, they are increasingly important in research.
New in 5.4: production_id separate from unique_device_id, used for production identifier (serial number, lot number).
2. SPECIMEN — Patient sample
2.1. Table structure
Column
Type
Required
Description
specimen_id
INTEGER
✅ PK
Unique ID
person_id
INTEGER
✅ FK
Patients
specimen_concept_id
INTEGER
✅
Sample type (SNOMED)
specimen_type_concept_id
INTEGER
✅
Data source
specimen_date
DATE
✅
Sampling date
specimen_datetime
DATETIME
quantity
FLOAT
Sample quantity
unit_concept_id
INTEGER
Unit (mL, g...)
anatomic_site_concept_id
INTEGER
Sampling location
disease_status_concept_id
INTEGER
Medical condition
specimen_source_id
VARCHAR(50)
Original barcode
specimen_source_value
VARCHAR(50)
Original model type
unit_source_value
VARCHAR(50)
Original unit
anatomic_site_source_value
VARCHAR(50)
Original location
disease_status_source_value
VARCHAR(50)
Original state
2.2. Popular model type
specimen_concept_id
Sample Type
Vietnamese
4045667
Blood specimens
Blood sample
4048506
Urine specimen
Urine
4002890
Serum specimens
Serum
4219166
Tissue specimens
Tissue samples
4045666
Sputum specimens
Dam
4260640
Cerebrospinal fluid
Cerebrospinal fluid
4000626
Bone marrow
Bone marrow
2.3. Specimen → Measurement link
-- Mẫu máu lấy ngày 15/6
INSERT INTO specimen VALUES (
150001, 100001, 4045667, -- Blood specimen
32817, '2024-06-15', NULL,
5, 8587, -- 5 mL
4236402, -- Antecubital vein
NULL, 'BARCODE_123456',
'MAU_MAU', 'mL', 'tinh_mach_khuu_tay', NULL
);
-- Xét nghiệm Glucose từ mẫu máu này
-- (liên kết qua person_id + measurement_date + logic ETL)
Note: SPECIMEN does not have a direct FK to MEASUREMENT in CDM 5.4. Links are usually via person_id + date or via FACT_RELATIONSHIP.
3. NOTE — Clinical notes
3.1. NOTE table structure
Column
Type
Required
Description
note_id
INTEGER
✅ PK
Unique ID
person_id
INTEGER
✅ FK
Patients
note_date
DATE
✅
Date of note
note_datetime
DATETIME
note_type_concept_id
INTEGER
✅
Note type
note_class_concept_id
INTEGER
✅
Classification
note_title
VARCHAR(250)
Title
note_text
CLOB
✅
Content
encoding_concept_id
INTEGER
✅
Encoding (UTF-8)
language_concept_id
INTEGER
✅
Language
provider_id
INTEGER
FK
Doctor writes
visit_occurrence_id
INTEGER
FK
Visit
visit_detail_id
INTEGER
FK
note_source_value
VARCHAR(50)
note_event_id
BIGINT
⭐ CDM 5.4
note_event_field_concept_id
INTEGER
⭐ CDM 5.4
3.2. common note_class_concept_id
Concept ID
Note Class
Vietnamese
44814637
Discharge summary
Discharge Summary
44814638
Emergency department note
Emergency notes
44814639
Inpatient note
Boarding Notes
44814640
Outpatient note
Outpatient Notes
44814641
Pathology report
Pathological results
44814642
Radiology report
X-ray/CT results
44814643
Surgical notes
Surgical records
3.3. For example
-- Tóm tắt xuất viện
INSERT INTO note (
note_id, person_id, note_date,
note_type_concept_id, note_class_concept_id,
note_title, note_text,
encoding_concept_id, language_concept_id,
provider_id, visit_occurrence_id
) VALUES (
160001, 100001, '2024-06-20',
32817, -- EHR
44814637, -- Discharge summary
'TÓM TẮT XUẤT VIỆN',
'Bệnh nhân nam, 55 tuổi, nhập viện vì đau ngực. '
|| 'Chẩn đoán: Nhồi máu cơ tim cấp. '
|| 'Đã can thiệp mạch vành, đặt 2 stent. '
|| 'Xuất viện ngày 20/06/2024 với đơn thuốc: '
|| 'Aspirin 81mg, Clopidogrel 75mg, Atorvastatin 40mg. '
|| 'Tái khám sau 1 tháng.',
32678, -- UTF-8
4181730, -- Vietnamese
5001, 50001
);
4. NOTE_NLP — NLP processing results
Column
Type
Required
Description
note_nlp_id
BIGINT
✅ PK
ID
note_id
INTEGER
✅ FK
Original Note
section_concept_id
INTEGER
Section (HPI, Assessment...)
snippet
VARCHAR(250)
Excerpted text
offset
VARCHAR(50)
Location in note
lexical_variant
VARCHAR(250)
✅
Root word/phrase
note_nlp_concept_id
INTEGER
✅
Concept discovery
note_nlp_source_concept_id
INTEGER
nlp_system
VARCHAR(250)
Name NLP tool
nlp_date
DATE
✅
NLP Run Day
nlp_datetime
DATETIME
term_exists
VARCHAR(1)
Y/N — confirm or deny
term_temporal
VARCHAR(50)
Past/Present/Future
term_modifiers
VARCHAR(2000)
Modifier (negation, family...)
-- NLP phát hiện "Nhồi máu cơ tim" từ tóm tắt xuất viện
INSERT INTO note_nlp (
note_nlp_id, note_id,
snippet, offset, lexical_variant,
note_nlp_concept_id,
nlp_system, nlp_date,
term_exists, term_temporal
) VALUES (
170001, 160001,
'Chẩn đoán: Nhồi máu cơ tim cấp',
'120-153',
'nhồi máu cơ tim cấp',
4329847, -- SNOMED: AMI
'cTAKES-VN', '2024-07-01',
'Y', -- Có (không phải negation)
'Present' -- Hiện tại
);
5. When to use which table?
Dữ liệu gốc
│
├── Thiết bị gắn/cấy trên BN ──→ DEVICE_EXPOSURE
│ (stent, pacemaker, kính)
│
├── Mẫu bệnh phẩm ──────────→ SPECIMEN
│ (máu, nước tiểu, mô)
│
├── Văn bản tự do ───────────→ NOTE
│ (tóm tắt xuất viện,
│ biên bản phẫu thuật)
│
└── Kết quả NLP từ NOTE ─────→ NOTE_NLP
(concepts phát hiện tự động)
Summary
DEVICE_EXPOSURE: implanted/attached medical device, uses UDI for tracing
SPECIMEN: patient sample, indirectly linked to MEASUREMENT
NOTE: free text clinical notes, Vietnamese language support