What kind of different relationships do I have to think about?

Question

I'm sure that I missed something, and that I've got something plain wrong.

1:(0-1) (one to zero or one)

1:1 (one to one)

1:(0-n) (one to zero, one or many)

(0-m):(0-n) (zero, one or many to zero, one or many)

m:n (many to many)

m:1 (many to one)

m:(0-1) (many to zero or one, i.e. 10 people share one meeting room)

Constrained Relationships: I'm not sure about these (probably I just invented them, lol):

1:max (one to specified maximum quantity, i.e. your mail account may not have more than 1000 mails)

1:min (one to specified minimum quantity, i.e. a vehicle must have at least 1 wheel)

1:min-max (one to between specified minimum and maximum quantity)

x:y (x entities to y entities, i.e. always two teachers are responsible for five classes)

x:min, x:max, x:min-max

Answer 1

In term of an ER model, there are 1:1, 1:N and M:N. This is what you can put into the diagram.

The relationships are always defined by the entities related. In ER model, there are no such things as "double relationship", "expired relationship", "ternary relationship" etc.

A pair of entities are either related or not, and the relationship is always binary. This is how the ER model works.

Now, imagine that A and B are related. This puts two questions:

1. May A be related to something else?
   • May B be related to something else?

And here is the answer matrix:

1     2    Relationship
Yes   Yes  M:N
No    Yes  1:N
Yes   No   N:1 (same as 1:N)
No    No   1:1

This covers all possibilities.

In terms of business logic, there can be any constraints you can imagine (most of them fitting into one of the three relationship schemes above with some additional checking).

However, this cannot be covered by the ER model only.