linking two tables

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.

From stackoverflow

• 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
  

• 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.

• This is called many-to-many relation, and the way to achieve that in RDBMS, is to have a junction table.

  You'd have 3 tables:

  • job — jobid (PK), jobdescription, etc
  • user — userid (PK), name, etc. (note, no jobid here)
  • userjob — userid, jobid (PK composite of both)