ddasum

Tables (18)

activity_record (-- 활동 기록)

  • Key (activity_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (staff_id) — BIGINT NOT NULL — -- 간호사, 요양사
  • Field (activity_type) — VARCHAR(50) — -- 재활운동, 음악치료, 산책 등
  • Field (duration_min) — INT
  • Field (participation) — TINYINT — -- 1~5
  • Field (photo_url) — VARCHAR(500)
  • Field (note) — TEXT
  • Field (activity_at) — DATETIME DEFAULT NOW()
  • FOREIGN KEY ((patient_id)) — REFERENCES patient(patient_id)
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL
  • 환자_id (patient_id2) — INT PRIMARY KEY AUTO_INCREMENT — Foreign key → patient.환자_id — NOT NULL — --1000단위 자동생성 증가

care_report (-- AI 케어 리포트)

  • Key (report_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (report_type) — ENUM('WEEKLY','MONTHLY','ALERT')
  • Field (summary) — TEXT
  • Field (detail_json) — JSON
  • Field (risk_level) — ENUM('NORMAL','CAUTION','WARNING','CRITICAL')
  • Field (sent_at) — DATETIME
  • Field (created_at) — DATETIME DEFAULT NOW()
  • FOREIGN KEY ((patient_id)) — REFERENCES patient(patient_id)
  • 환자_id (patient_id2) — INT PRIMARY KEY AUTO_INCREMENT — Foreign key → patient.환자_id — NOT NULL — --1000단위 자동생성 증가

consent_request (-- 보호자 동의 → 수납 관련 내역 등 모든 서류 관련)

  • Key (consent_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (doctor_id) — BIGINT NOT NULL
  • Field (guardian_id) — BIGINT NOT NULL
  • Field (request_type) — VARCHAR(100)
  • Field (reason) — TEXT
  • Field (evidence_json) — JSON
  • Field (status) — ENUM('PENDING','APPROVED','REJECTED') DEFAULT 'PENDING'
  • Field (requested_at) — DATETIME DEFAULT NOW()
  • Field (responded_at) — DATETIME
  • Field (signature_url) — VARCHAR(500)
  • FOREIGN KEY ((patient_id)) — REFERENCES patient(patient_id)
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL
  • 환자_id (patient_id2) — INT PRIMARY KEY AUTO_INCREMENT — Foreign key → patient.환자_id — NOT NULL — --1000단위 자동생성 증가

daily_record (-- 빠른기록 (요양사))

  • Key (record_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (recorder_id) — BIGINT NOT NULL
  • Field (recorder_role) — ENUM('CAREGIVER','NURSE','DOCTOR','SYSTEM')
  • Field (input_type) — ENUM('SELF','ASSISTED','PROXY')
  • Field (meal_breakfast) — TINYINT — -- 0:안먹음~3:완식
  • Field (meal_lunch) — TINYINT
  • Field (meal_dinner) — TINYINT
  • Field (sleep_hours) — DECIMAL(3,1)
  • Field (sleep_quality) — TINYINT — -- 1~5
  • Field (mood) — TINYINT — -- 1~5
  • Field (mobility) — TINYINT — -- 이동 능력 1~5
  • Field (activity_done) — BOOLEAN
  • Field (special_note) — TEXT
  • Field (recorded_at) — DATETIME DEFAULT NOW()
  • FOREIGN KEY ((patient_id)) — REFERENCES patient(patient_id)
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL

facility (-- 시설)

  • Key (facility_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (name) — VARCHAR(100) NOT NULL
  • Field (address) — VARCHAR(255)
  • Field (created_at) — DATETIME DEFAULT NOW()

facility_schedule (-- 시설 일정)

  • Key (schedule_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (facility_id) — BIGINT NOT NULL
  • Field (title) — VARCHAR(200)
  • Field (schedule_type) — ENUM('MEETING','EVENT','VISIT','OTHER')
  • Field (target_role) — SET('CAREGIVER','NURSE','DOCTOR','ALL')
  • Field (scheduled_date) — DATE NOT NULL
  • Field (scheduled_time) — TIME
  • Field (note) — TEXT
  • FOREIGN KEY ((facility_id)) — REFERENCES facility(facility_id) )
  • Key (facility_id2) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → facility.Key — NOT NULL

functional_score (-- 기능 상태 점수 (시계열))

  • Key (time) — TIMESTAMPTZ NOT NULL — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL,
  • Field (evaluator_id) — BIGINT
  • Field (score_type) — VARCHAR(20) — -- ADL, IADL, FALL_RISK, BRADEN
  • Field (score) — DECIMAL(5,2)
  • Field (is_ai_estimate) — BOOLEAN DEFAULT FALSE
  • Field

handover (-- 인수인계)

  • Key (handover_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (facility_id) — BIGINT NOT NULL
  • Field (from_staff_id) — BIGINT NOT NULL
  • Field (to_staff_id) — BIGINT
  • Field (shift_type) — ENUM('MORNING','AFTERNOON','NIGHT')
  • Field (content) — TEXT NOT NULL
  • Field (is_auto) — BOOLEAN DEFAULT FALSE
  • Field (is_confirmed) — BOOLEAN DEFAULT FALSE
  • Field (confirmed_at) — DATETIME
  • Field (created_at) — DATETIME DEFAULT NOW()
  • FOREIGN KEY ((facility_id)) — REFERENCES facility(facility_id)
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL

incident_record (-- 이상 징후 기록 (낙상, 욕창, 이상행동 통합))

  • Key (incident_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (recorder_id) — BIGINT NOT NULL
  • Field (incident_type) — ENUM('FALL','PRESSURE_ULCER','ABNORMAL_BEHAVIOR', 'ASPIRATION','OTHER')
  • Field (severity) — TINYINT — -- 1~5
  • Field (location) — VARCHAR(100) — -- 발생 장소
  • Field (trigger_note) — TEXT
  • Field (response_note) — TEXT
  • Field (occurred_at) — DATETIME NOT NULL
  • FOREIGN KEY ((patient_id)) — REFERENCES patient(patient_id)
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL

medication_record (-- 투약 기록)

  • Key (med_record_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (prescription_id) — BIGINT NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (nurse_id) — BIGINT
  • Field (doctor_id) — BIGINT
  • Field (scheduled_at) — DATETIME NOT NULL
  • Field (administered_at) — DATETIME
  • Field (status) — ENUM('DONE','SKIP','REFUSE') NOT NULL
  • Field (note) — TEXT
  • FOREIGN KEY ((prescription_id)) — REFERENCES prescription(prescription_id)
  • 환자_id (patient_id2) — INT PRIMARY KEY AUTO_INCREMENT — Foreign key → patient.환자_id — NOT NULL — --1000단위 자동생성 증가
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL
  • Key (prescription_id2) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → prescription.Key — NOT NULL

notification (-- 알림)

  • Key (noti_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (receiver_id) — BIGINT NOT NULL
  • Field (patient_id) — BIGINT
  • Field (noti_type) — ENUM('ALERT','REPORT','CONSENT','HANDOVER')
  • Field (title) — VARCHAR(200)
  • Field (body) — TEXT
  • Field (is_read) — BOOLEAN DEFAULT FALSE
  • Field (created_at) — DATETIME DEFAULT NOW()
  • FOREIGN KEY ((receiver_id)) — REFERENCES users(user_id)
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL

parent_id (--보호자 id 관리 테이블)

  • (user_id) — BIGINT NOT NULL — Primary key — NOT NULL
  • Field (login_id) — varchar(100) NOT NULL
  • Field (password) — varchar(255) NOT NULL

patient (-- 환자)

  • 환자_id (patient_id) — INT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL — --1000단위 자동생성 증가
  • 시설_id (facility_id) — BIGINT NOT NULL — --시설 정보
  • 이름 (name) — VARCHAR(50) NOT NULL
  • (birth_date) — DATE
  • (room_number) — VARCHAR(20) — -- A100 - B-100 이런식으로 활용 가능
  • (admission_date) — DATE — -- 입원 날짜
  • (care_grade) — TINYINT — -- 장기요양 등급 1~5
  • (primary_diagnosis) — VARCHAR(100) — -- 뇌졸중, 파킨슨 등
  • (is_active) — BOOLEAN DEFAULT TRUE — -- 입퇴원
  • FOREIGN KEY ((facility_id)) — REFERENCES facility(facility_id)
  • Key (facility_id2) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → facility.Key — NOT NULL

patient_guardian (-- 환자-보호자 연결)

  • Key (id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (guardian_id) — BIGINT NOT NULL
  • Field (relation) — VARCHAR(30)
  • Field (is_primary) — BOOLEAN DEFAULT FALSE
  • FOREIGN KEY ((patient_id)) — REFERENCES patient(patient_id)
  • FOREIGN KEY ((guardian_id)) — REFERENCES users(user_id)
  • (user_id) — BIGINT NOT NULL — Foreign key → parent_id.user_id — NOT NULL
  • 환자_id (patient_id2) — INT PRIMARY KEY AUTO_INCREMENT — Foreign key → patient.환자_id — NOT NULL — --1000단위 자동생성 증가

patient_staff (-- 환자-담당 의료진 연결)

  • Key (id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (staff_id) — BIGINT NOT NULL
  • Field (assigned_at) — DATETIME DEFAULT NOW()
  • FOREIGN KEY ((patient_id)) — REFERENCES patient(patient_id)
  • FOREIGN KEY ((staff_id)) — REFERENCES users(user_id)
  • 환자_id (patient_id2) — INT PRIMARY KEY AUTO_INCREMENT — Foreign key → patient.환자_id — NOT NULL — --1000단위 자동생성 증가
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL

prescription (-- 처방)

  • Key (prescription_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (doctor_id) — BIGINT NOT NULL
  • Field (drug_name) — VARCHAR(100) NOT NULL
  • Field (dosage) — VARCHAR(50)
  • Field (frequency) — VARCHAR(50),
  • Field (start_date) — DATE
  • Field (end_date) — DATE
  • Field (status) — ENUM('ACTIVE','CHANGED','STOPPED') DEFAULT 'ACTIVE'
  • Field (created_at) — DATETIME DEFAULT NOW()
  • FOREIGN KEY ((patient_id)) — REFERENCES patient(patient_id)
  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → users.Key — NOT NULL
  • 환자_id (patient_id2) — INT PRIMARY KEY AUTO_INCREMENT — Foreign key → patient.환자_id — NOT NULL — --1000단위 자동생성 증가

users (-- 사용자 (간호사2(원무), 의사3, 요양사4, 보호자5))

  • Key (user_id) — BIGINT PRIMARY KEY AUTO_INCREMENT — Primary key — NOT NULL
  • Field (facility_id) — BIGINT — 외부 요양사 존재 할 경우
  • Field (name) — VARCHAR(50) NOT NULL
  • Field (email) — VARCHAR(100) UNIQUE NOT NULL — -- Only 사내 이메일과 본인인증
  • Field (phone) — VARCHAR(30) NOT NULL
  • Field (password) — VARCHAR(255) NOT NULL
  • Field (role) — NUM('CAREGIVER','NURSE' , 'DOCTOR','GUARDIAN') NOT NULL
  • Field (is_active) — BOOLEAN DEFAULT TRUE — -- 휴가 통합 구분
  • Field (created_at) — DATETIME DEFAULT NOW()
  • FOREIGN KEY ((facility_id)) — REFERENCES facility(facility_id)
  • fk (user_id2) — BIGINT NOT NULL — Foreign key → parent_id.user_id — NOT NULL
  • Key (facility_id2) — BIGINT PRIMARY KEY AUTO_INCREMENT — Foreign key → facility.Key — NOT NULL

vital_signs (-- 활력징후 (시계열))

  • Key — Primary key — NOT NULL
  • Field (time) — TIMESTAMPTZ NOT NULL
  • Field (patient_id) — BIGINT NOT NULL
  • Field (recorder_id) — BIGINT
  • Field (bp_systolic) — SMALLINT
  • Field (bp_diastolic) — SMALLINT
  • Field (temperature) — DECIMAL(3,1)
  • Field (pulse) — SMALLINT
  • Field (oxygen_sat) — SMALLINT
  • Field (blood_sugar) — SMALLINT