Let us say i've individuals who look for jobs, and that i have a listing of jobs. And so i have two tables: people and jobs. Now i've a listing of abilities of the individual, and that i have a listing of abilities to do the job requirement.
What 's better to have ONE abilities table such as 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) )
In order to have TWO table, one for individuals_abilities and something for jobs_abilities. Which can give the greater performance results?
IMO, you need to make two tables, one for
job_skill(job_id, skill_id) and something for
person_skills(person_id,skill_id). Both indicate exactly the same abilities table though.
I ought to explain that performance is simply one consideration, as well as in many, most cases, you need to first concentrate on logical seem style of th data model after which on performance (if it's an issue whatsoever).
With RDBMS work, oftentimes (80%), the cleanest design can also be perfect for performance.
I believe basically were creating this, I'd possess a master Abilities table that consists of the world of defined abilities. I'd then possess a JobSkills table and PeopleSkills table. Both might have a FK mention of master Abilities table.
Performance results will rely on usage. Will people mainly look for just peopleskills, or simply job abilities, or people whose abilities match employment? Would you expect frequent updates of information? What's a suitable "quality" for your data (ie, how quick should a big change be reflected within the search)? What system are you currently using Oracle, MSSQL, MySQL...?
You will find very couple of universal rules for performance that affect anything. Programming just is not that thought-free.
Personally I'd use two tables,
job_skills. I've found it simpler to consider it this way and also to write joins against them.
PS In case your two tables find yourself getting huge, you could move these to separate disks to lessen io contention when you are performing heavy queries.