Skip to content

ERD

Jump to bottom
Eunji LEE edited this page Jun 27, 2026 · 11 revisions

Job-Demand Dashboard : Database Design

Django + Next.js · Adzuna API

This document defines the schema for data the app owns. Live job postings from Adzuna are not stored as tables — they live in Django's cache (TTL) and are refetched from Adzuna when they expire.

1. Stored in the DB vs. Cached

Type What How it's kept
Stored in DB Accounts, favourites, saved searches, daily analytics Permanent tables
Cached, not stored Live job listings, salary histograms, regional / trend data Django cache (refetched from Adzuna on TTL expiry)

Core principle: only model data you own. Adzuna postings are not our data, so they are never modeled as tables.

2. Entity Relationship Diagram

Below is the full schema — the four existing tables plus the new JOB_POSTINGS table for CSV imports.

erDiagram
  USERS ||--o{ FAVOURITE_JOBS : owns
  USERS ||--o{ SAVED_SEARCHES : owns
  USERS {
    int users_id PK
    string email
    string password_hash
    datetime created_at
    datetime last_updated
  }
  FAVOURITE_JOBS {
    int favourite_jobs_id PK
    int users_id FK
    string adzuna_id
    string title
    string company
    int salary_min
    int salary_max
    string location
    string redirect_url
    datetime saved_at
    datetime last_updated
  }
  SAVED_SEARCHES {
    int saved_searches_id PK
    int users_id FK
    string keyword
    string location
    string sort_by
    bool alert_enabled
    datetime created_at
    datetime last_updated
  }
  DEMAND_SNAPSHOTS {
    int demand_snapshots_id PK
    string skill
    int count
    date captured_at
    datetime last_updated
  }
  JOB_POSTINGS {
    int job_postings_id PK
    string source
    string external_id
    string title
    string company
    int salary_min
    int salary_max
    string location
    text description
    string url
    datetime posted_at
    datetime created_at
    datetime last_updated
  }
Loading

Relationship summary:

  • USERS 1 : N FAVOURITE_JOBS — one user owns many favourites
  • USERS 1 : N SAVED_SEARCHES — one user owns many saved searches
  • DEMAND_SNAPSHOTS — no relationship (global analytics data)
  • JOB_POSTINGS — no relationship (a shared listing visible to everyone, new)

Naming convention: every table's primary key is prefixed with the table name (users_id, favourite_jobs_id, …). Foreign keys reference the parent's key by the same name (e.g. users_id in FAVOURITE_JOBS points to USERS.users_id).

last_updated: every table carries a last_updated timestamp that is refreshed on each write. In Django this maps to auto_now=True on a DateTimeField.

3. Table Details

USERS

The account record behind login and registration.

Field Description
users_id (PK) Primary key
email Email address
password_hash Always stored hashed (never plain text)
created_at Created timestamp
last_updated Updated on every write

Django's built-in User model works fine here. One USERS row owns many favourites and saved searches.

FAVOURITE_JOBS

Jobs a user has starred — create / read / delete (CRD).

Field Description
favourite_jobs_id (PK) Primary key
users_id (FK) Owning user
adzuna_id Original Adzuna posting identifier
title, company, salary_min, salary_max, location, redirect_url Only the fields needed, copied at save time
saved_at When it was saved
last_updated Updated on every write

Don't store the whole Adzuna posting — copy only the fields you need at save time. The favourite survives even if Adzuna later drops the posting.

SAVED_SEARCHES

A user's saved search conditions — full CRUD.

Field Description
saved_searches_id (PK) Primary key
users_id (FK) Owning user
keyword, location, sort_by The query itself (not the results)
alert_enabled Email-alert on/off toggle (the bell icon in the UI)
created_at Created timestamp
last_updated Updated on every write

Stores the query, not the results. Re-run it any time to get fresh listings.

DEMAND_SNAPSHOTS

A daily analytics time-series — the odd one out, with no user link.

Field Description
demand_snapshots_id (PK) Primary key
skill Skill name
count Number of postings at capture time
captured_at Capture date
last_updated Updated on every write

Adzuna has no "demand over time" endpoint, so a scheduled job runs once a day and writes one row per skill (skill, count, captured_at). Over weeks these rows become the trend graph. No users_id — it's global data.

Example rows:

skill count captured_at
Python 142 2026-06-24
Java 98 2026-06-24
Python 145 2026-06-25

JOB_POSTINGS (new)

Job listings we own, pushed in directly (e.g. via CSV).

Field Description
job_postings_id (PK) Primary key
source Origin — "csv" / "adzuna" / "manual"
external_id Original identifier; prevents duplicate imports
title, company, salary_min, salary_max, location Basic posting info
description Full description
url Link to the posting
posted_at When the job was posted
created_at Import timestamp
last_updated Updated on every write

Adzuna postings only sit in the cache briefly, but CSV postings must be stored permanently, so a separate table is required. FAVOURITE_JOBS can't be reused because it's per-user data.

4. Considerations for CSV Import

Once CSV postings are stored in the DB, the listings shown on screen come from two sources:

  1. The JOB_POSTINGS table (our postings)
  2. The Adzuna cache (external postings)

So decide up front how the frontend (JobList.js) merges them:

  • Merge into one list → needs combining logic in the backend
  • Split into tabs → "External postings" / "Our postings"

5. Three Things to Remember

  1. User data is one-to-many — one USERS row owns many FAVOURITE_JOBS and SAVED_SEARCHES rows.
  2. Analytics stands alone — DEMAND_SNAPSHOTS has no user link; it's global data built by a daily job.
  3. Adzuna postings are cached, not modeled — the DB only holds data you own. Only CSV postings are stored, in JOB_POSTINGS.

Reference: How to populate DEMAND_SNAPSHOTS table everyday Reference: Choosing a Database Engine

Clone this wiki locally