Data Modeling: Visa-Sponsor Jobs

Normalized a flat, scraped jobs table (analytics.visa_sponsor_jobs, 1,392 rows from 4 public sources) into a 3NF schema, project1_jobs, and wrote analytical SQL on top of it. This schema is the base the later pipeline, performance and CDC projects all build on.

Tools

Tool	Used for
MySQL 8.4 (Docker, home server)	Source table and the new normalized schema
SQL (DDL, INSERT … SELECT, window functions, CTEs, ROLLUP)	Schema, migration and analysis
mysql CLI over SSH	Running the scripts and checking results

Steps

1. Design the schema

What it does: splits the flat table into companies, locations, sources and jobs. Company, location and source repeat across rows and have their own identity, so they became lookup tables. Low-cardinality labels (job_field has only 2 values, plus remote_type, seniority) stayed as columns on jobs: a join to a 2-row table on every query adds complexity for no real gain at this size.

CREATE TABLE project1_jobs.jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  company_id INT NOT NULL,
  location_id INT NULL,
  source_id INT NULL,
  remote_type VARCHAR(50),
  job_field VARCHAR(100),
  seniority VARCHAR(50),
  link VARCHAR(1000) NOT NULL,
  -- link is too long for a normal unique index, so uniqueness is enforced on its hash
  link_hash CHAR(64) GENERATED ALWAYS AS (SHA2(link, 256)) STORED UNIQUE,
  date_posted DATE,
  date_found DATETIME DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_jobs_company  FOREIGN KEY (company_id)  REFERENCES companies(id) ON DELETE CASCADE,
  CONSTRAINT fk_jobs_location FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
  CONSTRAINT fk_jobs_source   FOREIGN KEY (source_id)   REFERENCES sources(id)   ON DELETE SET NULL,
  INDEX idx_job_field (job_field),
  INDEX idx_date_posted (date_posted)
);

Key choices:

• Surrogate keys: scraped company names aren't stable (casing, whitespace, rebrands), so a natural key would force cascading renames.
• link_hash: a 1,000-character utf8mb4 URL exceeds MySQL's 3,072-byte index limit. A stored SHA-256 column carries the unique constraint instead, and this later becomes the pipeline's upsert key.
• Foreign keys: a job can't exist without its company (CASCADE), but losing a location or source shouldn't delete the job (SET NULL).

Result: schema created with PKs, FKs and two query-driven indexes.

2. Migrate the data

What it does: fills each lookup table with its distinct values, then inserts the jobs, joining back to get the new IDs.

INSERT INTO project1_jobs.companies (name)
SELECT DISTINCT TRIM(company) FROM analytics.visa_sponsor_jobs
WHERE company IS NOT NULL AND TRIM(company) <> '';

INSERT INTO project1_jobs.jobs (title, company_id, location_id, source_id, link, date_posted /* … */)
SELECT v.title, c.id, l.id, s.id, v.link, v.date_posted /* … */
FROM analytics.visa_sponsor_jobs v
LEFT JOIN project1_jobs.companies c ON c.name = TRIM(v.company)
LEFT JOIN project1_jobs.locations l ON l.location_text = TRIM(v.location)
LEFT JOIN project1_jobs.sources   s ON s.name = v.source;

Result:

Table	Rows	Check
jobs	1,392	matches the source exactly
companies	414	matches COUNT(DISTINCT company) exactly
locations	340	344 distinct strings in the source; 4 jobs had a blank location and were left as NULL, not invented
sources	4

3. Analytical queries

Top 3 employers per country (window function).

SELECT country, company_name, job_count, rank_in_country
FROM (
  SELECT l.country, c.name AS company_name, COUNT(*) AS job_count,
         RANK() OVER (PARTITION BY l.country ORDER BY COUNT(*) DESC) AS rank_in_country
  FROM project1_jobs.jobs j
  JOIN project1_jobs.companies c ON c.id = j.company_id
  LEFT JOIN project1_jobs.locations l ON l.id = j.location_id
  WHERE l.country IS NOT NULL
  GROUP BY l.country, c.name
) ranked
WHERE rank_in_country <= 3;

Companies hiring both remote and on-site (CTE). Aggregates per company, then keeps those with more than one distinct remote_type.

Jobs by country × field with subtotals (ROLLUP).

SELECT IF(GROUPING(l.country), 'All countries', COALESCE(l.country, 'Unknown')) AS country,
       IF(GROUPING(j.job_field), 'All job fields', COALESCE(j.job_field, 'Unspecified')) AS job_field,
       COUNT(*) AS job_count
FROM project1_jobs.jobs j
LEFT JOIN project1_jobs.locations l ON l.id = j.location_id
GROUP BY l.country, j.job_field WITH ROLLUP;

Most productive source for remote jobs. Uses SUM(COUNT(*)) OVER () for the percentage.

Result: one source (jobright-ai/Daily-H1B-Jobs-In-Tech) produced about 89% of all remote listings.

Issues encountered

Rollup totals looked like missing data

• Symptom: with COALESCE(country, 'Unknown'), the grand-total row and real jobs with no country both showed as "Unknown".
• Cause: ROLLUP fills its subtotal rows with NULL, the same value as genuinely missing data.
• Fix: label rows with GROUPING(), which is 1 only for the synthetic rollup rows.

Garbled characters in company names (mojibake)

• Symptom: names like "Bank for International Settlements – BIS" instead of an en dash.
• Cause: found later, in 2026-08-29. The text was double-encoded at scrape time: UTF-8 bytes were decoded as Windows-1252 and re-encoded before reaching MySQL. Hex comparison (HEX(title) vs Postgres encode(title::bytea,'hex')) proved the bytes were identical everywhere downstream, so the database stored exactly what it was given.
• Fix: left as-is in this project, then repaired across 104 rows with an ftfy-based script in the pipeline project, which also now repairs text on the way in.

What would change at 10× the data

• De-duplicate before inserting instead of relying on the unique key to reject rows.
• Check index use with EXPLAIN instead of assuming it. The performance project does exactly this.
• Parse location text, since "Berlin, Germany" and "Berlin, DE" would become two rows.
• Make the one-shot INSERT … SELECT migration batched and idempotent. The pipeline project does this.