Staff - Evaluations - Competences - Training Sessions - Participation... HELP!

[Luis Francisco Rivero]

Ok so I'm developing a database to handle staff management for a Camp that works all year long.

So I'm trying to make the following system.

We create events training sessions in which staff member either participate or don't (I want to record each time a staff member participates) so this is a many to many relationship between Staff and training sessions mediated by a Participation Table (in which I can create a status field for confirmation, assisted, didn't show up, etc...) but I also want to record each time a staff member gets an evaluation (which is not necessarily during training sessions, but each training session does have a related evaluation). Every training session has some associated competences (or skills) associated to it, and each staff member should get evaluated in this competence list...most this is where it gets super tricky... I'm kind of stuck on how to handle all of this relationships and even more stuck on how to script things to create the proper participation records and competences records and... Ahhh...

I think I'm in over my head... This is the third database I've built and the first time I've attempted anything so complex (the idea is then to automate many calculations based in these evaluations...). I'm self taught with regards to FileMaker or DB management.

I'll attach a clone later on since right now I'm commuting to my work place... But any insights so far would be greatly appreciated.

[comment]

It doesn't sound like you are at the stage where you can attempt a Filemaker implementation yet. You need to have a solid ERD before that. Based on your description, it seems it should look something like this?

 

 

 

 

 

[Luis Francisco Rivero]

That looks good it's more or less what I had envisioned... The thing is evaluations can happen regardless of the staff members participation on a training session... But I can just create a new TO for that right? Also why would the relationship between participation and evaluations be one to many and I was thinking there should be a join table between evaluations and skills (since you evaluate many skills each time and skills are each evaluated many times aswell.

[comment]

The thing is evaluations can happen regardless of the staff members participation on a training session...

 

Could you elaborate on this, please? It doesn't seem to agree with what I understood from:

 

each training session does have a related evaluation

[Luis Francisco Rivero]

We evaluate staff member on any number of occasions, one of which is when they attend a training session. But also after they work on a given project or when we see a decrease in performance we call them in to have an interview, etc... So there can be evaluations that are not linked to any training session...

[comment]

Perhaps I am still misunderstanding, but it seems like you have three separate entities here, instead of just two. Something like:

 

Evaluations -< Competence >- Skills

 

where evaluation is an occasion on which multiple skills of a staff member are tested and assigned a competence level. And, if I still understand correctly, the list of skills to be tested (not necessarily tested in the actual evaluation) depends on the training sessions in which the staff member participated (and perhaps on other factors as well?).

[Luis Francisco Rivero]

I think we are getting there!

I'm gonna work a bit on the file before uploading it... I'm not very organized so I'm sure it will look very messy to any one who isn't familiarized with it...

I think you raise an important point there... My question still is: how do I pre-populate the competence and evaluation tables with records for every participant of the training session x every skill being measured...

Edit: let me rephrase that. Every time I add a skill to a training session a new record should be created in the competences table for every participant and that skill, likewise, every time a new participant is added to the training session new competence records should be added for every skill...

[comment]

Every time I add a skill to a training session a new record should be created in the competences table for every participant and that skill, likewise, every time a new participant is added to the training session new competence records should be added for every skill...

 

I am not sure that's necessary (or even desired). I believe you need some sort of mechanism that would allow you to see, at any point in time, what skills of a staff member ought to be tested IF an evaluation were to take place now. A record in the Competence table should be created only when a skill is actually tested and a result is obtained.

 

The point that remains unanswered is what skills need to be tested other than the skills that the staff member acquired though participation in various training sessions.

[Luis Francisco Rivero]

All of the skills corresponding to a particular training session have to be tested on every participant. What I want is for the default records to be created so then the trainers can input their measurement through a portal on a layout based on the evaluation table...

I think...

[comment]

All of the skills corresponding to a particular training session have to be tested on every participant.

 

But now you are going back to an evaluation being tied to a specific training session. I thought you said that's just one of the occasions when a staff member will be evaluated. I am still unclear as to how an evaluation will be initiated and what determines which skills will be tested.

 

