linking two tables

Question

I have two table: job with jobid, jobdescription, etc and user with userid, jobid, name, etc.

What is the best practice to link two jobid to the same userid in table user ?

I can do this with a intermediate table that store the entries of the jobs of the same user but I am thinking that is a more elegant solution.

Thank you.

Answer 1

Three Tables - Users, Jobs, UserJobs

Users table contains information pertaining to the user

Jobs table describes the individual jobs

UserJobs table acts as a bridge between your many-to-many relationships using a combination PK/FK of UserID/JobID.

Example

Users
UserID Name
---------------------------------
1      Doogie Howser
2      Steve Urkel
3      Forest Gump

Jobs
JobID JobDescription
---------------------------------
1     TV Character
2     Movie Character
3     Nerd


UserJobs
UserID JobID
---------------------------------
1      1
1      3
2      1
2      3
3      2

Answer 2

I see that TheTXI has already given you a good answer, Adding more to what he says: What you have is a many-to-many mapping between Users <=> Jobs. And whenever you have this situation you will have to use a mapping table which maps Users and Jobs. Most often a table called UserJobs would be created which would have a composite key containing a jobid and userid. Hope that helps.