0

I'm trying to build database which allow me store job postings from scraped websites and now I'm in phase of creating a relational model. Getting straight to the point I want to know how should I re-design my relational model if I know one of my linking table's column (FK3 level_id) will contain NULLs. And the model: enter image description here

In brief:

  1. Every job posting has title and source website (Jobs table).

  2. Sometimes job posting contains information about additional skills. Their names I store in Additional skill table

  3. Additional skill may be required or may be not required (isRequired column).

  4. Additional skill may or may not contain information about its level (I store level names in Level table).

  5. Because job posting may contain information about 0 or 1 or many additonal skills and each one of them may have its own level I associated everything in linking table (Addiotional_skill-Level-Jobs )

Overall_required_level linking table is not important in this question.

Because we know that job posting may contain several Additional skill and not every Additonal skill may contain information about its own Level so there will be nulls in level_id column in Additional_skill-Level-Jobs linking table. To be honest they are not as bad as they look but I'd like to know how could I improve my relational model if I know with certainty that one of the column will be having NULLs.

0

Whether to allow NULL values or not is a religious topic. If you decide that it's OK to have nulls, you'll need to add a synthetic primary key to the link table, because the combination of job_id, skill_id, and nullable level_id cannot uniquely identify a row.

1
  • Hah, I read the same thing somewhere on stackoverflow :P. Ok two things: why should I add synthetic key? I'm asking because I've read somewhere that linking tables don't necessarily need such one. And second: let's say I want to do something with this problematic column. What should I do? I mean how should I re-organize my model?
    – beginsql
    Sep 27 '21 at 18:25

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.