ERD-RKhospital

Tables (18)

appointment

  • appointment_id — bigint — Primary key — NOT NULL
  • created_at — datetime(6)
  • patient_num — varchar(30) — NOT NULL
  • reserve_datetime — datetime(6) — NOT NULL
  • status — enum('CANCELLED','CONFIRMED','DONE','REQUESTED') — NOT NULL
  • symptom — varchar(255)
  • department_id — bigint — Foreign key → department.department_id
  • doctor_id — bigint — Foreign key → staff.staff_id
  • cancel_reason — varchar(500)
  • cancelled_at — datetime(6)
  • cancelled_by_user_id — bigint — Foreign key → patient.patient_id

audit_log

  • log_id — bigint — Primary key — NOT NULL
  • action — varchar(30) — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • detail — text
  • entity_id — varchar(100)
  • entity_type — varchar(100) — NOT NULL
  • actor_user_id — bigint — Foreign key → patient.patient_id — NOT NULL

compliment_comment

  • comment_id — bigint — Primary key — NOT NULL
  • content — tinytext — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • author_id — bigint — Foreign key → patient.patient_id — NOT NULL
  • post_id — bigint — Foreign key → compliment_post.thx_id — NOT NULL

compliment_post

  • thx_id — bigint — Primary key — NOT NULL
  • patient_id — bigint — Foreign key → patient.patient_id — NOT NULL
  • name — varchar(100) — NOT NULL
  • thx_title — varchar(200) — NOT NULL
  • thx_content — text — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • updated_at — datetime(6) — NOT NULL
  • view_count — bigint — NOT NULL
  • target_name — varchar(100) — NOT NULL
  • target_type — enum('DOCTOR','NURSE') — NOT NULL

department

  • department_id — bigint — Primary key — NOT NULL — 진료과 ID
  • department_name — varchar(255) — NOT NULL
  • description — varchar(255)

diagnosis_record

  • diagnose_id — bigint — Primary key — NOT NULL
  • assessment — text
  • created_at — datetime(6) — NOT NULL
  • detail — text
  • diagnose — varchar(255)
  • objective — text
  • patient_num — varchar(30) — NOT NULL
  • plan — text
  • subjective — text
  • total_cost — decimal(12,2)
  • appointment_id — bigint — Foreign key → appointment.appointment_id — NOT NULL
  • department_id — bigint — Foreign key → department.department_id — NOT NULL
  • doctor_id — bigint — Foreign key → staff.staff_id — NOT NULL

drug

  • drug_id — bigint — Primary key — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • description — varchar(255)
  • drug_name — varchar(200) — NOT NULL
  • drug_type — enum('CAPSULE','CREAM','INJECTION','OTHER','SYRUP','TABLET') — NOT NULL
  • manufacturer — varchar(255)
  • unit — varchar(255)
  • unit_price — int — NOT NULL

health_info_post

  • post_id — bigint — Primary key — NOT NULL
  • author_name — varchar(100) — NOT NULL
  • content — tinytext — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • summary — varchar(1000) — NOT NULL
  • thumbnail_file_id — bigint
  • title — varchar(200) — NOT NULL
  • updated_at — datetime(6) — NOT NULL
  • author_id — bigint — Foreign key → patient.patient_id — NOT NULL

media_file

  • file_id — bigint — Primary key — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • file_type — enum('DOC','DOCX','HWP','IMAGE','OTHER','PDF','PPT','PPTX','TXT','VIDEO','XLS','XLSX') — NOT NULL
  • original_name — varchar(255) — NOT NULL
  • owner_id — bigint — NOT NULL
  • owner_type — enum('HEALTHPOST','NOTICE','THX') — NOT NULL
  • size — bigint — NOT NULL
  • updated_at — datetime(6) — NOT NULL
  • url — varchar(500) — NOT NULL

