Mysql – How to represent a one-to-many relationship with three tables

database-designerdmysql-workbenchtable

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

?

Best Answer

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.

Related Question