ER Diagram - ระบบเภสัชกรรม (Pharmacy System)
📋 สารบัญ
ภาพรวม
ระบบเภสัชกรรมประกอบด้วย Entity หลัก 10 ตัว ที่มีความสัมพันธ์กันในการจัดการยาและการจ่ายยา
Core Entities
- Patient - ผู้ป่วย
- Visit - การมาพบแพทย์ (OPD/IPD)
- Prescription - ใบสั่งยา
- PrescriptionItem - รายการยาในใบสั่ง
- Medication - ข้อมูลยา (Master)
- Dispensing - การจ่ายยา
- DispensingItem - รายการยาที่จ่าย
- DrugInteraction - ปฏิกิริยาระหว่างยา
- MedicationTemplate - ชุดยาแพทย์
- MAR - Medication Administration Record (IPD)
Main ER Diagram
erDiagram
PATIENT ||--o{ VISIT : "has"
PATIENT ||--o{ ALLERGY : "has"
VISIT ||--|| PRESCRIPTION : "generates"
VISIT }o--|| DOCTOR : "treated by"
VISIT }o--|| CLINIC : "at"
PRESCRIPTION ||--|{ PRESCRIPTION_ITEM : "contains"
PRESCRIPTION_ITEM }o--|| MEDICATION : "references"
PRESCRIPTION ||--o| DISPENSING : "results in"
DISPENSING ||--|{ DISPENSING_ITEM : "contains"
DISPENSING_ITEM }o--|| MEDICATION : "references"
DISPENSING }o--|| PHARMACIST : "dispensed by"
MEDICATION ||--o{ DRUG_INTERACTION : "interacts with"
MEDICATION ||--o{ INVENTORY : "has stock"
DOCTOR ||--o{ MEDICATION_TEMPLATE : "creates"
MEDICATION_TEMPLATE ||--|{ TEMPLATE_ITEM : "contains"
TEMPLATE_ITEM }o--|| MEDICATION : "references"
PRESCRIPTION_ITEM ||--o{ MAR : "tracked in"
MAR }o--|| NURSE : "administered by"Entity Descriptions
1. PATIENT (ผู้ป่วย)
Primary Key: hn (Hospital Number)
| Field | Type | Description |
|---|---|---|
| hn | VARCHAR(10) | HN000123 |
| title | VARCHAR(10) | mr, mrs, ms |
| firstName | VARCHAR(100) | ชื่อ |
| lastName | VARCHAR(100) | นามสกุล |
| dateOfBirth | DATE | วันเกิด |
| age | INT | อายุ (คำนวณ) |
| gender | ENUM | male, female, other |
| bloodGroup | VARCHAR(5) | O+, A+, B+, AB+ |
| allergies | JSON | รายการแพ้ยา |
| chronicDiseases | JSON | โรคประจำตัว |
| insuranceType | VARCHAR(20) | uc, social, private |
Relationships: - 1 Patient → N Visits - 1 Patient → N Allergies
2. VISIT (การมาพบแพทย์)
Primary Key: visitId (VN)
| Field | Type | Description |
|---|---|---|
| visitId | VARCHAR(20) | VISIT-2024-001 |
| hn | VARCHAR(10) | FK → Patient |
| visitType | ENUM | OPD, IPD |
| visitDate | DATETIME | วันที่มาพบ |
| clinicId | VARCHAR(10) | FK → Clinic |
| doctorId | VARCHAR(10) | FK → Doctor |
| chiefComplaint | TEXT | อาการสำคัญ |
| diagnosis | JSON | Array of |
| status | ENUM | active, completed, cancelled |
For IPD: | Field | Type | Description | |-------|------|-------------| | an | VARCHAR(20) | Admission Number | | admitDate | DATETIME | วันเข้ารับการรักษา | | dischargeDate | DATETIME | วันจำหน่าย | | ward | VARCHAR(50) | หอผู้ป่วย | | bed | VARCHAR(20) | เตียง |
Relationships: - N Visits → 1 Patient - N Visits → 1 Doctor - N Visits → 1 Clinic - 1 Visit → 1 Prescription
3. PRESCRIPTION (ใบสั่งยา)
Primary Key: prescriptionId
| Field | Type | Description |
|---|---|---|
| prescriptionId | VARCHAR(20) | RX2024123 |
| visitId | VARCHAR(20) | FK → Visit |
| hn | VARCHAR(10) | FK → Patient |
| prescribedBy | VARCHAR(10) | FK → Doctor |
| prescribedAt | DATETIME | วันเวลาที่สั่ง |
| status | ENUM | ordered, dispensed, cancelled |
| type | ENUM | OPD, IPD |
| totalCost | DECIMAL(10,2) | ยอดรวม |
| copay | DECIMAL(10,2) | ค่าใช้จ่ายผู้ป่วย |
| insuranceCovered | DECIMAL(10,2) | ประกันจ่าย |
Relationships: - 1 Prescription → 1 Visit - 1 Prescription → N PrescriptionItems - 1 Prescription → 0..1 Dispensing
4. PRESCRIPTION_ITEM (รายการยาในใบสั่ง)
Primary Key: itemId
| Field | Type | Description |
|---|---|---|
| itemId | VARCHAR(20) | PRESC-ITEM-001 |
| prescriptionId | VARCHAR(20) | FK → Prescription |
| drugCode | VARCHAR(20) | FK → Medication |
| dosage | VARCHAR(50) | 1-2 tab |
| frequency | VARCHAR(20) | tid, bid, qid |
| timing | VARCHAR(20) | ac, pc, hs |
| route | VARCHAR(20) | oral, IV, IM |
| quantity | INT | จำนวน |
| duration | INT | จำนวนวัน |
| instruction | TEXT | คำแนะนำ |
| unitPrice | DECIMAL(10,2) | ราคาต่อหน่วย |
| totalPrice | DECIMAL(10,2) | ราคารวม |
For IPD: | Field | Type | Description | |-------|------|-------------| | orderType | ENUM | continue, stat, prn, one_day | | administrationTime | JSON | ["06:00", "18:00"] | | startDate | DATE | วันเริ่ม (continue) | | stopDate | DATE | วันหยุด (continue) | | prnCondition | TEXT | เงื่อนไข (PRN) | | urgency | ENUM | routine, urgent, critical (STAT) |
Relationships: - N PrescriptionItems → 1 Prescription - N PrescriptionItems → 1 Medication - 1 PrescriptionItem → N MAR (IPD only)
5. MEDICATION (ข้อมูลยา - Master)
Primary Key: drugCode
| Field | Type | Description |
|---|---|---|
| drugCode | VARCHAR(20) | MED001 |
| tmtCode | VARCHAR(20) | TMT Code |
| genericName | VARCHAR(100) | ชื่อสามัญ |
| tradeName | VARCHAR(100) | ชื่อการค้า |
| strength | VARCHAR(20) | ความแรง |
| unit | VARCHAR(10) | mg, ml |
| dosageForm | VARCHAR(20) | tablet, capsule, syrup |
| manufacturer | VARCHAR(100) | ผู้ผลิต |
| drugCategory | VARCHAR(20) | analgesic, antibiotic |
| costPrice | DECIMAL(10,2) | ต้นทุน |
| opdPrice | DECIMAL(10,2) | ราคา OPD |
| ipdPrice | DECIMAL(10,2) | ราคา IPD |
| indication | TEXT | ข้อบ่งใช้ |
| contraindication | TEXT | ข้อห้าม |
| sideEffects | TEXT | ผลข้างเคียง |
| pregnancyCategory | VARCHAR(1) | A, B, C, D, X |
| lactationSafe | BOOLEAN | ปลอดภัยในหญิงให้นมบุตร |
| pediatricSafe | BOOLEAN | ปลอดภัยในเด็ก |
| maxDailyDose | VARCHAR(50) | ขนาดสูงสุดต่อวัน |
Relationships: - 1 Medication → N PrescriptionItems - 1 Medication → N DispensingItems - 1 Medication → N DrugInteractions - 1 Medication → 1 Inventory
6. DISPENSING (การจ่ายยา)
Primary Key: dispensingId
| Field | Type | Description |
|---|---|---|
| dispensingId | VARCHAR(20) | DISP-2024-001 |
| prescriptionId | VARCHAR(20) | FK → Prescription |
| hn | VARCHAR(10) | FK → Patient |
| dispensedBy | VARCHAR(10) | FK → Pharmacist |
| dispensedAt | DATETIME | วันเวลาที่จ่าย |
| type | ENUM | OPD, IPD |
| status | ENUM | prepared, dispensed, returned |
| totalCost | DECIMAL(10,2) | ยอดรวม |
For IPD: | Field | Type | Description | |-------|------|-------------| | ward | VARCHAR(50) | หอผู้ป่วย | | sentAt | DATETIME | เวลาส่งยา | | receivedBy | VARCHAR(10) | FK → Nurse (ผู้รับยา) | | receivedAt | DATETIME | เวลารับยา |
Relationships: - 1 Dispensing → 1 Prescription - 1 Dispensing → N DispensingItems - N Dispensing → 1 Pharmacist
7. DISPENSING_ITEM (รายการยาที่จ่าย)
Primary Key: itemId
| Field | Type | Description |
|---|---|---|
| itemId | VARCHAR(20) | DISP-ITEM-001 |
| dispensingId | VARCHAR(20) | FK → Dispensing |
| drugCode | VARCHAR(20) | FK → Medication |
| quantity | INT | จำนวนที่จ่าย |
| batchNumber | VARCHAR(50) | Batch/Lot number |
| expiryDate | DATE | วันหมดอายุ |
| unitPrice | DECIMAL(10,2) | ราคาต่อหน่วย |
| totalPrice | DECIMAL(10,2) | ราคารวม |
Relationships: - N DispensingItems → 1 Dispensing - N DispensingItems → 1 Medication
8. DRUG_INTERACTION (ปฏิกิริยาระหว่างยา)
Primary Key: interactionId
| Field | Type | Description |
|---|---|---|
| interactionId | VARCHAR(20) | INT001 |
| drug1Code | VARCHAR(20) | FK → Medication |
| drug2Code | VARCHAR(20) | FK → Medication |
| severity | ENUM | major, moderate, minor |
| description | TEXT | คำอธิบาย |
| mechanism | TEXT | กลไก |
| effect | TEXT | ผลที่เกิด |
| management | TEXT | การจัดการ |
| references | JSON | แหล่งอ้างอิง |
Relationships: - N DrugInteractions → 2 Medications
9. MEDICATION_TEMPLATE (ชุดยาแพทย์)
Primary Key: templateId
| Field | Type | Description |
|---|---|---|
| templateId | VARCHAR(20) | TPL001 |
| name | VARCHAR(100) | ชื่อชุดยา |
| type | ENUM | personal, shared |
| doctorId | VARCHAR(10) | FK → Doctor (personal only) |
| disease | VARCHAR(100) | โรค/อาการ |
| category | VARCHAR(50) | หมวดหมู่ |
| guidelines | VARCHAR(100) | แนวทาง CPG |
| createdBy | VARCHAR(10) | FK → User |
| createdAt | DATETIME | วันที่สร้าง |
| approvedBy | VARCHAR(10) | ผู้อนุมัติ (shared only) |
Relationships: - N Templates → 1 Doctor (personal) - 1 Template → N TemplateItems
10. MAR (Medication Administration Record)
Primary Key: marId
| Field | Type | Description |
|---|---|---|
| marId | VARCHAR(20) | MAR-2024-001 |
| prescriptionItemId | VARCHAR(20) | FK → PrescriptionItem |
| an | VARCHAR(20) | FK → Visit (IPD) |
| date | DATE | วันที่ |
| scheduledTime | TIME | เวลาที่กำหนด |
| actualTime | TIME | เวลาที่ให้จริง |
| administeredBy | VARCHAR(10) | FK → Nurse |
| status | ENUM | given, missed, refused, held |
| remarks | TEXT | หมายเหตุ |
Relationships: - N MAR → 1 PrescriptionItem - N MAR → 1 Nurse
Relationships
Core Relationships
1. Patient → Visit → Prescription
erDiagram
PATIENT ||--o{ VISIT : "1:N"
VISIT ||--|| PRESCRIPTION : "1:1"
PATIENT {
string hn PK
string firstName
string lastName
}
VISIT {
string visitId PK
string hn FK
string visitType
datetime visitDate
}
PRESCRIPTION {
string prescriptionId PK
string visitId FK
string hn FK
datetime prescribedAt
}Cardinality: - 1 Patient มี 0..N Visits - 1 Visit มี 0..1 Prescription - 1 Prescription เป็นของ 1 Patient
2. Prescription → Items → Medication
erDiagram
PRESCRIPTION ||--|{ PRESCRIPTION_ITEM : "1:N"
PRESCRIPTION_ITEM }o--|| MEDICATION : "N:1"
PRESCRIPTION {
string prescriptionId PK
datetime prescribedAt
string status
}
PRESCRIPTION_ITEM {
string itemId PK
string prescriptionId FK
string drugCode FK
string dosage
int quantity
}
MEDICATION {
string drugCode PK
string genericName
string tradeName
decimal price
}Cardinality: - 1 Prescription มี 1..N PrescriptionItems - N PrescriptionItems อ้างถึง 1 Medication - 1 Medication ถูกใช้ใน N PrescriptionItems
3. Prescription → Dispensing → Items
erDiagram
PRESCRIPTION ||--o| DISPENSING : "1:0..1"
DISPENSING ||--|{ DISPENSING_ITEM : "1:N"
DISPENSING_ITEM }o--|| MEDICATION : "N:1"
PRESCRIPTION {
string prescriptionId PK
string status
}
DISPENSING {
string dispensingId PK
string prescriptionId FK
datetime dispensedAt
string dispensedBy FK
}
DISPENSING_ITEM {
string itemId PK
string dispensingId FK
string drugCode FK
int quantity
}
MEDICATION {
string drugCode PK
string tradeName
}Cardinality: - 1 Prescription มี 0..1 Dispensing - 1 Dispensing มี 1..N DispensingItems - N DispensingItems อ้างถึง 1 Medication
4. Drug Interactions (Self-referencing)
erDiagram
MEDICATION ||--o{ DRUG_INTERACTION : "drug1"
MEDICATION ||--o{ DRUG_INTERACTION : "drug2"
MEDICATION {
string drugCode PK
string genericName
string tradeName
}
DRUG_INTERACTION {
string interactionId PK
string drug1Code FK
string drug2Code FK
string severity
text description
}Cardinality: - 1 Medication มี N DrugInteractions (เป็น drug1) - 1 Medication มี N DrugInteractions (เป็น drug2) - 1 DrugInteraction เชื่อม 2 Medications
5. IPD: PrescriptionItem → MAR
erDiagram
PRESCRIPTION_ITEM ||--o{ MAR : "1:N"
MAR }o--|| NURSE : "N:1"
PRESCRIPTION_ITEM {
string itemId PK
string drugCode FK
json administrationTime
string orderType
}
MAR {
string marId PK
string prescriptionItemId FK
date date
time scheduledTime
time actualTime
string administeredBy FK
string status
}
NURSE {
string nurseId PK
string name
}Cardinality: - 1 PrescriptionItem (IPD) มี N MAR records - N MAR records → 1 Nurse (ผู้ให้ยา)
Detail Diagrams
OPD Workflow Entities
erDiagram
OPD_VISIT ||--|| OPD_PRESCRIPTION : has
OPD_PRESCRIPTION ||--|{ PRESCRIPTION_ITEM : contains
OPD_PRESCRIPTION ||--|| OPD_QUEUE : enters
OPD_QUEUE ||--o| OPD_DISPENSING : results_in
OPD_DISPENSING ||--|{ DISPENSING_ITEM : contains
OPD_VISIT {
string visitId PK
string hn FK
string clinicId FK
datetime visitDate
}
OPD_PRESCRIPTION {
string prescriptionId PK
string visitId FK
string status
}
OPD_QUEUE {
string queueId PK
string prescriptionId FK
string status
int queueNumber
}
OPD_DISPENSING {
string dispensingId PK
string prescriptionId FK
datetime dispensedAt
}IPD Workflow Entities
erDiagram
IPD_ADMISSION ||--|{ IPD_PRESCRIPTION : has
IPD_PRESCRIPTION ||--|{ PRESCRIPTION_ITEM : contains
PRESCRIPTION_ITEM ||--o{ MAR : tracked_in
IPD_PRESCRIPTION ||--o{ IPD_DISPENSING : results_in
IPD_DISPENSING }o--|| WARD : sent_to
IPD_ADMISSION {
string an PK
string hn FK
string ward
string bed
datetime admitDate
}
IPD_PRESCRIPTION {
string prescriptionId PK
string an FK
string orderType
datetime startDate
datetime stopDate
}
MAR {
string marId PK
string prescriptionItemId FK
datetime scheduledTime
datetime actualTime
string status
}
WARD {
string wardId PK
string wardName
int capacity
}Template & Master Data
erDiagram
DOCTOR ||--o{ MEDICATION_TEMPLATE : creates
MEDICATION_TEMPLATE ||--|{ TEMPLATE_ITEM : contains
TEMPLATE_ITEM }o--|| MEDICATION : references
MEDICATION ||--o{ INVENTORY : has_stock
MEDICATION ||--o{ DRUG_INTERACTION : interacts
DOCTOR {
string doctorId PK
string name
string specialty
}
MEDICATION_TEMPLATE {
string templateId PK
string doctorId FK
string name
string type
}
TEMPLATE_ITEM {
string itemId PK
string templateId FK
string drugCode FK
string dosage
}
MEDICATION {
string drugCode PK
string genericName
decimal price
}
INVENTORY {
string inventoryId PK
string drugCode FK
int stockQuantity
int reorderLevel
}Index Recommendations
Primary Indexes
-- Already covered by PRIMARY KEY
PK_Patient(hn)
PK_Visit(visitId)
PK_Prescription(prescriptionId)
PK_PrescriptionItem(itemId)
PK_Medication(drugCode)
PK_Dispensing(dispensingId)
Foreign Key Indexes
IDX_Visit_HN(hn)
IDX_Visit_DoctorId(doctorId)
IDX_Prescription_VisitId(visitId)
IDX_PrescriptionItem_PrescriptionId(prescriptionId)
IDX_PrescriptionItem_DrugCode(drugCode)
IDX_Dispensing_PrescriptionId(prescriptionId)
IDX_MAR_PrescriptionItemId(prescriptionItemId)
Search Indexes
IDX_Medication_GenericName(genericName)
IDX_Medication_TradeName(tradeName)
IDX_Prescription_Status_Date(status, prescribedAt)
IDX_Visit_Date(visitDate)
IDX_DrugInteraction_Drugs(drug1Code, drug2Code)
Composite Indexes
IDX_Prescription_Patient_Date(hn, prescribedAt DESC)
IDX_Dispensing_Date_Status(dispensedAt, status)
IDX_MAR_Date_Status(date, status)
Data Integrity Constraints
Foreign Key Constraints
FK_Visit_Patient: Visit.hn → Patient.hn
FK_Visit_Doctor: Visit.doctorId → Doctor.doctorId
FK_Prescription_Visit: Prescription.visitId → Visit.visitId
FK_PrescriptionItem_Prescription: PrescriptionItem.prescriptionId → Prescription.prescriptionId
FK_PrescriptionItem_Medication: PrescriptionItem.drugCode → Medication.drugCode
FK_Dispensing_Prescription: Dispensing.prescriptionId → Prescription.prescriptionId
FK_DispensingItem_Medication: DispensingItem.drugCode → Medication.drugCode
FK_MAR_PrescriptionItem: MAR.prescriptionItemId → PrescriptionItem.itemId
Check Constraints
CHK_Prescription_Status: status IN ('ordered', 'dispensed', 'cancelled')
CHK_Severity: severity IN ('major', 'moderate', 'minor')
CHK_OrderType: orderType IN ('continue', 'stat', 'prn', 'one_day', 'discharge')
CHK_MAR_Status: status IN ('given', 'missed', 'refused', 'held', 'na')
CHK_Quantity: quantity > 0
CHK_Price: price >= 0
Business Rules
-- ใบสั่งยาต้องมีอย่างน้อย 1 รายการ
RULE: COUNT(PrescriptionItem WHERE prescriptionId = X) >= 1
-- การจ่ายยาต้องมีสถานะใบสั่งยาเป็น 'ordered'
RULE: Prescription.status = 'ordered' BEFORE Dispensing created
-- MAR ใช้เฉพาะ IPD
RULE: MAR.prescriptionItemId → PrescriptionItem WHERE Visit.visitType = 'IPD'
-- Drug Interaction ต้องไม่ซ้ำคู่ยา
RULE: UNIQUE(drug1Code, drug2Code) OR UNIQUE(drug2Code, drug1Code)
Sample Queries
1. ดึงประวัติการใช้ยาของผู้ป่วย
SELECT
p.prescriptionId,
p.prescribedAt,
pi.drugCode,
m.tradeName,
pi.dosage,
pi.frequency,
pi.quantity,
d.dispensedAt
FROM Prescription p
INNER JOIN PrescriptionItem pi ON p.prescriptionId = pi.prescriptionId
INNER JOIN Medication m ON pi.drugCode = m.drugCode
LEFT JOIN Dispensing d ON p.prescriptionId = d.prescriptionId
WHERE p.hn = 'HN000123'
ORDER BY p.prescribedAt DESC
LIMIT 10;
2. ตรวจสอบ Drug Interaction
SELECT
di.severity,
m1.tradeName AS drug1,
m2.tradeName AS drug2,
di.description,
di.management
FROM PrescriptionItem pi1
INNER JOIN PrescriptionItem pi2
ON pi1.prescriptionId = pi2.prescriptionId
AND pi1.itemId < pi2.itemId
INNER JOIN DrugInteraction di
ON (di.drug1Code = pi1.drugCode AND di.drug2Code = pi2.drugCode)
OR (di.drug1Code = pi2.drugCode AND di.drug2Code = pi1.drugCode)
INNER JOIN Medication m1 ON pi1.drugCode = m1.drugCode
INNER JOIN Medication m2 ON pi2.drugCode = m2.drugCode
WHERE pi1.prescriptionId = 'RX2024123';
3. MAR Summary สำหรับหอผู้ป่วย
SELECT
v.an,
p.hn,
p.patientName,
pi.drugCode,
m.tradeName,
mar.scheduledTime,
mar.actualTime,
mar.status,
n.nurseName
FROM MAR mar
INNER JOIN PrescriptionItem pi ON mar.prescriptionItemId = pi.itemId
INNER JOIN Prescription pr ON pi.prescriptionId = pr.prescriptionId
INNER JOIN Visit v ON pr.visitId = v.visitId
INNER JOIN Patient p ON v.hn = p.hn
INNER JOIN Medication m ON pi.drugCode = m.drugCode
LEFT JOIN Nurse n ON mar.administeredBy = n.nurseId
WHERE v.ward = 'Medical Ward'
AND mar.date = CURDATE()
ORDER BY mar.scheduledTime;
4. คิวจ่ายยา OPD
SELECT
pr.prescriptionId,
p.hn,
p.firstName,
p.lastName,
pr.prescribedAt,
pr.status,
COUNT(pi.itemId) AS itemCount,
SUM(pi.totalPrice) AS totalCost
FROM Prescription pr
INNER JOIN Patient p ON pr.hn = p.hn
INNER JOIN PrescriptionItem pi ON pr.prescriptionId = pi.prescriptionId
WHERE pr.type = 'OPD'
AND pr.status IN ('ordered', 'ready_to_dispense')
GROUP BY pr.prescriptionId
ORDER BY pr.prescribedAt;
Summary Statistics
| Entity | Estimated Rows | Growth Rate |
|---|---|---|
| Patient | 50,000+ | ~100/day |
| Visit | 500,000+ | ~500/day |
| Prescription | 400,000+ | ~400/day |
| PrescriptionItem | 1,200,000+ | ~1,200/day |
| Medication | ~5,000 | Stable |
| Dispensing | 380,000+ | ~380/day |
| DispensingItem | 1,100,000+ | ~1,100/day |
| DrugInteraction | ~1,000 | Stable |
| MedicationTemplate | ~200 | +5/month |
| MAR | 50,000+ | ~500/day (IPD only) |
สร้างเมื่อ: 6 มกราคม 2026
Version: 1.0
เอกสารอ้างอิง: DATA_STRUCTURES.md