221 lines
20 KiB
Markdown
221 lines
20 KiB
Markdown
# 03. Database Design Architecture & Data Dictionary
|
|
**Project:** Thai Traditional Massage Queue Management System (TTMQMS Enterprise)
|
|
**Database Engine:** MySQL 8.0 / Percona Server (InnoDB Engine, utf8mb4_unicode_ci)
|
|
**Normalization:** 3rd Normal Form (3NF) Compliant
|
|
**Version:** 1.0.0 Production
|
|
|
|
---
|
|
|
|
## 1. Database Architecture & Design Principles
|
|
ฐานข้อมูลของระบบ TTMQMS ถูกออกแบบด้วยสถาปัตยกรรมเชิงสัมพันธ์ (Relational Database Architecture) ภายใต้มาตรฐานสากล โดยมีคุณสมบัติดังนี้:
|
|
1. **ACID Compliance & Transactional Integrity:** ทุกตารางใช้เครื่องยนต์ **InnoDB** รองรับ Foreign Key Constraints และ Atomic Transactions
|
|
2. **Unicode utf8mb4:** รองรับภาษาไทย ภาษาอังกฤษ ลายมือชื่อดิจิทัล (Base64/BLOB) และอิโมจิทางการแพทย์
|
|
3. **Optimized Indexing:** สร้าง Index บนคอลัมน์ที่สืบค้นบ่อย (เช่น `national_id`, `hn`, `status`, `created_at`, `branch_id`) เพื่อลดเวลา Execution Time ให้ต่ำกว่า 10ms
|
|
4. **Automated AI Allocation & Audit Logging:** ฝัง Logic การคำนวณคิวด้วย **Stored Procedure** (`sp_assign_smart_queue`) และมี **Triggers** เพื่ออัปเดตสถานะห้องและซิงค์ HIS อัตโนมัติ
|
|
|
|
---
|
|
|
|
## 2. Entity-Relationship Diagram (ERD Level 3)
|
|
แผนภาพความสัมพันธ์ระหว่างเอนทิตีทั้ง 13 ตารางหลักในระบบ TTMQMS
|
|
|
|
```mermaid
|
|
erDiagram
|
|
BRANCHES ||--o{ USERS : "has employees"
|
|
BRANCHES ||--o{ PATIENTS : "registers"
|
|
BRANCHES ||--o{ THERAPISTS : "employs"
|
|
BRANCHES ||--o{ ROOMS : "contains"
|
|
BRANCHES ||--o{ QUEUES : "manages"
|
|
|
|
USERS ||--o{ THERAPISTS : "links to profile"
|
|
USERS ||--o{ AUDIT_LOGS : "generates"
|
|
USERS ||--o{ API_TOKENS : "authenticates"
|
|
|
|
PATIENTS ||--o{ QUEUES : "requests"
|
|
PATIENTS ||--o{ SOAP_NOTES : "has clinical history"
|
|
PATIENTS ||--o{ BILLINGS : "pays bills"
|
|
|
|
THERAPISTS ||--o{ QUEUES : "assigned to"
|
|
THERAPISTS ||--o{ ROOMS : "stationed at"
|
|
THERAPISTS ||--o{ SOAP_NOTES : "records & signs"
|
|
THERAPISTS ||--o{ QUEUE_ALLOCATIONS : "evaluated in"
|
|
|
|
SERVICES ||--o{ QUEUES : "specified in"
|
|
ROOMS ||--o{ QUEUES : "hosts"
|
|
|
|
QUEUES ||--o| SOAP_NOTES : "results in"
|
|
QUEUES ||--o| BILLINGS : "generates invoice"
|
|
QUEUES ||--o{ QUEUE_ALLOCATIONS : "logged by AI"
|
|
```
|
|
|
|
---
|
|
|
|
## 3. Comprehensive Data Dictionary (พจนานุกรมข้อมูล 13 ตาราง)
|
|
|
|
### 3.1 ตาราง `branches` (สาขาของศูนย์การแพทย์)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสอ้างอิงสาขาหลัก |
|
|
| **name_th** | VARCHAR(150) | NOT NULL | ชื่อสาขาภาษาไทย (เช่น สาขาทองหล่อ) |
|
|
| **name_en** | VARCHAR(150) | NULL | ชื่อสาขาภาษาอังกฤษ |
|
|
| **address** | TEXT | NULL | ที่อยู่ตั้งสาขา |
|
|
| **phone** | VARCHAR(50) | NULL | เบอร์โทรศัพท์ติดต่อ |
|
|
| **is_active** | TINYINT(1) | DEFAULT 1 | สถานะเปิดใช้งาน (1=Active, 0=Inactive) |
|
|
|
|
### 3.2 ตาราง `users` (ผู้ใช้งานระบบ / เจ้าหน้าที่)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสอ้างอิงผู้ใช้งานระบบ |
|
|
| **branch_id** | BIGINT UNSIGNED | FK -> branches(id) | สังกัดสาขา |
|
|
| **national_id** | VARCHAR(13) | UNIQUE, NOT NULL | เลขประจำตัวประชาชน 13 หลัก (Username) |
|
|
| **password_hash** | VARCHAR(255) | NOT NULL | รหัสผ่านที่เข้ารหัสด้วย Argon2id |
|
|
| **full_name** | VARCHAR(150) | NOT NULL | ชื่อและนามสกุลเจ้าหน้าที่ |
|
|
| **role** | ENUM(...) | NOT NULL | บทบาท: Admin, Reception, Therapist, Cashier |
|
|
| **totp_secret** | VARCHAR(64) | NULL | รหัสลับสำหรับตรวจสอบ 2FA Google Authenticator |
|
|
| **is_2fa_enabled**| TINYINT(1) | DEFAULT 1 | สถานะบังคับใช้ 2FA (1=Enabled) |
|
|
|
|
### 3.3 ตาราง `patients` (ผู้ป่วยและผู้รับบริการ)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสอ้างอิงผู้ป่วยในระบบ |
|
|
| **branch_id** | BIGINT UNSIGNED | FK -> branches(id) | สาขาที่ลงทะเบียนครั้งแรก |
|
|
| **hn** | VARCHAR(50) | UNIQUE, NOT NULL | Hospital Number (เชื่อมโยงระบบ HIS) |
|
|
| **national_id** | VARCHAR(13) | UNIQUE, NOT NULL | เลขประจำตัวประชาชน 13 หลัก (จาก Smart Card) |
|
|
| **first_name_th** | VARCHAR(100) | NOT NULL | ชื่อจริงภาษาไทย |
|
|
| **last_name_th** | VARCHAR(100) | NOT NULL | นามสกุลภาษาไทย |
|
|
| **gender** | ENUM('Male','Female','Other') | NOT NULL | เพศสภาพ |
|
|
| **birth_date** | DATE | NULL | วันเดือนปีเกิด (คำนวณอายุอัตโนมัติ) |
|
|
| **phone** | VARCHAR(20) | NULL | เบอร์โทรศัพท์เคลื่อนที่ |
|
|
| **right_type** | VARCHAR(100) | DEFAULT 'UC' | สิทธิการรักษา (บัตรทอง, ข้าราชการ, ประกันสังคม, จ่ายเอง) |
|
|
| **contraindications**| TEXT | NULL | โรคประจำตัว / ข้อห้ามในการนวด (เช่น ความดันสูง, ตั้งครรภ์) |
|
|
|
|
### 3.4 ตาราง `therapists` (หมอนวดและแพทย์แผนไทย)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสอ้างอิงหมอนวด |
|
|
| **user_id** | BIGINT UNSIGNED | FK -> users(id), UNIQUE | รหัสบัญชีผู้เข้าใช้งานระบบ |
|
|
| **branch_id** | BIGINT UNSIGNED | FK -> branches(id) | ประจำสาขา |
|
|
| **license_no** | VARCHAR(50) | UNIQUE, NULL | เลขที่ใบประกอบวิชาชีพเวชกรรมไทย |
|
|
| **specialty** | VARCHAR(150) | NULL | ความเชี่ยวชาญพิเศษ (เช่น นวดราชสำนัก, แก้อาการ) |
|
|
| **status** | ENUM(...) | DEFAULT 'Available' | สถานะปัจจุบัน: Available, Busy, Off_Duty |
|
|
| **max_daily_minutes**| INT UNSIGNED | DEFAULT 480 | ชั่งโมงทำงานสูงสุดต่อวัน (นาที) เพื่อกันเหนื่อยล้า |
|
|
|
|
### 3.5 ตาราง `services` (บริการนวดและคอร์สการรักษา)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสบริการ |
|
|
| **code** | VARCHAR(50) | UNIQUE, NOT NULL | รหัสรหัสหัตถการ (ICD-10 TM Code Mapping) |
|
|
| **name_th** | VARCHAR(150) | NOT NULL | ชื่อบริการ (เช่น นวดแผนไทยราชสำนัก) |
|
|
| **duration_mins**| INT UNSIGNED | NOT NULL | ระยะเวลาให้บริการมาตรฐาน (นาที) |
|
|
| **price** | DECIMAL(10,2) | NOT NULL | อัตราค่าบริการ (บาท) |
|
|
| **required_room_type**| VARCHAR(50)| DEFAULT 'Standard' | ประเภทห้องที่ต้องการ: Standard, VIP Private, Foot Spa |
|
|
|
|
### 3.6 ตาราง `rooms` (ห้องนวดและเตียงบริการ)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสห้องนวด |
|
|
| **branch_id** | BIGINT UNSIGNED | FK -> branches(id) | สาขาที่ตั้ง |
|
|
| **room_no** | VARCHAR(50) | NOT NULL | หมายเลขห้อง / เตียง (เช่น R-101, Bed-05) |
|
|
| **name_th** | VARCHAR(100) | NOT NULL | ชื่อเรียกห้องนวด |
|
|
| **room_type** | VARCHAR(50) | NOT NULL | ประเภทห้อง: Standard Bed, VIP Private, Foot Spa, Aroma |
|
|
| **status** | ENUM(...) | DEFAULT 'Available' | สถานะ: Available, In_Use, Cleaning, Maintenance |
|
|
| **current_therapist_id**| BIGINT UNSIGNED| FK -> therapists(id), NULL| หมอนวดที่กำลังปฏิบัติงานในห้องนี้ |
|
|
|
|
### 3.7 ตาราง `queues` (การจัดสรรคิวบริการ)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสอ้างอิงรายการคิว |
|
|
| **branch_id** | BIGINT UNSIGNED | FK -> branches(id) | สาขาที่ออกคิว |
|
|
| **queue_no** | VARCHAR(20) | NOT NULL | หมายเลขบัตรคิว (เช่น A001, V001, E001) |
|
|
| **patient_id** | BIGINT UNSIGNED | FK -> patients(id) | ผู้รับบริการ |
|
|
| **service_id** | BIGINT UNSIGNED | FK -> services(id) | บริการที่เลือก |
|
|
| **assigned_therapist_id**| BIGINT UNSIGNED| FK -> therapists(id), NULL| หมอนวดที่ถูกจัดสรรโดย AI |
|
|
| **assigned_room_id**| BIGINT UNSIGNED| FK -> rooms(id), NULL | ห้องนวดที่ถูกจัดสรร |
|
|
| **priority** | ENUM(...) | DEFAULT 'Normal' | ระดับความสำคัญ: Normal, VIP, Emergency |
|
|
| **status** | ENUM(...) | DEFAULT 'Waiting' | สถานะ: Waiting, Assigned, In_Progress, Completed, Cancelled |
|
|
| **est_start_time**| DATETIME | NULL | เวลาเริ่มรับบริการโดยประมาณ (คำนวณโดย AI) |
|
|
| **actual_start_time**| DATETIME | NULL | เวลาเริ่มเข้าห้องนวดจริง |
|
|
| **completed_time**| DATETIME | NULL | เวลาสิ้นสุดการนวด |
|
|
|
|
### 3.8 ตาราง `queue_allocations` (ประวัติการคำนวณด้วย Smart Queue AI)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสบันทึกการจัดสรร |
|
|
| **queue_id** | BIGINT UNSIGNED | FK -> queues(id) | อ้างอิงรายการคิว |
|
|
| **therapist_id** | BIGINT UNSIGNED | FK -> therapists(id) | หมอนวดที่ถูกพิจารณา |
|
|
| **room_id** | BIGINT UNSIGNED | FK -> rooms(id) | ห้องพักที่จับคู่ |
|
|
| **workload_score**| DECIMAL(8,2) | NOT NULL | คะแนนความเหนื่อยล้าสะสมของหมอนวด (ยิ่งต่ำยิ่งได้สิทธิ์ก่อน) |
|
|
| **allocation_reason**| VARCHAR(255) | NOT NULL | คำอธิบายจาก AI (เช่น "จัดสรรให้หมอสมศรีเนื่องจากชั่วโมงว่างสูงสุด")|
|
|
| **created_at** | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP| เวลาที่ AI คำนวณเสร็จสิ้น |
|
|
|
|
### 3.9 ตาราง `soap_notes` (เวชระเบียนและการตรวจประเมินทางคลินิก)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสบันทึก SOAP Note |
|
|
| **queue_id** | BIGINT UNSIGNED | FK -> queues(id), UNIQUE| อ้างอิงการรับบริการในครั้งนั้น |
|
|
| **patient_id** | BIGINT UNSIGNED | FK -> patients(id) | อ้างอิงผู้ป่วย |
|
|
| **therapist_id** | BIGINT UNSIGNED | FK -> therapists(id) | แพทย์แผนไทย/หมอนวดผู้ประเมิน |
|
|
| **subjective** | TEXT | NOT NULL | S: อาการนำและประวัติการเจ็บป่วย (Chief Complaint) |
|
|
| **objective_vas_pre**| INT UNSIGNED| NOT NULL | O: ระดับความปวดก่อนรักษา VAS Score (0-10) |
|
|
| **objective_vas_post**| INT UNSIGNED| NOT NULL | O: ระดับความปวดหลังรักษา VAS Score (0-10) |
|
|
| **objective_rom** | JSON | NULL | O: องศาการเคลื่อนไหว (Flexion, Extension Goniometer) |
|
|
| **assessment** | TEXT | NOT NULL | A: การวินิจฉัยโรคตามคัมภีร์แพทย์แผนไทยและ ICD-10 TM |
|
|
| **plan_treatment**| TEXT | NOT NULL | P: หัตถการที่ให้และการพยาบาล (เช่น นวดราชสำนัก, ประคบ) |
|
|
| **signature_base64**| LONGTEXT | NOT NULL | ลายมือชื่อหมอนวดผู้ให้การรักษา (Base64 PNG Format) |
|
|
| **his_sync_status**| ENUM(...) | DEFAULT 'Pending' | สถานะซิงค์ HIS: Pending, Synced, Failed |
|
|
|
|
### 3.10 ตาราง `billings` (การคิดเงิน ออกใบเสร็จ และ PromptPay QR)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสบิล / ใบเสร็จรับเงิน |
|
|
| **queue_id** | BIGINT UNSIGNED | FK -> queues(id), UNIQUE| อ้างอิงคิวการให้บริการ |
|
|
| **patient_id** | BIGINT UNSIGNED | FK -> patients(id) | อ้างอิงผู้ชำระเงิน |
|
|
| **receipt_no** | VARCHAR(50) | UNIQUE, NOT NULL | เลขที่ใบเสร็จรับเงิน (เช่น INV-20260727-0001) |
|
|
| **subtotal** | DECIMAL(10,2) | NOT NULL | ยอดรวมค่าบริการก่อนลด (บาท) |
|
|
| **discount** | DECIMAL(10,2) | DEFAULT 0.00 | ยอดส่วนลด |
|
|
| **vat_amount** | DECIMAL(10,2) | NOT NULL | ภาษีมูลค่าเพิ่ม (VAT 7%) |
|
|
| **grand_total** | DECIMAL(10,2) | NOT NULL | ยอดชำระสุทธิ (บาท) |
|
|
| **payment_method**| ENUM(...) | NOT NULL | วิธีชำระเงิน: Cash, PromptPay, Credit, Insurance |
|
|
| **promptpay_qr_payload**| TEXT | NULL | ข้อความ EMVCo QR Code Payload สำหรับสแกนจ่าย |
|
|
| **status** | ENUM(...) | DEFAULT 'Paid' | สถานะใบเสร็จ: Paid, Refunded, Cancelled |
|
|
|
|
### 3.11 ตาราง `audit_logs` (บันทึกร่องรอยตรวจสอบความปลอดภัย OWASP)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัส Log |
|
|
| **user_id** | BIGINT UNSIGNED | FK -> users(id), NULL | เจ้าหน้าที่ผู้กระทำรายการ (NULL หากเป็นระบบอัตโนมัติ) |
|
|
| **action** | VARCHAR(100) | NOT NULL | ชื่อเหตุการณ์ (เช่น LOGIN_SUCCESS, WALK_IN_CREATE, SOAP_SIGN) |
|
|
| **entity_type** | VARCHAR(50) | NOT NULL | ชื่อตาราง/อ็อบเจกต์ที่เกี่ยวข้อง (เช่น Patient, Queue, Billing) |
|
|
| **entity_id** | BIGINT UNSIGNED | NULL | รหัส PK ของอ็อบเจกต์นั้นๆ |
|
|
| **ip_address** | VARCHAR(45) | NOT NULL | IP Address ของ Client (รองรับ IPv4 และ IPv6) |
|
|
| **user_agent** | TEXT | NULL | ข้อมูลเบราว์เซอร์และอุปกรณ์ PWA |
|
|
| **details** | JSON | NULL | ข้อมูลการเปลี่ยนแปลง (Before/After State JSON) |
|
|
| **created_at** | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP| วันเวลาที่เกิดเหตุการณ์ (UTC+7) |
|
|
|
|
### 3.12 ตาราง `sys_config` (การตั้งค่าพารามิเตอร์ระบบและ AI SLA)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **key_name** | VARCHAR(100) | PK | ชื่อตัวแปรตั้งค่า (เช่น `sla_target_wait_mins`, `jwt_ttl_minutes`) |
|
|
| **value_str** | TEXT | NOT NULL | ค่าพารามิเตอร์ที่กำหนด |
|
|
| **description** | VARCHAR(255) | NULL | คำอธิบายการตั้งค่า |
|
|
| **updated_at** | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP| วันเวลาที่แก้ไขล่าสุด |
|
|
|
|
### 3.13 ตาราง `api_tokens` (การจัดการ Access Token และ Revocation List)
|
|
| Column Name | Data Type | Constraints | Description |
|
|
| :--- | :--- | :--- | :--- |
|
|
| **id** | BIGINT UNSIGNED | PK, AUTO_INCREMENT | รหัสโทเค็น |
|
|
| **user_id** | BIGINT UNSIGNED | FK -> users(id) | อ้างอิงเจ้าของโทเค็น |
|
|
| **token_hash** | VARCHAR(64) | UNIQUE, NOT NULL | ค่า SHA-256 Hash ของ JWT Access/Refresh Token |
|
|
| **expires_at** | DATETIME | NOT NULL | วันเวลาหมดอายุของโทเค็น |
|
|
| **is_revoked** | TINYINT(1) | DEFAULT 0 | สถานะยกเลิกโทเค็น (0=Valid, 1=Revoked / Logged out) |
|
|
|
|
---
|
|
|
|
## 4. Database Views & Stored Procedures Architecture
|
|
ระบบมีการสร้าง Objects บนฐานข้อมูลเพื่อรองรับการประมวลผลข้อมูลขั้นสูง:
|
|
- **View `v_daily_executive_kpi`:** วิเคราะห์สถิติคิวบริการแบบ Realtime, คำนวณเวลารอคอยเฉลี่ย (Average Wait Time in Mins), อัตราการใช้เตียง/ห้องนวด (Occupancy Rate %) และสรุปยอดรายรับรวมประจำวันของแต่ละสาขา
|
|
- **View `v_therapist_workload_balance`:** สรุปชั่วโมงปฏิบัติงานสะสมประจำวันของหมอนวดแต่ละคน เปรียบเทียบกับโควตา 480 นาที เพื่อให้ผู้บริหารและระบบ AI ตรวจสอบความสมดุลของภาระงานได้อย่างโปร่งใส
|
|
- **Stored Procedure `sp_assign_smart_queue(in_patient_id, in_service_id, in_priority, in_branch_id)`:** หัวใจสำคัญของ Smart Queue Engine ที่ทำหน้าที่ล็อกตาราง (Row-level Locking) เพื่อค้นหาหมอนวดที่มีชั่วโมงทำงานสะสมน้อยที่สุดและจับคู่เข้ากับห้องพักที่ว่างพร้อมใช้ภายในเสี้ยววินาที
|
|
|
|
---
|
|
*เอกสารนี้ได้รับการตรวจสอบความสอดคล้องตามโครงสร้าง schema.sql และ Database Design Spec*
|