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

    Help Please!  Complex Join Tables

    cretool

      Hi,

       

      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.

       

      Thanks,

      Don

       

      Job Candidates Schema.png

       


        • 1. Re: Help Please!  Complex Join Tables
          erolst

          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
            cretool

            Oliver,

             

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

             

            Back to you as soon as I do.

             

            Thanks!!!

            Don