Help with MySQL and other Database issues.?

Question

I have been using MySQL and PHP for a few months now and have a working knowledge of how things go together. I am working on my second application and I am suddenly stumped with a fundamentals problem:

I have a ‘JOBS’ table and a ‘STAFF’ table. The JOBS table lists events and details for different jobs. The STAFF table lists employees and their details. If I want to link the two tables, how should I do it?

e.g.
JOB_1 is staffed by 3, 4, 13
JOB_2 is staffed by 3, 6
JOB_3 is staffed by no one
JOB_4 is staffed by 6 only

Some jobs may have no staff; others may have 20-30.

So, in my JOB table, how do I configure the Column that will link to the possible Staff entries?

Like this:
JOB.staff_job = 3, 4, 13

Then how do I build the query to locate these entries?
SELECT * FROM job RIGHT JOIN staff ON id_staff = staff_job

Will a query like this know to grab either 1 record, no records or several records?

Answer 1

Hello,



You have a JOBS table which has events and different jobs

You have a STAFF table which has staffs that has names, etc ..



Now we have a relation between JOBS and STAFF, so your saying you have a ONE-to-MANY relation where a JOB has many STAFFS. So you have to implement a Relation Table.



We want to make sure a job has many staff, and one staff per job, so you have to make a relation ( where I will name employees) for some job. I will explain what I mean in a bit!.



You stated in your question that you want to link those two tables, and you want to know who have some jobs that has no staff. And you want to know who has some jobs that has 20 staff. A 'staffed' table could be used (or anything you want to call it) can do that, you just assign which STAFF_ID has which JOB_ID, that way you will NORMALIZE your table . Normalization is explained in the link I gave you below.



Initial Design

==============================

Existing Tables:

JOBS Table: jobID | jobName | jobDescription

-> where primarykey = jobID

STAFF Table: staffID | staffName

-> where primarykey = staffID



A New Table we will add:

EMPLOYEES Table: staffID | jobID

-> where primarykey = (staffID, jobID)

-> the reason why we want to make a group primary key cause we do not want duplicates of the same person having the same job, plus searching on that pk index is very quick.



Physical Design

==============================

CREATE TABLE jobs (

jobID int AUTO_INCREMENT,

jobName varchar 20,

jobDesc varchar 20,

PRIMARY KEY(jobID) );



INSERT INTO jobs VALUES (1, 'software developer', 'sql queries');

INSERT INTO jobs VALUES (2, 'buisness analyst', 'deals with businesss');

INSERT INTO jobs VALUES (3, 'secretary', 'deals with businesss');



CREATE TABLE staff (

staffID int AUTO_INCREMENT,

staffName varchar 20

PRIMARY KEY(jobID) );



INSERT INTO staff VALUES(1,'m0');

INSERT INTO staff VALUES(2,'Amanda);

INSERT INTO staff VALUES(3,'Mike');

INSERT INTO staff VALUES(4,'Joe');



CREATE TABLE staffed (

jobID int,

staffID int,

PRIMARY KEY(jobID,staffID),

FOREIGN KEY(jobID) REFERENCES jobs(jobID),

FOREIGN KEY(staffID) REFERENCES staff(staffID));



// m0 has job 1

INSERT INTO staffed VALUES(1,1);

// mike has job 1

INSERT INTO staffed VALUES(1,3);

// amanda has job 3

INSERT INTO staffed VALUES(3,2);

// joe has job 2

INSERT INTO staffed VALUES(2,4);





Discussion how to query this...

=============================

lets select all employees who has job1 ...



SELECT staffName FROM staff s

LEFT JOIN staffed st ON s.staffID = st.staffID

LEFT JOIN job j ON j.jobID = st.jobID



^^ Notice we are joining tables that ONLY have the relation where it meets in staffed table. When I did 'staffed st' it is temporarly renaming that table to st so I can present it better. So instead of doing JOIN staffed ON staff.staffID = staffed.staffID, i can do it in short form like; JOIN staffed st ON s.staffID = st.staffID



If you need more info, let us know, and we will be glad to help. I was a Database Teachers assistance during my undergrad :)-



Take a look at the following source to learn more about normalization

Answer 2

The way that I would approach this would be to actually define another table, which we could call something like "Positions" to represent the relationship between staffers and jobs.



So, what you have now is a table with jobs and job details, and another table with staff and staff details.



In your new table, you might have a column for a job id, and a column for a staffer.



Each row in your Positions table would represent one Job to Staffer relationship.



Based on your example, you first 3 rows might be something like:

Job 1 - Staffer 3

Job 1 - Staffer 4

Job 1 - Staffer 13



and so on for the other jobs in your example.



The advantage of an approach like this is that you aren't trying to shoehorn multiple staff id's into a single row in your Job table, or multiple job id's into each staff entry in your Staff table.



Now, when you want to see who is working on Job 1, you can just say:



SELECT * FROM POSITIONS WHERE JOB_ID = 1



And, if you want person details or job details, you can do a join with one(or both) of your other tables.



If you are interested in learning more about this, you might want to read up on what's called "database normalization", which is basically a set of rules to determine the best way to lay out tables.



This: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html



might be a good beginners resource, although I haven't read all of it yet.

Answer 3

It will most likely display all the jobs with all the staffs , if u want only one job with 1 staff, use GROUP BY job_id



cheers

Answer 4

I think this will get you on your way...helped me ;)



http://www.plus2net.com/sql_tutorial/sql_linking_table.php