5 Replies Latest reply on Jan 6, 2013 7:49 AM by philmodjunk

    Datamodeling Trouble - Help me please!

    EfronHirsch

      Title

      Datamodeling Trouble - Help me please!

      Post

           I'm still in the datamodeling phase of creating a DB, and I've hit a conundrum.

           I'm creating a recruitment database.

           And basically my model looks like this:

            

           Consultant 1- 11 Candidates 11 - 11 Positions 11 - 1 Company
            
           So a candidate can apply for many positions, and a position can have many candidates.
           I realize that I need a new table in between there to solve this problem.
            
           So I was thinking to call the new table "Pipeline Report"
           Each position can have only one pipeline report related to it, so it's like a one to one relationship.
           However, the same problem again:
           Many different candidate records could be in many different pipeline reports,
           and many pipeline reports can hold many candidates, but not one candidate record twice.
           Basically the pipeline report should show a list of all the candidate records that have applied for the position and also what stage each of them are at in the interview process (resume submitted, 1st interview, 2nd interview, etc.)
           However, since a candidate can apply to multiple positions with the same or different companies, then, hmmm.
           I've thought about it a long time, how to do this, and now I'm so confused.
            
           I need help so I came here.
            
           Any ideas?
            
            
            

        • 1. Re: Datamodeling Trouble - Help me please!
          EfronHirsch

               I was also thinking that I could have a candidate pipeline report and a position pipeline report.

               a candidate pipeline report would show each position that a candidate applied for and what stage they are at, and a position pipeline report would show a list of each candidate that applied for the position and what stage they are at. So basically this creates 2 more tables in between candidates and positions to solve the many to many relationship problem, creating 2 one to many relationships between it.

               Would this work?

          • 2. Re: Datamodeling Trouble - Help me please!
            philmodjunk

                 What you need is called a "join" table:

                 Candidates----<Candidate_Position>-------Positions

                 Candidate::__pkCandidateID = Candidate_Position::_fkCandidateID
                 Positions::__pkPositionID = Candidate_Position::_fkPositionID

                 See this link: Common Forum Relationship and Field Notations Explained if this notation is not familiar to you.

                 With these relationships, a portal to Candidate_Position on a Candidates layout will list all positions for which they applied. A portal to the same join table on the Positions layout will list all candidates that have applied for it.

                 You can base report layouts on this join table and include fields from Candidates and Positions as part of the report to list positions and who has applied from them.

                 Here's a many to many demo file you may find useful: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

            • 3. Re: Datamodeling Trouble - Help me please!
              EfronHirsch

                   I would call this Candidate_Positions join table "Pipeline".

                   So let me confirm, what you're saying is that both Candidates and Positions would be the parents for the Pipeline join table.

                   So it would look like this:

                   Candidate | - || Pipeline || - | Positions

                   So there is a one to many relationship from Candidate to Pipeline,

                   and there is also a one to many relationship from Positions to Pipeline.

                   And you're also saying that on the Candidate form it would be able to, display every position a candidate has applied to given that there is a portal to Pipeline,

                   and on the Position form it would show every candidate who has applied to that position, given that there is a Portal to Pipeline.

                   Is that right?

              • 4. Re: Datamodeling Trouble - Help me please!
                EfronHirsch

                     Sorry for the newbie question.

                • 5. Re: Datamodeling Trouble - Help me please!
                  philmodjunk

                       Yes, what you have posted is correct. Please investigate the links and demo fields for more detail on what I have suggested.