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?
2. 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.

Answer 2

As far as the database itself is concerned (as this question is on database design), there are really only two types of relationship: 1:1 and 1:0..1. A foreign key is either nullable or not nullable.

If you count the inverse relationship then there is also one-to-many (1:N).

Of course, you can implement different kinds of relationships with constraints, but the possibilities are almost infinite; to know which ones you have to "think about", you'd have to know the domain model.

Update: I thought maybe it would be useful to explain how these match up to the other, more UML-esque relationships you listed. In this list, 1:N means that the RS entity has a nullable or non-nullable foreign key/association with the LS entity:

(0-m):(0-n) - AKA M:N, this is just a combination of 1:N and 1:M with the latter inverted, i.e. (1:N)(M:1)
m:n - Same as above with check: M >= 1 and N >= 1 (very rare, difficult to implement)
m:1 - Same as 1:N, non-nullable
m:(0-1) - Same as 1:N, nullable
1:max - Same as 1:N with check: N <= max, non-nullable
1:min - Same as 1:N with check: N >= min, non-nullable
1:min-max - Same as 1:N with check: N >= min and N <= max, non-nullable
x:y Same as (1:N)(M:1) with check: N = x and M = y, assuming all N are related to all M (otherwise this is not a valid relationship)

And so on - my point being that databases deal only with the "fundamental" relationships and that any other type of relationship is just a combination of these relationships plus constraints.

Once you get the hang of it, it actually becomes much easier than fussing over multiplicities. Just figure out what's related to what, and in which direction, and implement specific rules later as need be.

Answer 3

From the point of view of relationships, Object Role Modeling provides a rich set of concepts and tools to help. You'll find there are dozens, including all you've listed and more. The tools aee more powerful than you might suspect, including the ability to generate database schemae with rules, triggers, etc. to enforce your constraints.

Here's an illustration from the Wikipedia article.

If you want to explore this in some depth, here's a link to get you started from the aspect of your question.