emp

테이블 (20)

admin

  • admin_id — BIGSERIAL — 기본 키 — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20)
  • role_level — INTEGER
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

ai_recommendation

  • recommendation_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • symptom_analysis_id — BIGINT — 외래 키 → symptom_analysis.analysis_id
  • recommendation_type — VARCHAR(50) — NOT NULL
  • recommended_institution_type — VARCHAR(50)
  • recommended_department — VARCHAR(50)
  • recommended_location — VARCHAR(200)
  • confidence_score — NUMERIC(5,2)
  • reasoning — TEXT
  • created_at — TIMESTAMP

chat_message

  • message_id — BIGSERIAL — 기본 키 — NOT NULL
  • room_id — BIGINT — 외래 키 → chat_room.room_id — NOT NULL
  • sender_id — BIGINT — NOT NULL
  • sender_type — VARCHAR(20) — NOT NULL
  • message_type — VARCHAR(20)
  • content — TEXT — NOT NULL
  • attachment_url — VARCHAR(255)
  • is_read — BOOLEAN
  • created_at — TIMESTAMP

chat_room

  • room_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id
  • doctor_id — BIGINT — 외래 키 → doctor.doctor_id
  • admin_id — BIGINT — 외래 키 → admin.admin_id
  • room_type — VARCHAR(20) — NOT NULL
  • last_message — TEXT
  • last_message_date — TIMESTAMP
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • status — VARCHAR(20)

community_board

  • board_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • view_count — INTEGER
  • like_count — INTEGER
  • comment_count — INTEGER
  • is_anonymous — BOOLEAN
  • is_notice — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • status — VARCHAR(20)

community_comment

  • comment_id — BIGSERIAL — 기본 키 — NOT NULL
  • board_id — BIGINT — 외래 키 → community_board.board_id — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • parent_comment_id — BIGINT — 외래 키 → community_comment.comment_id
  • content — TEXT — NOT NULL
  • is_anonymous — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • status — VARCHAR(20)

consultation

  • consultation_id — BIGSERIAL — 기본 키 — NOT NULL
  • reservation_id — BIGINT — 외래 키 → reservation.reservation_id
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • doctor_id — BIGINT — 외래 키 → doctor.doctor_id — NOT NULL
  • consultation_date — TIMESTAMP — NOT NULL
  • consultation_type — VARCHAR(30)
  • stt_transcript — TEXT
  • diagnosis — TEXT
  • prescription — TEXT
  • prescription_image — VARCHAR(255)
  • notes — TEXT
  • ai_generated_emr — TEXT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

doctor

  • doctor_id — BIGSERIAL — 기본 키 — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20)
  • license_number — VARCHAR(50) — NOT NULL
  • specialization — VARCHAR(100)
  • profile_image — VARCHAR(255)
  • introduction — TEXT
  • rating — NUMERIC(3,2)
  • years_of_experience — INTEGER
  • available_hours — JSONB
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • status — VARCHAR(20)

health_prediction

  • prediction_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • prediction_date — DATE — NOT NULL
  • predicted_diseases_json — JSONB
  • risk_level — VARCHAR(20)
  • recommendations — TEXT
  • ai_reasoning — TEXT
  • confidence_score — NUMERIC(5,2)
  • created_at — TIMESTAMP

iot_data

  • iot_data_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • data_type — VARCHAR(50) — NOT NULL
  • measurement_value — NUMERIC(10,2) — NOT NULL
  • measurement_unit — VARCHAR(20) — NOT NULL
  • is_abnormal — BOOLEAN
  • alert_sent — BOOLEAN
  • measurement_date — TIMESTAMP — NOT NULL
  • device_id — VARCHAR(100)
  • created_at — TIMESTAMP

medication_schedule

  • schedule_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • consultation_id — BIGINT — 외래 키 → consultation.consultation_id
  • medication_name — VARCHAR(100) — NOT NULL
  • dosage — VARCHAR(50) — NOT NULL
  • frequency — VARCHAR(50) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE
  • reminder_times — JSONB
  • is_active — BOOLEAN
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

notification

  • notification_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • notification_type — VARCHAR(30) — NOT NULL
  • title — VARCHAR(100) — NOT NULL
  • content — TEXT
  • target_url — VARCHAR(255)
  • send_date — TIMESTAMP
  • is_read — BOOLEAN
  • created_at — TIMESTAMP

reservation

  • reservation_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • doctor_id — BIGINT — 외래 키 → doctor.doctor_id — NOT NULL
  • reservation_date — DATE — NOT NULL
  • reservation_time — TIME — NOT NULL
  • symptoms — TEXT
  • department_type — VARCHAR(50)
  • institution_type — VARCHAR(50)
  • status — VARCHAR(20)
  • meeting_link — VARCHAR(255)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

symptom_analysis

  • analysis_id — BIGSERIAL — 기본 키 — NOT NULL
  • user_id — BIGINT — 외래 키 → user.user_id — NOT NULL
  • input_method — VARCHAR(20) — NOT NULL
  • symptom_text — TEXT
  • symptom_image — VARCHAR(255)
  • ai_generated_questions_json — JSONB
  • user_answers_json — JSONB
  • analyzed_diseases_json — JSONB
  • priority_order — INTEGER
  • reasoning — TEXT
  • created_at — TIMESTAMP

user

  • user_id — BIGSERIAL — 기본 키 — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20)
  • birth_date — DATE
  • gender — VARCHAR(10)
  • address — VARCHAR(200)
  • profile_image — VARCHAR(255)
  • medical_history — TEXT
  • preferred_language — VARCHAR(10)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • status — VARCHAR(20)

부서 (dept)

  • 부서번호 (deptno) — int — 기본 키 — NOT NULL
  • 부서명 (dname) — varchar(20)
  • 부서위치 (loc) — varchar(20)

사용차량 (emp_car)

  • 사번 (empno) — int — 외래 키 → 사원.사번 — NOT NULL
  • 일련번호 (car_id) — int — 외래 키 → 자동차.일련번호 — NOT NULL
  • 운행거리 (mileage) — int

사원 (emp)

  • 사번 (empno) — int — 기본 키 — NOT NULL
  • 이름 (ename) — varchar(10)
  • 직무 (job) — varchar(20)
  • 매니저 (mgr) — int
  • 입사일 (hiredate) — date
  • 월급 (sal) — int
  • 커미션 (comm) — int
  • 부서번호 (deptno) — int — 외래 키 → 부서.부서번호 — NOT NULL

월급등급 (salgrade)

  • 등급 (grade) — 기본 키 — NOT NULL
  • 하위레벨 (losal)
  • 상위레벨 (hisal)

자동차 (car)

  • 일련번호 (car_id) — int — 기본 키 — NOT NULL
  • 차량번호 (car_no) — varchar(8)
  • 차량이미지 (car_img) — varchar(48)