Table Design Question?

[Daryl G.]

When making a join table linking mutliple tables should I make one table or
two?

Example
Table one is People
Table two is Jobs
Table Three is Skills

Both People and Jobs have skills. Later I will want to query to see from a
Person what jobs are open with the skills they have, or from a job what
people have the skills to do the job

Should my Join be SkillCode,JobCode,PeopleCode
or two tables SkillCode,JobCode and SkillCode,People code?

Thanks,

Daryl

 

[John Vinson]

Both People and Jobs have skills. Later I will want to query to see from a
Person what jobs are open with the skills they have, or from a job what
people have the skills to do the job

Should my Join be SkillCode,JobCode,PeopleCode
or two tables SkillCode,JobCode and SkillCode,People code?

Both designs have their place.

If you want to be able to produce a listing of all of the skills
required for a given job (independent of whether any people in the
database have that job or that skill), or a list of all the jobs that
require a given skill, then you probably want two tables (similarly
for the people-skills link).

If you want to link all three tables, you may want the unified table.

You might have a good reason to create *all three* tables - the
People/Skills table to find people with a particular skill set, the
Job/Skills table to identify required skill sets, and the three-field
table to identify who's in what job with which skills.

 

[Daryl G.]

Thanks John

Both designs have their place.

If you want to be able to produce a listing of all of the skills
required for a given job (independent of whether any people in the
database have that job or that skill), or a list of all the jobs that
require a given skill, then you probably want two tables (similarly
for the people-skills link).

If you want to link all three tables, you may want the unified table.

You might have a good reason to create *all three* tables - the
People/Skills table to find people with a particular skill set, the
Job/Skills table to identify required skill sets, and the three-field
table to identify who's in what job with which skills.