multiple references in a table

SirDuke · #1 (**permalink**) 08-26-07, 15:03

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!

Pat Phelan · #2 (**permalink**) 08-26-07, 15:22

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

r937 · #3 (**permalink**) 08-26-07, 16:16

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

SirDuke · #4 (**permalink**) 08-26-07, 18:36

Aha, ok. This sounds simple enough. I'll try it out tomorrow and let you know. Thanx

aschk · #5 (**permalink**) 08-28-07, 07:20

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

aschk · #6 (**permalink**) 08-28-07, 07:26

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!

SirDuke · #7 (**permalink**) 08-29-07, 18:54

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)

r937 · #8 (**permalink**) 08-29-07, 19:15

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

SirDuke · #9 (**permalink**) 08-31-07, 06:10

thats exactly what happened to me today when i deleted an emploee :P hehe i will look into it. Thanx

Internet Services

Web Development

Digital Marketing

Consumer Tech