Article Data mining

Let's build a web scraper - Part 2 - Defining the data model

Sámuel Mészáros

Sámuel Mészáros

• 2 min read
Prerequisite: Let's build a web scraper - Part 1 - Defining the scope

My main concern is to collect as many data as possible and later filter it out strictly. Keep in mind, that data can come from various sources in various forms, so the data model should be flexible enough to handle them all.

The second concern is, of course, the filtering. What type of queries I want to execute on the gathered data? I will break them down one by one, but first:

Entities of the data model

It's a job listing scraper, therefore it comes naturally that job is the main entity of my DM. What is linked to the jobs? The companies that searching for the right person to fullfil the vacancy. I can define a lot more, but these two are most import for me now, the others can come later. Now return to the above mentioned concerns.

Flexible way to store data

I have two entities only, it's a super simple relational model where one company has many job listings. That means my queris stay simple, since I don't need to join dosen of tables to get the desired results. The other thing it means is denormalized data, but in cost of data duplication I get simplicity. It totally worth it for me.

The entities are the tables of relational model, what are the columns? I'm using SQLite intentionally, it has a reserved amount of data types compared to more serious RDBMS such as MySQL. Is it a bad thing? Not really, I can store all my scraped data in it and my purpose was to make my data model easily transferable.

Company table

Bellow every job description there is a "Upoznaj kompaniju" section from where I can get more than enough information. I am going to aggregate the vast amount of data like this:

CREATE TABLE IF NOT EXIST Company (
    company_id INTEGER PRIMARY KEY,
    company_link TEXT,
    external_id INTEGER,
    company_external_link TEXT, # real website of company
    tech_stack TEXT, # list of technologies used by the company
    stars REAL, # 1 - 5,
    description TEXT,
    feedback_count INTEGER,
    salary_max REAL,
    salary_min REAL,
    salary_average REAL,
    salary_standard_deviation REAL,
    salary_count INTEGER,
    last_sync_date TEXT, # when was updated the company e.g. new salary range added
    active INTEGER
);

Job table

Let's take a glance at helloworld.rs and list out what kind of data can be extracted. After one entered the landing page and hit the "IT poslovi" button, a job listing time line with a bunch of filters and of course the jobs themself. If one clicks on a job link, it will navigate the visitor to the full description page of job. Not to mention, there is minor differences between the listings for example: some has a star indicating the ranking of company. For beginning, the DDL of job table:

CREATE TABLE IF NOT EXISTS Job (
  job_id INTEGER PRIMARY KEY,
  company_id INTEGER NOT NULL, # this will be a foreign key to Company table
  external_id INTEGER, # the job listings has own id in helloworld.rs
  title TEXT,
  job_link TEXT,
  apply_link TEXT,
  location TEXT,
  tags TEXT,
  seniority TEXT,
  deadline_date TEXT,
  created_date TEXT,
  description TEXT,
  active INTEGER,
  FOREIGN KEY (company_id) REFERENCES Company(company_id) ON DELETE CASCADE;
  );

Technical part

To make my model version controlled, I wrap it into TypeORM's migration inside my project.

Next up: Let's implement the first automated action
Share