I just started using MySQL Workbench drawing an entity-relationship diagram that I previously designed in paper.

I want to represent a conceptual one-to-many relationship by means of three tables. For example, I have entity A and entity B with 1-to-N (for every A I have many Bs).

How can I do that with three tables, one of them being a "middle" table?

Like in the diagram that follows

enter image description here

or like in the diagram below

enter image description here


1 Answer 1


When representing one-to-many (1:n) relationships no "intermediate" table is neccessary. You can simply model that with two tables, one for the 1 cardinality and the other for the n cardinality. The outcome is that one row in the referenced table can be referenced by many rows in the referencing table.

You need an "intermediate" table for representations of many-to-many (n:m) relationships only, because you cannot directly model the situation as described above in both directions.

  • Ok thanks but if i want to do it with three tablets which is the right photo ? I want to do it with three tables cause in the middle i will add one attribute than none of the other two tables have. Apr 3, 2015 at 12:49
  • If you insert a 3rd table, regardless how you name it, you essentially have no 1:n relationship anymore between A + B. So at the end you again design a 1:n relationship (say between A + middle) and then add one between one of the participating tables against a third one (B). It's the same process. So, both your approaches are ok. You have to define a 1:n and a 1:1 relationship ultimately. It's up to what you prefer if you do that with A or B (and the 3rd table). Apr 3, 2015 at 13:00
  • I am still i little bit confused about where i have to put 1:1 , 1-N relationship at middle table. If i want to say that "for every A i have many B, for every B i have only one A ", which is the proper er diagram from above the first one or the second one ? Apr 3, 2015 at 13:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.