1

I have the following entities in my system: COMPANY, POSITION, APPLICANTS. There is many to many relationships between POSITION and APPLICANTS, but the current model does not show that there is many to many relationships between COMPANY and APPLICANTS.

Does it make sense to you to have a join table foo that has the company_id, position_id and applicant_id or i should have a table that joins COMPANY and POSITION and another that joins POSITION and APPLICANTS?

Teodor Talov
  • 1,933
  • 2
  • 25
  • 39
  • doesn't POSITION reference COMPANY? Because if so you'd need only join table for POSITION and APPLICANTS and get the relationship between APPLICANTS and COMPANY via POSITION. – ma cılay Mar 25 '12 at 21:30
  • What's the relation between COMPANY and POSITION? – jpm Mar 25 '12 at 21:31
  • @user306848 it does, but if i want to find how many applicants a company has, i have to do 3 way join – Teodor Talov Mar 25 '12 at 21:31
  • @TeodorTalov I see, but you should take the answer from duffymo into consideration. – ma cılay Mar 25 '12 at 21:34
  • @TeodorTalov Can't you hide the 3 way join behind a view? Or is it a performance issue (although the right indices might help)? – ma cılay Mar 25 '12 at 21:44
  • I wouldn't design based on imagined performance issues. See Knuth quote. Do your best to model the problem as it is and then get some data. Switch it up when the data tells you that you should. – duffymo Mar 26 '12 at 00:07

1 Answers1

2

I don't think so. You can get APPLICANT for a given COMPANY via JOIN with POSITION.

I think a relationship between a COMPANY and an individual becomes significant when they shift from APPLICANT to EMPLOYEE. I would not model it as you propose.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • In addition to this answer, if the FK from your many-to-many table to `Position` is not merely `position_id` but a compound `FOREIGN KEY (position_id, company_id) REFERNCES Position(position_id, company_id)`, then you could join `Applicants` and `Company` tables directly through the many-to-many table - without using `Position`. Compound Primary Keys are not easy to handle but can help avoiding some Joins. – ypercubeᵀᴹ Mar 25 '12 at 22:41