Datamodeling Trouble - Help me please!
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.