MySQL database design help

[ParkerDigital]

I'm designing a database for a recruitment agency; I need to be able to store details for job seekers, which will include which job types/industries they would like to work in. They need to be able to select job types/industries from a long list, and I'm wondering which is the best way to record which users are connected to which industries? I was thinking of creating a table for each job type/industry, and then simply adding user IDs to the relevant tables - but the job type/industry list has 40/50 entries. Is it feasible to have 40/50 tables within the same MySQL database, or can anyone suggest a better way of doing it?

[Geekygirl]

Give me a job and I'll do it for you................

[SiE]

dont make lots of tables, just use one and use a field to categorise each job type and another field for the industry then use queries to differentiate them.

[ParkerDigital]

SiE, I see what you mean about just having one table , but the problem I'm having is working out how employers can search for people looking for work in a certain industry. So when a job seeker registers, they might specify (for example) that they're looking for work in Web Design (one job type) or Web Development (another job type). A job seeker should be able to specify as few or as many job types as they like. Then, when an employer is looking for a Web Designer, any job seekers that are looking for a job as a Web Designer should come up in the search results. So what's the best way to attach job seeker profiles to certain job types? Hope I'm explaining myself clearly - it's all very confusing to me!

[/dev/null]

Hi, You would be needing three tables at least as you are requiring a many-many relationship. If you want industries to be separate (ie, you could say computing industry) then you can include a further table of industry however for now it seems you are just looking at linking the job types so I'll explain that - if you do want separate industries then let me know and I'll update it!
Anyway, you would have the following tables: (Hope you are familiar with this notation - if not, say so!)

JobType(ID, description, etc etc...)
Person(ID,forename, surname, telephone, etc etc...)
This is the important 'link' table!
SeeksJob(fk:JobType:ID,fkerson:ID, dateCreated, etc etc...)

So... Everytime a person wants to seek a job then you create a new record in SeeksJob with the id of the person, and the id of the job they seek. I used 'id' for the primary key of jobtype and person although you 'could' say that the primary key is another field (national insurance number, job type etc... it just is easier to do it this way atm!)
The primary key in seeksjob is a composite of the foreign keys 'id' from person and jobtype. Obviously where I have put 'etc etc...' you can store any other info you may want to in there...

So, lets say you populate your database with the following job types (small atm):

1 - Web Designer
2 - Programmer
3 - Slave

and I come along saying I am looking for a job as a programmer. You then create a "person" record for me along the lines of:

INSERT INTO person VALUES('','John','Keymer','01344567890'');

I have assumed that you have used auto_increment on 'ID' so it automatically give sme an ID if I supply null to that field. Lets say it gives me ID number 1. (I am the first in the database! {This number system starts at 1, not 0, for those who may pick. }).

You then create a seeksJob record as follows:

INSERT INTO seeksJob VALUES('2','1','todaysDate');

Where '2' above is the code for programmer and '1' is my ID.

Now, if I also seek to be a web designer, you just use my existing ID (1) but job code 1, and create another record in seeksJob like so:

INSERT INTO seeksJob VALUES('1','1','todaysDate');

So a person can seek as many different job types as he likes - in the above, you can not have the person seeking the same job twice (makes logical sense really don't it, lol!) due to the primary key restrictions.

Now, lets say a manager wants to find all people looking for a job as a web designer. He just has to execute a query like this:

SELECT forename, surname, telephone FROM SeeksJob,Person WHERE SeeksJob.jobID='1';

You could even use more complex SQL to have it done by description of the job (see below), however I assume your system will deal with this side of things (how exactly things are searched for - ie, from a drop-down box, by entering manually the job type etc).

SELECT forename, surname, telephone FROM SeeksJob, Person WHERE SeeksJob.ID='(SELECT ID FROM JobType WHERE description='Web Designer' LIMIT 1)';

Without further info on what EXACTLY you are planning to do and how exactly you are planning to implement it, it's difficult to say much more however I hope this has been of some help! Feel free to ask anything you're not sure about.

-John-

[bmuk]

are you using phpmyadmin? you may find navicat may help you see things better.

good luck

[ParkerDigital]

Wow John, that's brilliant - thanks for being so thorough! That'll definitely be a big help.
bmuk - Yep, I'm using phpmyadmin. I haven't heard of navicat before, I'll check it out, thanks for the tip

[/dev/null]

No problems - like I said, anything else, just PM me (or post here).

Jk