PHP —

Identifying relationships in ER modeling

Before identifying the relationships between the entities we have identified, we noted earlier in this section that:

Lines connect entities to relationships. Lines may be without any annotation, be annotated with an M and an N, or annotated with an M and a 1 (or an N and a 1). Annotations indicate the cardinality of the relationship.

Cardinality refers to the three possible relationships between two entities[A] and, before you can consider how the entities are related, you need to explore the possible kinds of relationship:

[A] Actually, relationships can exist between as many entities as there are in the model. Also, we have deliberately omitted the distinction with relationships that are optional, that is, where one instance of an entity-such as a customer-can exist without a related entity-such as an order.
One-to-one

A one-to-one relationship is represented by a line without any annotations that joins two entities. One-to-one means that for the two entities connected by the line, there is exactly one instance of the first entity for each one instance of the second entity. An example might be customers and user details: each customer has exactly one username and password, and that particular username and password is only for that customer.

One-to-many (or many-to-one)

A one-to-many relationship is represented by a line annotated with a 1 and an M (or a 1 and an N). One-to-many means that for the two entities connected by the line, there are one or more instances of the second entity for each one instance of the first entity. From the perspective of the second entity, any instance of the second entity is related to only one instance of the first entity. An example is wineries and wines: each winery sells many wines, but each wine is made by exactly one winery. Many-to-one relationships are the most common relationships between entities.

Many-to-many

A many-to-many relationship is represented by a line annotated with an M and an N. Many-to-many means that for the two entities connected by the line, each instance of the first entity is related to one or more instances of the second entity and, from the other perspective, each instance of the second entity is related to one or more instances of the first entity. An example is the relationship between wineries and delivery firms: a winery may use many delivery firms to freight wine to customers, while a delivery firm may work for many different wineries.

It isn't surprising that many database modelers make mistakes with cardinalities. Determining the cardinalities of the relationships between the entities is the most difficult skill in ER modeling, but one that, when performed correctly, results in a well-designed database. To illustrate how cardinality is determined, let's consider the relationships between the entities in the winestore and present arguments for their cardinalities.

Correctly assigning cardinalities is essential. Mistakes in cardinalities of relationships lead to duplicated data, inconsistencies, and redundancy in the database. All lead to poor performance and a hard-to-maintain database.