Forum Discussion

aashton's avatar
aashton
Helper V
2 years ago

Normalizing a many-to-many

I have an Applications table with ID, Date, Name, Candidate ID, etc.  It is linked to a Contracts table with ID, Date, Name Candidate ID, on Candidate ID, many-to-many.  I am trying to resolve this m...
Need Help
sevenhills's avatar
sevenhills
Super User
2 years ago

I think you are missing some info to make it happen.

 

   CandidateID  << 1..N << Applications
   CandidateID  << 1..N << Contracts
 
Let us say, CandidateID is the master data table i.e., no duplicates on Candidate and these two tables seems to like two transaction tables: Applications, Contracts.
 
But where is the info that says a particular "Contract" is part of an "Application" for a given Candidate? do you have that info?
 
Say, In these scenarios, there is no contract and application combinations data.
Cand100 has App101, App102.
Cand101 has no app data.
Cand103 has App101, App102.
Cand102 has App201 data.
 
And,
Cand100 has Contract100.
Cand101 has Contract101
Cand103 has Contract100 ... Same contract
 
 
If you have the data that a contract and application are related then yes, you can do that easily as
           CandidateID, ContractID, ApplicationID
  • aashton's avatar
    aashton
    Helper V
    2 years ago

    sevenhills  Applications links to Contracts on Candidate ID.  So in your example, Candidate 100 and Candidate 103 can't both be linked to Contract 100.