Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    4

    Question Unanswered: multiple references in a table

    Hi there. This is my 1st post in this good-looking forum. Here is my little problem...

    i am using mySQL.

    i have a database to manage a company and have a table for emploees (name, salary, ...) and a table for jobs (title, cost, ...). Each emploee can work in many jobs and of course each job is done by many emploees. How do i implement this in mySQL??


    I cannot add a field "emploee_id" in the jobs table cause clearly there are more than 1 emploees on the job. How can i make this dynamic? Always have space for more emploees and never unused space?

    I am sure this a trivial matter cause it is very common!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You need a new table that links employees to jobs. It needs to have the PK from employee and from job, and I'd strongly suggest including dates for when the relationship began and ended too. This allows you to have one employee on many jobs, one job done by many employees, and the begin/end dates allow you to put an employee on a job, take them off for a while, put them back again as needed.

    -PatP

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so, to recap, the EmployeeJobs linking table's PK will be a 3-column composite: emp_id, job_id, and startdate

    EFTS*: show the CHECK constraint to ensure you do not overlap the startdate/enddate of a single employee's jobs

    * EFTS was my old high school teacher's acronym for "exercise for the student" which meant it was optional but encouraged for what you would learn from trying it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2007
    Posts
    4
    Aha, ok. This sounds simple enough. I'll try it out tomorrow and let you know. Thanx

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    In relationship terms you are looking at the following :

    One Job has Many employees (working on it)
    One Employee has Many Jobs (that they are working on)

    Thus many->many relationship, which means in order to model it you're looking at a:
    Code:
    employee             employeeJobs              jobs
        one ---- FK -----> many <----- FK ---- one
    Using the above you can keep your employees normalised by Primary Key (only ONE of each)
    And keep your jobs normalised also (only ONE of each)

    Hope that helps you understand what you're doing better.

    note : FK = foreign key constraint

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    I'll elaborate a little more with the following example :

    Code:
    TABLE employees
    =====================
    id | firstname | lastname
    =====================
    1  | Joe         | Bloggs
    2  | John        | Doe
    
    TABLE Jobs
    =========================
    id | Title          | Description
    =========================
    1  | Amazing Job 1  | This job is so amazing that we want all employees on it
    2  | Amazing Job 2  | This job is less amazing so we only want Joe
    In the above tables (which I guess is what you've modelled so far) you want to have Joe working on Job2 BUT Joe AND John working on Job1....

    Here comes the help :
    Code:
    TABLE employeeJobs
    =================
    employee_id | job_id
    =================
    1          | 2
    1          | 1
    2          | 1
    From the above table you can see that Joe is mapped to Job 2, but BOTH Joe and John are mapped to Job 1... Wow I hear you say!

  7. #7
    Join Date
    Aug 2007
    Posts
    4
    Thanx for all the posts guys. I've done it and it works great!

    one last question. Is there a FK (Foreign Key) contraint in mySQL? (though i dont understand why i should use it)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes there is and yes you should because it guarantees relational integrity

    that way you will never find a row in the EmployeeJobs table that has an invalid Employee_id or Job_id value

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2007
    Posts
    4
    thats exactly what happened to me today when i deleted an emploee :P hehe i will look into it. Thanx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •