Need help for my simple recruitment database (1 Viewer)

[jhoelesmejarda]

Hello everyone,

I would like to seek help from guru's here in the forum.

I am currently working on a Database design which is base from an excel file that I have been using, the file was just handed over to me.

So the purpose of this database is mainly,

The recruitment tracking database tracks manpower requirements of the project base on their required number of candidates needed for a specific job title per department. Store candidate information, request (salary or availability), status, progress on his deployment, arrival and exit date, store agency information. Can show numbers of candidates who proceed on their selection, back out from their selection, pass their medical, can proceed to visa application, visa issuance, ticketing and then arrival.

Things that I want my database to react on my data,

1. Store job post requirements by departments, how many candidates needed for that specific job title.
   
2. Store selected candidate information for the required job post.
   
3. Can update, track changes on candidates's information, progress, deployment up to being an hired by the company.
   
4. Can track how many candidates are selected on a specific job title in each department by date (total count and list of names).
   
5. Can track/count how many candidates should be selected base on the demand, number of candidates that was selected and the balance for each position. (selected - demand = balance, total count and list of names)
   
6. Can track how many selected candidates fail (e.g. back out from the selection, fail to pass the medical, other reasons. Total count and list of names)
   
7. Can track how many selected candidates proceed on their employment up to being employed in the project until they leave the project (total count and list of names)
   
8. Can track how many selected candidates pass their medical (total count and list of names)
   
9. Can track how many selected candidates can proceed in applying their visa (total count and list of names)
   
10. Can track how many selected candidates have their visa issued (total count and list of names)

I attached a picture of my (so-called) database table relationship created from excel.

I just want to know more, learn more from your comments and suggestions. Thanks in advance!

 

[plog]

Not bad, I think you went overboard on normalization, but that's better than jamming everything into one huge table.

By 'overnormalization' I mean tables that only have 1 real piece of data in them (tblCategory, tblSelectionType, tblGender, etc.). You're not really gaining anything by having 1 piece of data in a table. Is 1/2 much better for male/female than M/F? Might as well store the M/F in the tblCandidateSelectionProcess and get rid of tblGender. Same for all those others.

Without knowing your intended use of each table, can't really comment more than that. Here's a few questions though:

 

[jhoelesmejarda]

Thanks for the comment.

I will delete those tables and replace it with a look up field.

Anyway, what would be the few questions that you might want to ask?

 

[plog]

Is an agency contact to be tied to a candidate? Right now, only an agency is tied to a candidate, not a specific contact at that agency.

Would the same person be in tblCandidateSelectionProcess more than once. Like being submitted to different posts or for different agencies or with a different Visa?

Can an agency contact person belong to more than 1 agency? Your relationship allows that, just not sure if that's true.

Main concern is that tblCandidateSelectionProcess isn't broken out enough. Don't understand what it's ultimate purpose is, so can't really say or not.

 

[jhoelesmejarda]

Is an agency contact to be tied to a candidate? Right now, only an agency is tied to a candidate, not a specific contact at that agency.

- Yes there is no need to attached the contact person from that agency to the candidate since the purpose of the agency is just to track how many candidates have been selected in that recruitment agency

Would the same person be in tblCandidateSelectionProcess more than once. Like being submitted to different posts or for different agencies or with a different Visa?

- I already think about that before since this would be just a simple database, I prefer to have their history, say, candidate A's job title was change from job title A to B information goes to the tblCandidateStatusUpdate then just change the record of candidate A directly.

Can an agency contact person belong to more than 1 agency? Your relationship allows that, just not sure if that's true.

- No, because that contact person is working with that specific agency. can you lighten me up about the flaw. I am still new in creating databases.

Main concern is that tblCandidateSelectionProcess isn't broken out enough. Don't understand what it's ultimate purpose is, so can't really say or not.

