4 Replies Latest reply on Feb 13, 2015 6:51 AM by mdphillips




      Relationship & Normalization Help


      I'm trying to create a solution to track training for a group I'm with.  I need to track members, task books, task book evaluations and job action sheets. A member can be working on multiple TBs at any given time and each of the TBs will be evaluated multiple times. A member will also receive a job action sheet every time they deploy.  Based on table relationships it look like I'm on the right track and that I've normalized 1st, 2nd & 3rd forms?


        • 1. Re: Relationship & Normalization Help

          A member can be working on multiple TBs at any given time

          But can more than one member work on the same task book?

          If not, you don't need the join table between members and task books.

          And the same holds for the link between task books and evaluations. You've indicated that a task book can have multiple evaluations, but can the same evaluation be linked to more than one task book? Unless that is the case, the join table between evaluations and task books is unnecessary.

          And you seem to indicate that a member can have many job action sheets, but does will a single job action sheet ever need to be linked to more than one member?

          • 2. Re: Relationship & Normalization Help

            Those were the questions that were making my head spin because it could depend on how you look at it.  PTBs (Position Task Books) evaluate a member's ability to complete tasks/skills needed for a particular position. There are a finite number of positions, and thus, task books for our group.  While they may have the same task book name they are completed by the individual team member.  The same could be said for the evaluations.  Each evaluation record must relate to specific task book record that is related to an individual member.

            The JAS (Job Action Sheets) are a bit different. They are also an evaluation tool for positions but with different criteria and more performance oriented.  An individual member should have at least 1 JAS for each event but could have more if they performed multiple jobs during an event. 

            PTB and JAS usually go hand in hand, ie. if you are deployed (for the first time) to Event X in the "Ground Support" position, you would initiate the "Ground Support" PTB, receive your first "Ground Support" PTB Evaluation and receive a JAS for "Ground Support" on Event X.  However JAS are not always related to a PTB.  The JAS are relatively new so older members may PTB Evaluations for events but no associated JAS.  You may also have instances where a member is already certified in a position and simply receives the JAS as a performance review.

            So now after that long winded explanation...If each record in the Members, PTB & JAS tables have unique IDs they can be related to the individual member without the join table?  Would that also hold true for the Evaluations since each eval record would also have a unique ID that is related the PTB that is in turn related to the member?  

            Hope that all makes sense.  Thanks so much for your help and patience!


            • 3. Re: Relationship & Normalization Help

              Sounds like everything is one to many, not many to many:

              JAS>----Members----<PTB----<Evaluations    (---< means "one to many" )

              But that's just the "backbone". your discussion of how a JAS may or may not be associated with a PTB suggests that you might also need a second Tutorial: What are Table Occurrences? of PTB to link to JAS.

              • 4. Re: Relationship & Normalization Help

                As long as a JAS is associated with a specific member I think all should be right with the world.  So this should work? 



                Thanks Phil I really appreciate you helping me wrap my mind around it!