Skip to main content

The Problem with Many-to-Many Relationships

Many-to-Many relationships in an entity relationship diagram tend to conceal areas of poor understanding, and in fact some databases will not allow you to create such relationships. Almost always, a Many-to-Many relationship conceals a hidden entity. For this reason, identifying and adding the hidden entity to the model eliminates Many-to-Many relationships. This usually ends up with an extra entity being added, which then has a One-to-Many relationship with the existing entity.

Data modelling is all about identifying entities and their relationships, and then drawing a diagram that accurately depicts the system. This applies equally to the design of a new system or the analysis of an existing one. The end result of data modelling should be a clear picture of how information is stored and related within a proposed, or existing, system. Some examples of information systems and their entities are listed below:

Banking system: customer, account, loan

Airline system: aircraft, passenger, flight, airport

A box containing the name of that entity represents an entity. A precise definition of 'entity' is not really possible, as entities vary in nature. For example, in the airline system, whilst an aircraft is a physical object (as entities often are) a flight is an event and an airport is a location. However, entities are nearly always the things about which data will be stored in the system under investigation. Note that entities are always named in the singular; for example: customer, account, and loan, and not customers, accounts and loans.

This course uses symbols that are standard in the IT industry. We use the soft-box symbol shown to represent an entity. If an Internet site uses a different symbol set this is not a problem, as data modelling techniques are the same regardless of the symbols being used.

Similar entity occurrences are grouped together and collectively termed an entity type. It is entity types that are identified and drawn on the data model. An 'entity occurrence' identifies a specific resource, event, location, notion or (more typically) physical object. In this course, the term 'entity' refers to entity type. The term 'entity occurrence' will be specifically used where relevant.

Each entity has a data group associated with it. The elements of the data group are referred to as the 'attributes' of the entity. The distinction between what is an attribute of an entity and what is an entity in its own right is often unclear. This is illustrated shortly.

Next: Identifying and Naming Entities