Newbie Relationship question

[MDPhillips]

I haven't had experience with complex relational databases most have been simple & didn't require much planning so I could really use some help. I'm trying to create a db to track training for a group.  The project needs, goals or whatever they are called are as follows:

 

The group has many members that need to be tracked for position certification

The group has 19 different positions available

Each member can become certified for more than 1 position

Each position has a task book and a Job Action Sheet (JAS) associated with it

Each member will have their task book(s) evaluated several times before becoming certified

Each member also receives 1 JAS for each position(s) every time they perform it

 

I've included a picture of the tables I think I need but am getting stuck at how to relate them correctly.  I added fields for every pk/fk combo I could think of even though I might not need them (newbie overkill) so don't laugh.  I also included a picture of what I would like to use as the main data entry layout.  Members could be added and then the related data could be added/seen via the portal.  Any insight would be greatly appreciated.

 

 

https://www.dropbox.com/s/z4f7kvsbyl5jvjy/Layout.JPG?dl=0

 

https://www.dropbox.com/s/m0bkbuudnqet3wd/Tables.JPG?dl=0

[comment]

You should be working with an ERD at this point, not the RG in Filemaker. And you should tighten up those descriptions so that it's absolutely clear how many of each object are there for each object related to it.

 

Right now, I can't figure out what is the difference between a certification, an evaluation, a task book and a JAS. They all seem to be the same entity, linking Members to Positions. With a certification being no more than just an evaluation that the member has passed successfully.

[MDPhillips]

My apologies - being a newbie I'm struggling a bit with some of the concepts and nomenclature.  Here's my thought process- I don't know if this is any better but here goes. 

 

There are 19 different job positions in our group and a member can seek certification in one or more position.  Both are entities and the are many to many relationships because 1 member can have more than one position and more than one member can have then same position.

 

Since both both the task books and the JAS are based on a position (common denominator) but they are 2 different evaluations. Shouldn't they be considered different entities according to 3nf (i think that's the one)? This is why

 

Each member will be given a task book (one book per position) - the task book lists the skills a member should be proficient in for that position.  So task books would also be an entity. This would also be a many to many relationship to members in that 1 member may be working on more than 1 task book and more than one member may be working on the a task book for the same position.

 

The skills listed in the task book will be evaluated several times - one reason is to show proficiency the other is that not every skill will be needed/performed on every event we cover.  Small events require fewer of the skills so it may take a member longer to be proficient if they only work small events. This relationship is the one I think I have the most trouble with. I think this is also a many to many because a member can have more than 1 task book with more than one evaluation.

 

The JAS is more of more of a self evaluation of your performance of the position on a given event and is reviewed by your supervisor.  Although there a member will only receive 1 JAS per position per event, it is possible for a member to have multiple JAS per event because they worked multiple positions on that event.  It's also likely that many members will work in the same position during an event and each have their own JAS.  An example would be that 3 members were assigned to the ground support position on the same event.  So wouldn't those scenarios also represent a many to many relationship also?

 

I think my biggest downfall is being a newbie and not fully understanding the different ways to join tables and the use of global tables - not sure if I have the right nomenclature there.

[comment]

I think my biggest downfall is being a newbie and not fully understanding the different ways to join tables

 

It's not so much about how to join tables, but more about how the real-life objects relate to each other. And there aren't that many ways they can do that: basically, it's either one-to-many or many-to-many. If you find yourself considering a one-to-one, then most likely you are dealing with a single entity.

 

My problem here is that I don't fully understand the objects that you describe. I think it's quite clear that you have a many-to-many relationship here:

 

a member can seek certification in one or more position.

 

in the form of:

 

member -< seeks certification >-  position

 

But what exactly is "seeks certification"?  I thought the entity you called Evaluation was that thing - but in order to (hopefully) reduce confusion, let me use the name Applications instead.

 

So thus far we have:

 

Members -< Applications >- Positions

 

Each member will be given a task book (one book per position) - the task book lists the skills a member should be proficient in for that position.  So task books would also be an entity.

 

No, I don't think so. As you say, there is exactly one book per position - so to me a position and a task book are one and the same entity.  However, when you say that "the task book lists the skills a member should be proficient in", you are clearly describing a one-to-many relationship between Positions and Skills.

 

Moreover, when you say that:

 

The skills listed in the task book will be evaluated several times

 

you are describing a many-to-many relationship between Skills and Applications. Which would lead us to something like:

 

 

I am not sure where your evaluations fit in this; it could be that Evaluations = Scores, or that an evaluation is a group of scores obtained at a single occasion, or even that Evaluations = Applications.