Table Design Question?

  • Thread starter Thread starter Daryl G.
  • Start date Start date
D

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
 
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?
Click to expand...

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.
 
Thanks John
John Vinson said:
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.
Click to expand...
 
Back
Top