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

I think if I were designing this, I would have a master Skills table that contains the universe of defined skills. I would then have a JobSkills table and PeopleSkills table. Both would have a FK reference to the master Skills table.

Answer 2

Performance results will depend on usage. Will people primarily search for just peopleskills, or just job skills, or people whose skills match a job? Do you expect frequent updates of data? What's an acceptable "freshness" to your data (ie, how quick should a change be reflected in the search)? What system are you using Oracle, MSSQL, MySQL...?

There are very few universal rules for performance that apply to anything. Programming just isn't that thought-free.

Answer 3

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.

Answer 4

Personally I would use two tables, people_skills and job_skills. I find it easier to think about it that way and to write joins against them.

Regards
K

PS If your two tables ever get huge, you can always move them to separate disks to reduce io contention when doing heavy queries.