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