I don’t know how to read many to many relationships

Posted on

Question :

I’m reading this tutorial, and I have question about it.

Look at this picture:

enter image description here

Looking at Physical Many-to-Many Implementation I read the relationship this way: ‘A user can be in zero or more projects’.

But I don’t know how to read it in the other way from ProjectEmployee to Employee. How do you read it?

And another question, Why the relationship is one and only one on Employee side?

Answer :

The many-to-many nature of the relationship is expressed in the Physical diagram by breaking the relationship down into a pair of many-to-one relationships. A many-to-one relationship can be implemented using a foreign key that references a primary key.

Project-Employee contains a foreign key (presumably EmployeeID) that references the primary key of the Employee table (presumably ID). That is why there is only one Employee allowed on that end on the upper line.

Note in passing that if you were to implement using some kind of non-relational DB, the diagram on the left would be more useful than the one on the right.

Also note in passing that, in the classical literature on DB design, the one on the left would have been called the “Conceptual Model” and the one on the right would have been called the “Logical Model”. The physical model would have included DBMS specific features like tablespaces, indexes, and clusters. Times change, and terminology changes in ways that can be bewildering to those who learned it the original way.

You don’t, really. The ProjectEmployee table is just there for technical reasons (it doesn’t work any other way), which is why it’s omitted in the logical diagram (implementation detail).

Same goes for the 1-1 relation on the Project and Employee tables. The actual many-many relation happens in the third table, so that’s the one with the “crow feet”.

Don’t think about it too hard. It’ll just mess with your head.

Leave a Reply

Your email address will not be published. Required fields are marked *