As an example: you could divide the skills into categories (where each category may correspond to a training session). Then, anyone administering an evaluation would select the staff member to evaluate and the category of skills in which to evaluate, and fire away.

[Luis Francisco Rivero]

Sorry my bad, evaluations can get triggered by any number of things... Whenever a staff member participates in a training session, his participation will be evaluated in general but also in the specific skills that are related to that particular training session. But also a staff members performance in a project will also be evaluated as well as every once in a while they will get evaluations for extraordinary reasons (not related to a training session, a project or any other foreseeable instance in the database. Hence a separate table for evaluations and participations. Additionally the performance of a staff member in a training session or a project can be evaluated separately by more than one evaluator, thus creating two separate evaluation and competence record for the same staff member participation and training session...

[comment]

Based on what you said so far, I would amend the ERD to:

 

 

 

However, these two points remain unclear:

 

The point that remains unanswered is what skills need to be tested other than the skills that the staff member acquired though participation in various training sessions.

 

I am still unclear as to ...  what determines which skills will be tested.

[Luis Francisco Rivero]

The skills are determined arbitrarily during the time a training session gets programmed (usually prior to the selection of participants) only the skills relevant to the training session need to be tested... Evaluations that aren't related to a training session don't test skills and are of a more qualitative nature.

[Luis Francisco Rivero]

What do you think?

 

[comment]

I think that when you say "determined arbitrarily" and "a more qualitative nature", then that's too vague to suggest anything. In my limited experience, nothing is determined arbitrarily - certainly not in a database. Users need to make their selections from a restricted domain of choices; it is your job to present the available choices to them and allow them to make their selection/s.

 

Regarding the RG, it seems similar to my last ERD, except you changed the name of Skills  to Competencies and Competencies to Eval_x_Comp.

[Luis Francisco Rivero]

Yeah, it's mainly a translation issue... My main language is Spanish and I'm developing in Spanish...

What I meant is that the skillset will be different for most training sessions. For instance in a rope course training the skills will refer to applied knowledge and techniques whilst on a more general introductory course we will evaluate social skills, etc... With regards to evaluations what I mean is that in a particular evaluation the person in charge will rate the staff member from a scale of 1 to 5 but also record observations, recommendations, etc... All according to the criteria of the evaluator whiles the competences are basically yes or no records.

For instance, let's say that in a rope course training the skills to be evaluated are: tying an eight knot, recognizing tripos of rope, etc. the participant either posses that skill or he doesn't (I might set that up on a scale instead such as Not Acquired, In Process, Acquired...) but I'm also interested in recording a general rating from the evaluator and his observations regarding the staff member's participation during the session (for instance: he's inexperienced but shows great interest and promise with training. + a general appreciation mark of 4. That would allow me to compare staff member in a more open way without restricting everything to foreseeable measurements... I know that doesn't seem ideal, but that's kind of the way we like to work at my camp.)

[comment]

For instance, let's say that in a rope course training the skills to be evaluated are: tying an eight knot, recognizing tripos of rope, etc. the participant either posses that skill or he doesn't (I might set that up on a scale instead such as Not Acquired, In Process, Acquired...) but I'm also interested in recording a general rating from the evaluator and his observations regarding the staff member's participation during the session (for instance: he's inexperienced but shows great interest and promise with training. + a general appreciation mark of 4.

 

I think that in the example you describe, the evaluator would :

[a] ask to create a new evaluation;

select the staff member to evaluate;

[c] select "rope" as the skill-set for this evaluation.

 

Having done that, the evaluator would be presented with a layout where the different rope skills (tying an eight knot, recognizing tripos of rope, etc.) are shown in a portal, along with a field (or a checkbox) for the evaluator to fill. A general appreciation mark would be a field in the Evaluations table. Notes could be added either to the evaluation in general, or to individual skills (or both).  

 

Note that skill-sets could also include more general skills to be evaluated alongside the subject-specific ones.