db_assignment

Tables (9)

applications

  • application_id — INT
  • user_id — INT — Foreign key → users.user_id — NOT NULL
  • job_id — INT — Foreign key → jobs.job_id — NOT NULL
  • applied_at — DATETIME
  • status — ENUM('PENDING', 'ACCEPTED', 'REJECTED')
  • cover_letter — TEXT

blocks

  • block_id — INT
  • blocker_id — INT — Foreign key → users.user_id — NOT NULL
  • blocked_user_id — INT — Foreign key → users.user_id
  • blocked_job_id — INT — Foreign key → jobs.job_id
  • blocked_at — DATETIME

evaluations

  • eval_id — INT
  • reviewer_id — INT — Foreign key → users.user_id — NOT NULL
  • reviewee_id — INT — Foreign key → users.user_id — NOT NULL
  • job_id — INT — Foreign key → jobs.job_id — NOT NULL
  • score — INT — NOT NULL
  • comment — TEXT — EX
  • eval_type — ENUM('EMPLOYER_TO_SEEKER', 'SEEKER_TO_EMPLOYER') — NOT NULL
  • created_at — DATETIME

jobs

  • job_id — INT
  • employer_id — INT — Foreign key → users.user_id — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • category — VARCHAR(50)
  • hourly_wage — INT — NOT NULL
  • location — VARCHAR(255)
  • work_hours — VARCHAR(100)
  • deadline — DATETIME
  • description — TEXT
  • status — ENUM('OPEN', 'CLOSED')
  • created_at — DATETIME

notifications

  • notif_id — INT
  • user_id — INT — Foreign key → users.user_id — NOT NULL
  • type — ENUM('APPLICATION_RESULT', 'NEW_RECOMMENDATION', 'SYSTEM_ALERT') — NOT NULL
  • message — VARCHAR(255) — NOT NULL
  • is_read — BOOLEAN
  • created_at — DATETIME

recommendations

  • rec_id — INT
  • user_id — INT — Foreign key → users.user_id — NOT NULL
  • job_id — INT — Foreign key → jobs.job_id — NOT NULL
  • score — INT
  • reason — VARCHAR(255)
  • is_viewed — BOOLEAN
  • created_at — DATETIME

reports

  • report_id — INT
  • reporter_id — INT — Foreign key → users.user_id — NOT NULL
  • target_job_id — INT — Foreign key → jobs.job_id
  • target_user_id — INT — Foreign key → users.user_id
  • reason — TEXT — NOT NULL
  • status — ENUM('PENDING', 'RESOLVED')

user_categories

  • id — INT
  • user_id — INT — Foreign key → users.user_id — NOT NULL
  • category — VARCHAR(50) — NOT NULL

users

  • user_id — INT
  • email — VARCHAR(255) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • phone — VARCHAR(20)
  • role — ENUM('EMPLOYER', 'SEEKER', 'ADMIN') — NOT NULL
  • region — VARCHAR(100)
  • preferred_time — VARCHAR(100)
  • created_at — DATETIME
  • is_active — BOOLEAN