Conceptual data modelling: How to read a recursive many to many relationship

Question

I understand that a simple binary relationship like the one below would read from left to right: a user "may" have one or more pictures. Also if you read it from right to left is would be... an image "must" belong to one and only one user.

However, where I get a little confused is when I see the following. Could anyone tell me how you read this type of relationship? Also, in the image following the one right below are they saying the same thing just differently?

and finally, in this recursive relationship where a user can be a friend of another user does it make sense that both ends are specified as an optional many, or should one be a must and many?

The way I see it is if a user can have zero or many friends than on the other end it should have one or many friends because if user A is friends with user B then user B no longer has an option to have zero friends. Is this assumption correct or am I wrong?

Any thoughts would help I am just reading through a book on conceptual data modelling and really want to understand this before I move on and practice on real tables.

Answer 1

There's a lot to unpack here. The answer from Jo Douglass covers a lot of the ground.

I believe that part of your confusion comes from the fact that people use ER diagrams to depict two very different kind of models. The first is entity-relationship models, better known as ER models. The second is relational models. On the surface, the two models look almost identical. But they have different features, and they are built for different purposes.

An ER model can facilitate communication between a database designer and a subject matter expert. A subject matter expert may have a deep understanding of the data: what it looks like, what it means, why it's important, and how it is to be used. That same subject matter expert may have little or no interest in technical topics like foreign keys, referential integrity, or data normalization.

A relational model is a good preliminary result for a designer intending to design and build a database in one of the popular SQL databases, such as SQL Server, Oracle, or dozens of others.

Your last diagram, with just the box labeled User, is very succinct and clear. It highlights the many-to-many nature of the relationship. It's perfectly valid in an ER model, but a relational modeler would tell you that it's not legitimate, and that a junction box is required.

It does, however lack a name for the relationship, namely Friendship. Naming a relationship is useful if you might have two relationships that are otherwise identical. It also provides a name for you to hang attributes on. In some case, you might be interested in the date on which a given friendship started.

Whether the relationship is mandatory or optional may depend on whether you are analyzing the subject matter, or designing the solution. If it's the first of these, you look to the subject matter to find out whether it's mandatory or not. Technical experts in here can't answer the question for you, because we don't know your subject matter, even when we think we do.

If you're designing the solution, you may want to look at it from a different point of view. Are you overconstraining the data? Are you underconstraining?

I hope this addresses some of what you are wrestling with. Database design isn't complicated. But it is abstract.

Answer 2

Yes, the two diagrams are showing the same thing.

Some people choose to leave many-to-many relationships unresolved in conceptual diagram. Some text describing the relationship might be helpful (I'd suggest something like "is friends with"). I'd then read this as something like "a User may be friends with other Users."

The second diagram shows what you'd draw if you instead decided to resolve the many-to-many relationship. Some people leave this until logical modelling, and you'll come across the same construct when you read about it (which I would recommend as the next step after learning about conceptual modelling). I would read the relationships between User and Friendship as something like "a User may have Friendships."

These relationships are always optional because you're modelling the big picture, not one specific instance. Showing it as non-optional on the right-hand side would be saying every User must have at least one entry in the second column of the Friendship table in the database you'd eventually make from this model - and that's not true.

By the way, I think it's really commendable that you're reading up on this (far too many people build databases without ever trying to grasp conceptual or logical modelling!). I wouldn't worry about waiting until you feel like you understand it fully before trying out what you're learning, though; some of the ideas may make more sense once you're putting them into practice on real data that you already know. Try sketching out conceptual diagrams based on your own data while you learn, if you haven't yet.