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!
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.
I'll elaborate a little more with the following example :
id | firstname | lastname
1 | Joe | Bloggs
2 | John | Doe
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....