Database Relationships - Laboratory Master Data

ความสัมพันธ์ระหว่าง Tables และ Entity Relationship Diagram (ERD)

📊 ERD Overview

erDiagram
    %% Core Master Data
    lab_categories ||--o{ lab_items : "categorizes"
    specimen_types ||--o{ lab_items : "requires"
    container_types ||--o{ lab_items : "uses"

    %% Lab Items & Panels (Many-to-Many)
    lab_items ||--o{ lab_panel_items : "belongs to"
    lab_panels ||--o{ lab_panel_items : "contains"

    %% Reference Values
    lab_items ||--o{ lab_normal_ranges : "has multiple ranges"
    lab_items ||--o| lab_critical_values : "has one set"

    %% Table Definitions
    lab_categories {
        bigint id PK
        varchar code UK
        varchar name_th
        varchar name_en
        varchar color
        int display_order
        tinyint is_active
    }

    specimen_types {
        bigint id PK
        varchar code UK
        varchar name_th
        varchar name_en
        text description
        int display_order
        tinyint is_active
    }

    container_types {
        bigint id PK
        varchar code UK
        varchar name_th
        varchar name_en
        varchar color
        text description
        int display_order
        tinyint is_active
    }

    lab_items {
        bigint id PK
        varchar lab_code UK
        varchar lab_name_th
        varchar lab_name_en
        bigint category_id FK
        bigint specimen_type_id FK
        bigint container_type_id FK
        decimal specimen_volume
        varchar unit
        text method
        int turn_around_time
        decimal price
        decimal cost
        tinyint is_out_lab
        varchar out_lab_name
        varchar out_lab_code
        decimal out_lab_cost
        tinyint is_active
    }

    lab_panels {
        bigint id PK
        varchar panel_code UK
        varchar panel_name_th
        varchar panel_name_en
        text description
        decimal price
        decimal cost
        tinyint is_active
    }

    lab_panel_items {
        bigint id PK
        bigint panel_id FK
        bigint lab_item_id FK
        int display_order
    }

    lab_normal_ranges {
        bigint id PK
        bigint lab_item_id FK
        int age_min_days
        int age_max_days
        enum gender
        enum range_type
        decimal min_value
        decimal max_value
        text text_value
        text possible_values
    }

    lab_critical_values {
        bigint id PK
        bigint lab_item_id FK,UK
        decimal critical_low
        decimal critical_high
        decimal panic_low
        decimal panic_high
        text alert_message
        text action_required
        tinyint notify_immediately
    }

---

🔗 Relationship Details

1. lab_categories → lab_items (One-to-Many)

Type: Optional (lab_items.category_id NULL allowed)

-- Foreign Key
ALTER TABLE lab_items
  ADD CONSTRAINT fk_lab_item_category 
  FOREIGN KEY (category_id) 
  REFERENCES lab_categories(id) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

Description: - 1 Category มีได้หลาย Lab Items - 1 Lab Item อยู่ในได้ 1 Category (หรือไม่มี) - ลบ Category → lab_items.category_id = NULL (ไม่ลบ Lab Item)

Business Logic:

-- ดึง Lab Items ทั้งหมดในหมวด HEMATOLOGY
SELECT li.* 
FROM lab_items li
JOIN lab_categories lc ON li.category_id = lc.id
WHERE lc.code = 'HEMA' AND lc.is_active = 1 AND li.is_active = 1;

---

2. specimen_types → lab_items (One-to-Many)

Type: Optional (lab_items.specimen_type_id NULL allowed)

-- Foreign Key
ALTER TABLE lab_items
  ADD CONSTRAINT fk_lab_item_specimen 
  FOREIGN KEY (specimen_type_id) 
  REFERENCES specimen_types(id) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

Description: - 1 Specimen Type ใช้ได้กับหลาย Lab Items - 1 Lab Item ใช้ได้ 1 Specimen Type (หรือไม่ระบุ) - ลบ Specimen Type → lab_items.specimen_type_id = NULL

Business Logic:

-- ดึง Lab Items ที่ใช้สิ่งส่งตรวจเป็น "เลือด"
SELECT li.* 
FROM lab_items li
JOIN specimen_types st ON li.specimen_type_id = st.id
WHERE st.code = 'BLOOD' AND st.is_active = 1 AND li.is_active = 1;

---

3. container_types → lab_items (One-to-Many)

Type: Optional (lab_items.container_type_id NULL allowed)

-- Foreign Key
ALTER TABLE lab_items
  ADD CONSTRAINT fk_lab_item_container 
  FOREIGN KEY (container_type_id) 
  REFERENCES container_types(id) 
  ON DELETE SET NULL 
  ON UPDATE CASCADE;

Description: - 1 Container Type ใช้ได้กับหลาย Lab Items - 1 Lab Item ใช้ได้ 1 Container Type (หรือไม่ระบุ) - ลบ Container Type → lab_items.container_type_id = NULL

Business Logic:

-- ดึง Lab Items ที่ใช้หลอด EDTA (จุกม่วง)
SELECT li.* 
FROM lab_items li
JOIN container_types ct ON li.container_type_id = ct.id
WHERE ct.code = 'EDTA' AND ct.is_active = 1 AND li.is_active = 1;

---

4. lab_panels ↔ lab_items (Many-to-Many via lab_panel_items)

Type: Required (Junction Table Pattern)

-- Foreign Keys ใน Junction Table
ALTER TABLE lab_panel_items
  ADD CONSTRAINT fk_panel_item_panel 
    FOREIGN KEY (panel_id) 
    REFERENCES lab_panels(id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE,
  ADD CONSTRAINT fk_panel_item_lab 
    FOREIGN KEY (lab_item_id) 
    REFERENCES lab_items(id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE;

-- Unique Constraint (ป้องกัน item ซ้ำใน panel เดียวกัน)
ALTER TABLE lab_panel_items
  ADD UNIQUE KEY uk_panel_item (panel_id, lab_item_id);

Description: - 1 Panel ประกอบด้วยหลาย Lab Items - 1 Lab Item สามารถอยู่ในหลาย Panels - ลบ Panel → ลบ items ใน panel (CASCADE) - ลบ Lab Item → ลบออกจากทุก panels (CASCADE)

Business Logic:

-- ดึงรายการตรวจทั้งหมดใน Panel 'LFT'
SELECT 
  lp.panel_code,
  lp.panel_name_th,
  li.lab_code,
  li.lab_name_th,
  li.is_out_lab,
  li.out_lab_name,
  lpi.display_order
FROM lab_panels lp
JOIN lab_panel_items lpi ON lp.id = lpi.panel_id
JOIN lab_items li ON lpi.lab_item_id = li.id
WHERE lp.panel_code = 'LFT' AND lp.is_active = 1 AND li.is_active = 1
ORDER BY lpi.display_order;

-- คำนวณราคารวมของ Panel จาก Items
SELECT 
  lp.panel_code,
  lp.price AS panel_price,
  SUM(li.price) AS items_total_price,
  SUM(li.cost) AS items_total_cost
FROM lab_panels lp
JOIN lab_panel_items lpi ON lp.id = lpi.panel_id
JOIN lab_items li ON lpi.lab_item_id = li.id
WHERE lp.panel_code = 'LFT'
GROUP BY lp.id;

-- หา Panels ที่มี Out Lab items
SELECT DISTINCT
  lp.panel_code,
  lp.panel_name_th,
  COUNT(CASE WHEN li.is_out_lab = 1 THEN 1 END) AS out_lab_count,
  COUNT(*) AS total_items
FROM lab_panels lp
JOIN lab_panel_items lpi ON lp.id = lpi.panel_id
JOIN lab_items li ON lpi.lab_item_id = li.id
WHERE lp.is_active = 1
GROUP BY lp.id
HAVING out_lab_count > 0;

---

5. lab_items → lab_normal_ranges (One-to-Many)

Type: Optional (lab_items อาจไม่มีค่าปกติ)

-- Foreign Key
ALTER TABLE lab_normal_ranges
  ADD CONSTRAINT fk_normal_range_lab_item 
  FOREIGN KEY (lab_item_id) 
  REFERENCES lab_items(id) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE;

Description: - 1 Lab Item มีได้หลาย Normal Ranges (แยกตามอายุ/เพศ) - 1 Normal Range ผูกกับได้ 1 Lab Item เท่านั้น - ลบ Lab Item → ลบ Normal Ranges ทั้งหมด (CASCADE)

Business Logic:

-- หาค่าปกติที่เหมาะสมสำหรับผู้ป่วย (อายุ 35 ปี, เพศชาย)
-- อายุ 35 ปี = 35 * 365 = 12,775 วัน
SELECT nr.*
FROM lab_normal_ranges nr
WHERE nr.lab_item_id = 123
  AND (nr.age_min_days IS NULL OR nr.age_min_days <= 12775)
  AND (nr.age_max_days IS NULL OR nr.age_max_days >= 12775)
  AND (nr.gender = 'M' OR nr.gender = 'ALL')
ORDER BY 
  CASE WHEN nr.gender = 'M' THEN 1 ELSE 2 END, -- ลำดับ: M > ALL
  nr.age_min_days DESC -- เลือก range ที่แคบที่สุด
LIMIT 1;

-- ดึงค่าปกติทั้งหมดของ Lab Item 'FBS'
SELECT 
  li.lab_code,
  li.lab_name_th,
  CASE 
    WHEN nr.age_min_days IS NULL AND nr.age_max_days IS NULL THEN 'ทุกอายุ'
    WHEN nr.age_min_days IS NULL THEN CONCAT('≤ ', FLOOR(nr.age_max_days/365), ' ปี')
    WHEN nr.age_max_days IS NULL THEN CONCAT('≥ ', FLOOR(nr.age_min_days/365), ' ปี')
    ELSE CONCAT(FLOOR(nr.age_min_days/365), '-', FLOOR(nr.age_max_days/365), ' ปี')
  END AS age_range,
  CASE nr.gender
    WHEN 'M' THEN 'ชาย'
    WHEN 'F' THEN 'หญิง'
    ELSE 'ทั้งหมด'
  END AS gender,
  CASE nr.range_type
    WHEN 'NUMERIC' THEN CONCAT(nr.min_value, ' - ', nr.max_value, ' ', li.unit)
    WHEN 'TEXT' THEN nr.text_value
    WHEN 'POSSIBLE_VALUES' THEN nr.possible_values
  END AS normal_range
FROM lab_items li
LEFT JOIN lab_normal_ranges nr ON li.id = nr.lab_item_id
WHERE li.lab_code = 'FBS'
ORDER BY nr.age_min_days, nr.gender;

---

6. lab_items → lab_critical_values (One-to-One)

Type: Optional (lab_items อาจไม่มีค่าวิกฤต)

-- Foreign Key with UNIQUE constraint
ALTER TABLE lab_critical_values
  ADD CONSTRAINT fk_critical_value_lab_item 
  FOREIGN KEY (lab_item_id) 
  REFERENCES lab_items(id) 
  ON DELETE CASCADE 
  ON UPDATE CASCADE,
  ADD UNIQUE KEY uk_lab_item (lab_item_id);

Description: - 1 Lab Item มีได้ 1 ชุด Critical Values เท่านั้น - 1 Critical Values ผูกกับได้ 1 Lab Item เท่านั้น - ลบ Lab Item → ลบ Critical Values (CASCADE) - UNIQUE constraint ป้องกันมี critical values ซ้ำ

Business Logic:

-- ตรวจสอบค่า Lab ว่าเป็นค่าวิกฤตหรือไม่
SELECT 
  li.lab_code,
  li.lab_name_th,
  result.value AS result_value,
  CASE
    WHEN result.value IS NULL THEN 'N/A'
    WHEN result.value <= cv.panic_low THEN 'PANIC LOW'
    WHEN result.value >= cv.panic_high THEN 'PANIC HIGH'
    WHEN result.value < cv.critical_low THEN 'CRITICAL LOW'
    WHEN result.value > cv.critical_high THEN 'CRITICAL HIGH'
    ELSE 'NORMAL'
  END AS alert_level,
  cv.alert_message,
  cv.notify_immediately
FROM lab_items li
LEFT JOIN lab_critical_values cv ON li.id = cv.lab_item_id
CROSS JOIN (SELECT 2.0 AS value) result -- ตัวอย่างผลตรวจ Potassium = 2.0
WHERE li.lab_code = 'K';

-- หารายการตรวจที่มีค่าวิกฤต
SELECT 
  li.lab_code,
  li.lab_name_th,
  CONCAT(cv.critical_low, ' - ', cv.critical_high) AS critical_range,
  CONCAT(cv.panic_low, ' - ', cv.panic_high) AS panic_range
FROM lab_items li
JOIN lab_critical_values cv ON li.id = cv.lab_item_id
WHERE li.is_active = 1
ORDER BY li.lab_code;

---

📋 Cascading Rules Summary

Relationship	Parent	Child	ON DELETE	ON UPDATE
Category → Lab Item	lab_categories	lab_items	SET NULL	CASCADE
Specimen → Lab Item	specimen_types	lab_items	SET NULL	CASCADE
Container → Lab Item	container_types	lab_items	SET NULL	CASCADE
Panel → Panel Items	lab_panels	lab_panel_items	CASCADE	CASCADE
Lab Item → Panel Items	lab_items	lab_panel_items	CASCADE	CASCADE
Lab Item → Normal Ranges	lab_items	lab_normal_ranges	CASCADE	CASCADE
Lab Item → Critical Values	lab_items	lab_critical_values	CASCADE	CASCADE

Cascading Behavior Explained

SET NULL (สำหรับ Master Data References): - ลบ Category/Specimen/Container → Lab Items ยังคงอยู่ แต่ reference = NULL - เหมาะสำหรับ optional references ที่ไม่ใช่ข้อมูลหลัก

CASCADE (สำหรับ Dependent Data): - ลบ Lab Item → ลบ Normal Ranges และ Critical Values ทั้งหมด - ลบ Panel → ลบรายการตรวจใน Panel (junction table) - เหมาะสำหรับข้อมูลที่ไม่มีความหมายเมื่อ parent ถูกลบ

---

🔍 Common Query Patterns

1. ดึงข้อมูล Lab Item แบบเต็ม (Full Detail)

SELECT 
  li.id,
  li.lab_code,
  li.lab_name_th,
  li.lab_name_en,
  -- Category
  lc.code AS category_code,
  lc.name_th AS category_name,
  lc.color AS category_color,
  -- Specimen
  st.code AS specimen_code,
  st.name_th AS specimen_name,
  -- Container
  ct.code AS container_code,
  ct.name_th AS container_name,
  ct.color AS container_color,
  -- Item Details
  li.specimen_volume,
  li.unit,
  li.method,
  li.turn_around_time,
  li.price,
  li.cost,
  -- Out Lab
  li.is_out_lab,
  li.out_lab_name,
  li.out_lab_cost,
  -- Normal Range Count
  COUNT(DISTINCT nr.id) AS normal_range_count,
  -- Critical Value
  CASE WHEN cv.id IS NOT NULL THEN 1 ELSE 0 END AS has_critical_value
FROM lab_items li
LEFT JOIN lab_categories lc ON li.category_id = lc.id
LEFT JOIN specimen_types st ON li.specimen_type_id = st.id
LEFT JOIN container_types ct ON li.container_type_id = ct.id
LEFT JOIN lab_normal_ranges nr ON li.id = nr.lab_item_id
LEFT JOIN lab_critical_values cv ON li.id = cv.lab_item_id
WHERE li.is_active = 1
GROUP BY li.id
ORDER BY li.lab_code;

2. ดึงข้อมูล Panel พร้อม Items

SELECT 
  lp.panel_code,
  lp.panel_name_th,
  lp.price AS panel_price,
  -- Items in Panel
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'lab_code', li.lab_code,
      'lab_name_th', li.lab_name_th,
      'is_out_lab', li.is_out_lab,
      'out_lab_name', li.out_lab_name,
      'price', li.price,
      'cost', li.cost,
      'display_order', lpi.display_order
    ) ORDER BY lpi.display_order
  ) AS items,
  -- Summary
  COUNT(*) AS total_items,
  SUM(CASE WHEN li.is_out_lab = 1 THEN 1 ELSE 0 END) AS out_lab_items,
  SUM(li.price) AS items_total_price,
  SUM(li.cost) AS items_total_cost
FROM lab_panels lp
JOIN lab_panel_items lpi ON lp.id = lpi.panel_id
JOIN lab_items li ON lpi.lab_item_id = li.id
WHERE lp.is_active = 1 AND li.is_active = 1
GROUP BY lp.id
ORDER BY lp.panel_code;

3. หา Lab Items ที่ไม่ได้อยู่ใน Panel ใดเลย

SELECT 
  li.lab_code,
  li.lab_name_th,
  li.price
FROM lab_items li
LEFT JOIN lab_panel_items lpi ON li.id = lpi.lab_item_id
WHERE lpi.id IS NULL 
  AND li.is_active = 1
ORDER BY li.lab_code;

4. หา Panels ที่มีทั้ง In-house และ Out Lab

SELECT 
  lp.panel_code,
  lp.panel_name_th,
  COUNT(CASE WHEN li.is_out_lab = 0 THEN 1 END) AS in_house_count,
  COUNT(CASE WHEN li.is_out_lab = 1 THEN 1 END) AS out_lab_count
FROM lab_panels lp
JOIN lab_panel_items lpi ON lp.id = lpi.panel_id
JOIN lab_items li ON lpi.lab_item_id = li.id
WHERE lp.is_active = 1
GROUP BY lp.id
HAVING in_house_count > 0 AND out_lab_count > 0
ORDER BY lp.panel_code;

5. Validate Data Integrity

-- หา Lab Items ที่ไม่มี Normal Range
SELECT li.lab_code, li.lab_name_th
FROM lab_items li
LEFT JOIN lab_normal_ranges nr ON li.id = nr.lab_item_id
WHERE li.is_active = 1 AND nr.id IS NULL;

-- หา Lab Items ที่มีราคาต่ำกว่าต้นทุน (ขายขาดทุน)
SELECT lab_code, lab_name_th, price, cost
FROM lab_items
WHERE price < cost AND is_active = 1;

-- หา Panels ที่ราคาสูงกว่าผลรวม items (ไม่คุ้มค่า)
SELECT 
  lp.panel_code,
  lp.price AS panel_price,
  SUM(li.price) AS items_total
FROM lab_panels lp
JOIN lab_panel_items lpi ON lp.id = lpi.panel_id
JOIN lab_items li ON lpi.lab_item_id = li.id
GROUP BY lp.id
HAVING panel_price > items_total;

---

🎯 Indexing Strategy

Indexes สำหรับ Performance

-- lab_items (Most Queried Table)
CREATE INDEX idx_lab_items_category ON lab_items(category_id) WHERE is_active = 1;
CREATE INDEX idx_lab_items_specimen ON lab_items(specimen_type_id) WHERE is_active = 1;
CREATE INDEX idx_lab_items_container ON lab_items(container_type_id) WHERE is_active = 1;
CREATE INDEX idx_lab_items_out_lab ON lab_items(is_out_lab, is_active);
CREATE FULLTEXT INDEX ft_lab_items_search ON lab_items(lab_name_th, lab_name_en, lab_code);

-- lab_panel_items (Junction Table)
CREATE INDEX idx_panel_items_composite ON lab_panel_items(panel_id, display_order);
CREATE INDEX idx_panel_items_lab ON lab_panel_items(lab_item_id);

-- lab_normal_ranges (Age/Gender Queries)
CREATE INDEX idx_normal_range_age_gender ON lab_normal_ranges(lab_item_id, age_min_days, age_max_days, gender);

-- lab_critical_values (Alert Queries)
CREATE INDEX idx_critical_notify ON lab_critical_values(notify_immediately) WHERE notify_immediately = 1;

---

Next: ดู DATA_DICTIONARY.md สำหรับรายละเอียดแต่ละ Column