2 Replies Latest reply on May 28, 2012 6:58 PM by cretool

    Help Please!  Complex Join Tables




      I need to create a database in which we can match the talents of job candidates to the position. See the attached for the sample database and schema.


      What I need to accomplish is to link Projects to Job Requirements and then how each Candidate scores on those Requirments.


      I've created a simple form that creates a project and then the Job Requirements of that Project. Of course I have a form for creating the Candidate records.


      What I'm stuck on is the form that brings them all together. Ideally I would be able to select a Project from a pull-down list. This would then populate the portal with the Job Requirements created on the other form and then allow me to select a Candidate that I could grade for each one of the Job Requirements. It would keep the Score for each Candidate, for each Job Requirement, for each Project. That's my ideal, but I'm a ways from that.



      All help is greatly appreciated.





      Job Candidates Schema.png


        • 1. Re: Help Please!  Complex Join Tables

          Well, this looks like fun! Here's an altered version of your database.


          I suggest adding dedicated JobRequirement and Projects_People (aka Candidates) tables, which gives you more flexibility (e.g. summing up all scores of a candidate for a job). Take the ProjectID_fk out of persons. What if a person applies for several jobs?


          Assign/remove People and JobReqs to/from Projects, while making sure that each Project_People combination has CandidateScore records corresponding to the JobReqs you have set up for the project. (Check my script for comments on that). This is a script-intensive method, because you need to do a lot of housecleaning when adding/removing JobReqs and Candidates, but allows for very clean and nice interfaces


          You can apply this generic method of display/selection/filter, as (partially) shown on the Projects layout, to all entities - Projects, JobReqs and People.

          • 2. Re: Help Please!  Complex Join Tables



            Wow.  Thanks.  It's going to take me a bit to digest it!


            Back to you as soon as I do.