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 — 생성일