Choosing the Right Relationships

Choosing the Right Relationships

Entity sets can be joined in different ways by relationships. However, adding to our design every possible relationship is not often a good idea. First, it can lead to  redundancy, where the connected pairs or sets of entities for one relationship can be deduced from one or more other relationships. Second, the resulting database could need much more space to store redundant elements, and modifying the database could become too complicated, because one change in the data could need many changes to the stored relationships. The problems are basically the same as those discussed in "Avoiding Redundancy", although the cause of the problem is different from the problems we discussed there.

We shall demonstrate the problem and what to do about it with two examples. In the first example, some relationships could represent the same information; in the  second, one relationship could be deduced from several others.

Example : Let us review "Attributes on Relationships" figure, where we linked movies, stars, and studios with a three-way relationship Contracts. We omitted from that figure the two binary relationships Stars-in and Owns from "Entity-Relationship Diagrams" figure. Do we also need these relationships, between Movies and Stars, and between Movies and Studios, respectively? The answer is: "we don't know; it depends on our assumptions regarding the three relationships in question".

It might be possible to deduce the relationship Stars-in from Contracts. If a star can appear in a movie only if there is a contract involving that star, that movie, and the owning studio for the movie, then there truly is no need for relationship Stars-in. We could  work out all the star-movie pairs by looking at the star-movie-studio triples in the relationship set for Contracts and taking only the star and movie components. However, if a star can work on a movie without there being a contract - or what is more likely, without there being a contract that we know about in our database - then there could be star-movie pairs in Stars-in that are not part of star-movie-studio triples in Contracts. In that case, we have to to retain the Stars-in relationship.

A similar observation applies to relationship Owns. If for every movie, there is at least one contract involving that movie, its owning studio, and some star for that movie, then we can dispense with Owns. However, if there is the possibility that a studio owns a movie, yet has no stars under contract for that movie, or no such contract is known to our database, then we must keep Owns.

In summary, we cannot tell you whether a given relationship will be redundant. You must discover from those who wish the database created what to expect. Only then can you make a rational decision about whether or not to include relationships such as Stars-in or Owns.

Example : Now, consider "Entity-Relationship Diagrams" figure, again. In this diagram, there is no relationship between stars and studios. Yet we can use the two relationships Stars-in and Owns to build a connection by the process of composing those two relationships. That is, a star is linked to some movies by Stars-in, and those movies are linked to studios by Owns. In this way, we could say that a star is linked to the studios that own movies in which the star has appeared.

Would it make sense to have a relationship Works-for, as recommended in the following figure, between Stars and Studios too? Again, we cannot tell without knowing more. First, what would the meaning of this relationship be? If it is to mean "the star appeared in at least one movie of this studio", then perhaps there is no good reason to include it in the diagram. We could deduce this information from Stars-in and Owns instead.

Though, it is imaginable that we have other information about stars working for studios that is not involved by the connection through a movie. In that case, a relationship connecting stars directly to studios might be useful and would not be redundant. Alternatively, we might use a relationship between stars and studios to mean something totally different. For instance, it might represent the fact that the star is under contract to the studio, in a manner unrelated to any movie. As we recommended in "Roles in Relationships" example, it is possible for a star to be under contract to one studio and yet work on a movie owned by another studio. In this case, the  information found in the new Works-for relation would be independent of the Stars-in and Owns relationships, and would surely be nonredundant.

Adding a relationship between Stars and Studios