I'm trying to design a database in order to store IT job offers. My data comes from 4 different websites and unfortunately only part of their attributes (column names) are common. It looks like this:

Website 1: title, overall experience level
Webiste 2: title, overall experience level, skills required, skills nice to see
Website 3: title, overall experience level, skills required, level of required skills (junior or mid or senior)
Webiste 4: title, overall experience level, skills required, level of required skills (in years), skills nice to see, language, level of langauge

I would like to know how would you design a database with this kind of data? I did some research and found that it can be done in different ways:

Approach 1: only one table with all attributes (there will be a lot of nulls) but to be honest I think it's bad idea.

Approach 2: one table storing common attributes to all websites and rest of attributes in separate tables. Also I must notice that each job offer has multiple values in skills required and skills nice to see attributes so I think these two must be in many-to-many relationship with JobOffer table enter image description here

Approach 3: Four tables with attributes assigned to each of websites (so each table represents each website with its atrributes). And like in previous approach skills required and skills nice to see are in many-to-many relationship with almost (except WEBSITE1 table) every website table enter image description here

Approach 4: Your own approach because none of these above fit well to this problem. (I've never been designing database before so I'm aware that all 3 approaches proposed by me could be wrong)

Thanks in advance!

  • Sparse table (one table with NULLs in unused columns) seems to be the simplest solution. And it provides simple solid unified processing. – Akina Oct 28 '20 at 10:14
  • Look into the following approach: Class Table Inheritance. It resembles your approach 2, but with more to it. – Walter Mitty Oct 28 '20 at 10:57

Considering "nice to see skills" and "required skills" as wholly separate attributes is a mistake. They are all just skills. Each Job has Skills then those JobSkills are either Required or not (yes/no). In particular, a skill might be required for one job and optional for another. Further, I'd suggest that a language is just another skill.

Mostly, it would seem like this would fit into a schema that looks something like this, where several of the things you have in separate tables become attributes on the Job-Skill cross reference.

  • Skill

    • SkillId
    • Name
    • Type (technical, language, etc)
  • Job

    • JobId
    • Name
    • Description
    • Overall level
    • Source website
  • JobSkill

    • JobId
    • SkillId
    • IsRequired
    • SkillLevel

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.