notice_post

  • post_id — bigint — Primary key — NOT NULL
  • author_name — varchar(100) — NOT NULL
  • content — text — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • title — varchar(200) — NOT NULL
  • updated_at — datetime(6) — NOT NULL
  • author_id — bigint — Foreign key → patient.patient_id — NOT NULL
  • is_main_modal — tinyint(1) — NOT NULL

patient

  • patient_id — bigint — Primary key — NOT NULL
  • patient_num — varchar(30) — NOT NULL
  • name — varchar(100) — NOT NULL
  • birth — date — NOT NULL
  • gender — enum('F','M','O') — NOT NULL
  • phone — varchar(20)
  • email — varchar(255)
  • address — varchar(255)
  • username — varchar(50) — NOT NULL
  • password_hash — varchar(255) — NOT NULL
  • refresh_token_hash — varchar(64)
  • refresh_token_expires_at — datetime
  • ssn_enc — tinyblob
  • created_at — timestamp — NOT NULL
  • updated_at — timestamp — NOT NULL
  • role — varchar(20) — NOT NULL

prescription

  • prescription_id — bigint — Primary key — NOT NULL
  • issued_at — datetime(6) — NOT NULL
  • notes — text
  • diagnose_id — bigint — Foreign key → diagnosis_record.diagnose_id — NOT NULL
  • doctor_id — bigint — Foreign key → staff.staff_id — NOT NULL

prescription_item

  • item_id — bigint — Primary key — NOT NULL
  • days — int
  • dosage — varchar(100)
  • duration — varchar(255)
  • frequency — varchar(100)
  • notes — text
  • quantity — int
  • drug_id — bigint — Foreign key → drug.drug_id — NOT NULL
  • prescription_id — bigint — Foreign key → prescription.prescription_id — NOT NULL
  • unit_price — int — NOT NULL

reception

  • reception_id — bigint — Primary key — NOT NULL
  • checked_in_at — datetime(6) — NOT NULL
  • queue_no — int — NOT NULL
  • status — enum('CANCELLED','DONE','IN_PROGRESS','WAITING') — NOT NULL
  • appointment_id — bigint — Foreign key → appointment.appointment_id — NOT NULL
  • department_id — bigint — Foreign key → department.department_id — NOT NULL
  • cancel_reason — varchar(500)
  • cancelled_at — datetime(6)

reservation

  • reservation_id — bigint — Primary key — NOT NULL
  • created_at — datetime(6)
  • date — date — NOT NULL
  • department_id — bigint — NOT NULL
  • doctor_name — varchar(255) — NOT NULL
  • patient_id — bigint — NOT NULL
  • status — varchar(20) — NOT NULL
  • symptom — varchar(500)
  • time_slot — varchar(10) — NOT NULL

revoked_token

  • revoked_token_id — bigint — Primary key — NOT NULL
  • created_at — datetime(6) — NOT NULL
  • expires_at — datetime(6) — NOT NULL
  • token_hash — varchar(64) — NOT NULL

staff

  • staff_id — bigint — Primary key — NOT NULL — 의료진 고유 ID
  • department_id — bigint — Foreign key → department.department_id — NOT NULL — 소속 진료과 ID
  • name — varchar(255) — NOT NULL
  • role — enum('DOCTOR', 'NURSE', 'ADMIN', 'STAFF') — NOT NULL — 역할
  • phone — varchar(255)
  • email — varchar(255)
  • active — tinyint(1) — NOT NULL — 재직 여부
  • created_at — datetime — NOT NULL — 생성일

staff_schedule

  • schedule_id — bigint — Primary key — NOT NULL — 시간표 고유 ID
  • staff_id — bigint — Foreign key → staff.staff_id — NOT NULL — 의료진 ID
  • department_id — bigint — Foreign key → department.department_id — NOT NULL — 진료과 ID
  • day_of_week — int — NOT NULL
  • start_time — time — NOT NULL — 진료 시작 시간
  • end_time — time — NOT NULL — 진료 종료 시간
  • valid_from — date — 적용 시작일
  • valid_to — date — 적용 종료일
  • created_at — datetime — NOT NULL — 생성일