Setting up Database - need help with relationships between tables

[wilkob]

Hi,

I would like to create a small HR database that holds:

* Employee details (Name, Date of birth, Entry Date, Exit Date, Function,Department, Shift)
* Departments
* Departments and capabilities needed
* Employee, departments and capability achieved (1=yes, 0=no)

The idea would be to have a link between the employee the departments capabilities I can tick fields of the capabilities they have or have achieved.

When I have this I could run a report that shows a score per employee
For example:

Name: Department: Orderpicking: Packing: Cutting:

John Warehouse 1 1 0

As the tasks / capabilities required are different per department you would see something different if you would have user Jane that works in accounting


Is there anyone that can give me some hints so that I can get any further??

For the moment I have 3 tables:

1. Employee information - John, Jane
2. Departments - Warehouse, Accounting
3. Department tasks - Department, Cap1, Cap2, Cap3, Cap4

 

[burrcm]

You seem to be on the right track. The join would be employee_id (a number which never changes). All employees would be referenced in all tables by this number. Name and details appear only in the Employee details table. That way if a name or address changes, there is only one dataset to alter. Tables can then be added as required to store additional skills etc.

Chris B

 

[wilkob]

OK, let me add some info to make it a bit clearer.

1.) A employee is assigned to a single department
2.) Each dept has standard tasks that, ideally, and employee should all learn to do
3.) For each employee we want to keep track of how multi-functional they are

I have added a spreadsheet that should hopefully visualize what I have now and what I would like to end up with: A table of employees where if you click the + it would show all the tasks of the department the person is working on and fields ticked (or 1 for yes and 0 for no) of task the person is able to do.

Can someone help me along a bit with the tables set up the relationships required

 

[The_Doc_Man]

You have an issue of data duplication that will cause you headaches.

Look at this as food for thought:

1. Table tEmployee
Prime key (PK): EmplID, autonumber unless your company has another scheme for this.
Data about person as of now.
DeptID - pointer to currently assigned department

2. Table tDepartment
PK: DeptID, autonumber unless your company has another scheme for this.
Data about department.
SupvID - pointer to employee table to show who is the department supervisor

OK, here is where I have to avoid duplication.

3. Table tSkills
PK: SkillID, autonumber - unless there is some other source for this code
Description of skill
Technical qualifications of skill, etc etc.

Here is the critical part. (And look up JUNCTION tables if you don't know about them by now. You're about to use them.)

4. Table tDeptQual
FOREIGN KEY (FK): DeptID
FK: SkillID

which is different from

5. Table tEmplSkill
FK: EmplID
FK: SkillID
Info on when learned, when certified, who taught, etc.

You need TWO skill tables to assure that you keep things separate that need to be separate, but yet have a way to relate them.

Where you potentially run into trouble is this: A department requires skills. You have desciptions of those skills. And employee has skills. You want to be able to correlate them, so they must relate to the same table - tSkills. BUT the department's requirements do not bear identically the same relationship to skills as the employee's having learned those skills. For instance, a department's skills represent a list of "learn this or change jobs" (or earn less money...) whereas an employee's skills represent a list of things that (technically) have nothing to do with a department other than the department requires some of the skills. And therein likes the crucial difference that forces the split. The department wants ALL of its associated skills. The employee qualifies for employment in that department with SOME of his/her skills.

This structure is expandable once you decide you want to keep employee historical data, but at the moment it is limited to the here and now.

If you have not read up on database normalization, do so now. Access Help will give you some hints. Wikipedia (.org) will give you more. A Google search for "Database Normalization" will give you so much that you will need to filter it. I would limit my Google search articles to the first few responses from the .EDU domain and maybe a couple of database solution vendors from the .COM domain. ORACLE, of course. INFORMIX, FOCUS, DB-IV, Sybase (if they are still around), and a few others might help.

Using the junction tables against the same skill tables is a way to represent that the same skills are being discussed from the department side of the house and from the employee side of the house. That means that the FK to SkillID in junction table is an apples to apples comparison. But the nature of the relationship from the two directions IS different in its logic.

When you've done some reading and thought about the above, come back with more questions. One or the other of us will toss in some more ideas.

 

[wilkob]

Doc Man,

I have followed your advised and set up above mentioned tables (see attached)

The only thing that strikes me as odd is that you would expect that if I have set up certain standard tasks for a department and if the department is set up in the employee table, that when I click the + in front of the employees name in the table tbEmployee the default set of tasks would appear.

This would be ideal and then you just have to tick / or grade the fields that have been achieved

 

[The_Doc_Man]

The catch is that if you have a list of department requirements, the first time you tick off the list, where do you store the ticks? In the skills list? Can't be right because that is independent of personnel. In the department list? Can't be right because that applies to more than one person. This has to be a person's list, but due to normalization cannot be in the person's primary employee record. What do you do if they change departments? So the structure I defined tracks department requires of skills and employee achievement of skill training separately.

Having said that, there is nothing to stop you from building a form that does exactly what you want. Don't confuse the form with the underlying data.

Read over the above and see if you understand the oddity. Then we can discuss mechanisms of updating things the way you describe.

 

[wilkob]

I see what you mean. The only thing I am afraid of that the HR employees will forget to assign one of the warehouse of accounting task to a person and as I would like to get some kind of reporting per department which shows the tasks and the score for each individual on that department I would rather work with a fixed set op tasks.

Now you say this can be done via a form. I don't mind working with a form. I could add a button to the form of the employee info and a pop-up would appear where based on the department the user is assigned to, the standard tasks would appear. The tasks descriptions and values however need to be stored against the user.
Any idea how I could achieve this?

 

[Rabbie]

Remember you should use tables for storing your data in an organised way but it is often easier to use queries to extract linked data for your forms and reports.

You should be able to create a "unmatched Query" using the Wizard to produce areport to show unallocated tasks.