Generaly in database design what is better one table with two references or one table?

Question

Let's say i have people who search for jobs, and i have a list of jobs. So i have two tables: people and jobs. Now i have a list of skills of the person, and i have a list of skills for the job requirement.

What is better to have ONE skills table like this:

CREATE TABLE skills_reference
(
id INT,
reference_id INT, -- can reference people(id) or job(id)
reference ENUM('person','job'),
skill FOREIGN KEY REFERENCE skills(id)
)

OR to have TWO table, one for people_skills and one for jobs_skills. Which one will give the better performance results?

Thanks.

Answer 1

IMO, you should make two tables, one for job_skill(job_id, skill_id) and one for person_skills(person_id,skill_id). Both point to the same skills table though.

I should point out that performance is just one consideration, and in many, many cases, you should first focus on logical sound design of th data model and then on performance (if it is a problem at all).

With RDBMS work, in many cases (80%), the cleanest design is also best for performance.