- Do you mind giving me your thoughts about the table? do only purpose of that table is to store all of the personal information of the candidate, the processing of his deployment (medical, visa and ticketing), up to his employment (Arrival date (start date), QatarID, employeeID then up to his exit on the company.

I am very sorry I am not good in explaining things lol, but thanks for giving me your thoughts about my design.

 

[plog]

No, your explantions are good. I was just thinking of possible places it might break. You know your data better than I and sounds like its a good structure.

I just wondered if you should break out candidate info (First Name, Date of Birth, etc.) into a table of its own and then link to the CandidateID in the tblCandidateSelectionProcess table. Sounds like you have a handle on things.

 

[jhoelesmejarda]

In my first design, I separated the candidate info and also the progress of the candidate but the thing is the candidate will just have one progress in his deployment that is why I change it and include one table for their updates.

If I am going to break the candidate info from that table? do you mean by putting them into a one to one relationship?

Thank you very much!

 

[plog]

No, you shouldn't have a one to one relationship in your database.

 

[jhoelesmejarda]

Okay, I get your point.

So the point in separating the candidate info is to allow the candidate to apply for another post? am I correct?

 

[plog]

Yes, assuming that's possible.

 

[jhoelesmejarda]

Thank you for your advise. I will change the design and create the tables then test drive it then I'll post here if there are any issues.

Thanks again!

 

[jhoelesmejarda]

Hi plog or to anyone here that can check the attached photo,

this is the revised tables that I made already in access.

Can you please give me some advice to things that I need to change or to what would be the problem in the future. TIA

 

[spikepl]

A few things:

1. tblAgencyContact PersonDetails - do you relaly exepct mutiple emails/phones for the conact person? If not then all that info belongs in the tblAgencyContactPerson

2. Candidates supploied by agencies usually have some reference number, so that when speaking with an agency any ambiguities associated with plain names are removed. I do not see that information here.

 

[jhoelesmejarda]

1. tblAgencyContact PersonDetails - do you relaly exepct mutiple emails/phones for the conact person? If not then all that info belongs in the tblAgencyContactPerson

- Thank you for the info I will definitely remove that table.

2. Candidates supploied by agencies usually have some reference number, so that when speaking with an agency any ambiguities associated with plain names are removed. I do not see that information here.

- I did not include any reference number or control number since what I am receiving from the agency is plain resume of candidates no control number written from the agency.

 

[jhoelesmejarda]

Hello to everyone,

After trying the design that I posted before, I realized that it will not work on my needs, finally I made a new structure for me to again test but before I create a table and construct it in access I just wanted to ask for suggestions again in this design.

Please take a look on the attached photo as I have constructed it using excel.

I really need some help as my Excel file is getting bigger each day.

 

[jdraw]

Before looking at the structure, please tell us more about this
...... I realized that it will not work on my needs...

Did the requirement change?

 

[jhoelesmejarda]

I am very sorry for the dumb explanation on what I realized on the first structure.

On my First structure the problem is it doesn't give me the feature to update the department and the Job title as it is already included in the tblPost.

What I did in the new structure is that for the sake of creating a summary of the demand I made a Job title look up on the tblPost and another job title to the tblEmployees, the same goes with the Department and SubDepartment field for updates when they are already deployed and the management decided to change their job title or change their department and subdepartment as well.

Also from the first structure I made a tblSelectionProcess which seems redundant since the tblCandidate can handle all the fields on the tblSelectionProcess that is why I separated them and just link it with the tblCandidate as the fields is directly related with the tblCandidate.

Actually, I am not sure if my structure is correct or my explanation is understandable.

 

[jdraw]

Just typing while thinking: and these may not apply

When the Employee is hired, he is moved into a Position, and that Position has a JobTitle.
I think you would have tblDepartment and tblsubDepartment (I don't know if there are several or just 1 per department). The Position is with the organizational unit (in my view) and not the Employee.

eg Bob Gonzales occupies Position 123 in Department 200. The position name/title is Chief Technology Guru.

If you identify Departments and subdepartments and Positions by unique identifiers, you can record Name/Title changes accordingly. This would seem analogous to Product prices which can be changed as required. A means to record the EffectiveDate is required to show what is current.

eg:
tblDepartment
DeptId
DeptEffectivedateDate
DeptName
...

The DeptName associated with the most recent DeptEffectivedateDate is current.

I'm not sure how you know who failed on Medical etc???

Take some realistic test data, and run some test scenarios against your model. Be meticulous -- if anything doesn't quite work -- make sure you determine why.
Is it the model, is it poorly defined process/bad scenario, is it the data?? Reconcile this by changing whatever, and retest till it works. Then, proceed with Access.

Good luck.

 

[jhoelesmejarda]

Thank you for the suggestion.

I will rethink the structure before doing it in access.