Relationships Between Entities

In any data model, it is rarely enough to identify the things — the entities — that a system needs to track. Equally important is understanding how those things relate to one another. A database that stores customers but cannot connect them to orders, or one that records employees but cannot express who manages whom, is of very limited practical use. Relationships are the structural glue of a data model: they capture real-world interactions, dependencies, and associations between entities, allowing data to be queried, combined, and reasoned about in meaningful ways. Understanding how to identify, classify, and represent relationships is therefore one of the most fundamental skills in database design.

What Is a Relationship?

A relationship in data modeling represents a real-world interaction, dependency, or association between two or more entities. Where an entity represents a thing — a person, place, object, concept, or event — a relationship represents a fact about things. The statement "a customer places an order" captures a relationship; so does "an employee works in a department," or "a student enrolls in a course."

Relationships are almost always named using a verb or verb phrase. The verb describes the nature of the association from the perspective of one entity acting upon or being associated with another. Good relationship names are active and specific: places, manages, supplies, belongs to. Vague names like has or is related to should be avoided when a more precise verb is available, because clarity in the model leads directly to clarity in the resulting schema.

In Entity-Relationship (ER) diagrams following the classic Chen notation (introduced by Peter Chen in 1976), relationships are drawn as diamond shapes. Lines connect the diamond to each of the participating entity rectangles, and labels along those lines describe the cardinality — for example, 1 or N. This visual convention makes it immediately obvious which entities are involved and what the nature of their connection is.

A relationship involving exactly two entity types is called binary, and it is by far the most common. A relationship involving three entity types simultaneously is called ternary. Although ternary and higher-degree relationships are less frequent, they arise whenever an association cannot be accurately decomposed into a collection of binary relationships without losing essential information. Consider the example of a SUPPLIER providing a PART to a PROJECT: removing any one of those three participants from the relationship changes its meaning entirely, so a ternary relationship is required.

Cardinality of Relationships

Cardinality defines the numerical nature of an association: specifically, how many instances of one entity can be associated with how many instances of another. It is one of the most consequential design decisions a database designer makes, because cardinality directly determines how tables, columns, and foreign keys will be structured in the eventual relational schema.

There are three fundamental cardinality types:

• One-to-One (1:1): Each instance of Entity A is associated with at most one instance of Entity B, and each instance of Entity B is associated with at most one instance of Entity A. True 1:1 relationships are relatively rare in practice. A common example is a PERSON and their PASSPORT — a person holds at most one passport, and a passport belongs to exactly one person. In a relational schema, a 1:1 relationship is typically implemented by placing a foreign key in one of the two tables, or by merging the two entities into a single table if they always co-exist.
• One-to-Many (1:N): One instance of Entity A can be associated with many instances of Entity B, but each instance of Entity B is associated with at most one instance of Entity A. This is the most prevalent cardinality in relational databases. A classic example is CUSTOMER and ORDER: one customer can place many orders, but each order belongs to exactly one customer. In a relational schema, 1:N relationships are implemented by placing a foreign key in the table on the "many" side (in this case, a customer_id column in the ORDER table).
• Many-to-Many (M:N): Multiple instances of Entity A can be associated with multiple instances of Entity B, and vice versa. For example, a STUDENT can enroll in many COURSES, and a COURSE can have many students enrolled. M:N relationships cannot be directly represented with a single foreign key; they require a separate junction table (also called a bridge table, associative table, or linking table) that holds foreign keys referencing both participating entities. This junction table may also carry attributes of the relationship itself — for instance, an enrollment date or a grade.

The following table summarizes how cardinality maps to relational implementation:

Misidentifying cardinality is a common and costly mistake. Treating a genuinely M:N relationship as 1:N, for example, will force data to be duplicated or lost. Always validate cardinality against real-world business rules: ask "can one A relate to many Bs?" and "can one B relate to many As?" and let the answers drive the model.

Participation Constraints

Cardinality tells us the maximum number of relationship instances an entity can participate in. Participation constraints tell us the minimum. Together they fully describe the range of participation for each entity in a relationship.

Total participation (also called mandatory participation) means that every instance of an entity must participate in at least one instance of the relationship. No entity instance of that type is allowed to exist without being related to some instance of the other entity. In Chen notation, total participation is indicated by a double line between the entity rectangle and the relationship diamond.

Partial participation (also called optional participation) means that some instances of an entity may exist without participating in any instance of the relationship. They are permitted but not required to have an association. In Chen notation, partial participation is shown as a single line.

Consider a concrete example using CUSTOMER and ORDER linked by the relationship places:

• ORDER has total participation in places: every order must have been placed by some customer. An order that belongs to no customer is meaningless and should not exist. This translates to a NOT NULL constraint on the foreign key customer_id in the ORDER table.
• CUSTOMER has partial participation in places: a customer may exist in the system — perhaps as a registered account — without having placed any orders yet. This means the foreign key reference from ORDER to CUSTOMER is required, but no corresponding requirement forces a customer row to appear in any order row.

Participation constraints have direct implications for database integrity rules. Total participation typically maps to a NOT NULL foreign key constraint, or occasionally to a trigger or check constraint. Partial participation means the foreign key is allowed to be null or simply that no mandatory back-reference exists.

When combined with cardinality, participation constraints give rise to what is sometimes written as a (min, max) notation. For instance, (1, N) on the ORDER side of places means an order must participate in at least 1 and at most N instances of places — more precisely, here N equals 1, so an order is placed by exactly one customer. On the CUSTOMER side (0, N) means a customer participates in zero or more instances of places.

Degree of a Relationship

The degree of a relationship refers to the number of distinct entity types that participate in it. Understanding degree is essential to ensure the model does not oversimplify or misrepresent complex real-world associations.

• Unary (Degree 1) — Recursive Relationship: A single entity type participates in a relationship with itself. The entity appears on both "sides" of the relationship. A canonical example is an EMPLOYEE entity in which some employees manage other employees. The relationship involves only one entity type: EMPLOYEE. Unary relationships are discussed in detail below.
• Binary (Degree 2): Two distinct entity types participate. This is the most common and most directly translatable relationship type. Examples include DEPARTMENT employs EMPLOYEE, or AUTHOR writes BOOK. The vast majority of relationships modeled in practice are binary.
• Ternary (Degree 3): Three distinct entity types participate simultaneously in a single relationship. The classic textbook example is SUPPLIER supplies PART to PROJECT. This cannot be accurately decomposed into two separate binary relationships without potentially losing information about which combination of supplier, part, and project is valid. In a relational schema, a ternary relationship is implemented with a junction table that holds foreign keys to all three participating entities.

It is worth noting that higher-degree relationships (quaternary, etc.) exist in theory but are exceptionally rare. In practice, when a designer encounters what appears to be a high-degree relationship, it often signals either a missing entity or an opportunity to restructure the model. Always ask: "Is there an implicit entity here that should be made explicit?" For ternary relationships, the answer is sometimes yes — for example, a ternary supplier-part-project relationship might be better modeled as a SUPPLY_CONTRACT entity with binary relationships to SUPPLIER, PART, and PROJECT.

Recursive (Self-Referencing) Relationships

A recursive relationship (also called a unary or self-referencing relationship) is one in which an entity participates in a relationship with other instances of the same entity type. These arise naturally when modeling hierarchies, organizational structures, networks, and classification systems.

The most frequently cited example is the manages relationship on EMPLOYEE: an employee can be the manager of other employees, yet all individuals involved are employees. In an ER diagram, the relationship diamond connects back to the same EMPLOYEE rectangle using two separate lines. To avoid ambiguity about which role each line represents, role names are attached to the lines. In the manages example, one role might be labeled manager and the other subordinate, clearly distinguishing which side of the relationship each participating employee occupies.

Cardinality applies to recursive relationships just as it does to binary ones:

• A 1:N recursive relationship — one manager can supervise many employees, but each employee has at most one direct manager — is the standard representation of a strict organizational hierarchy or a tree structure. In a relational table, this is implemented with a self-referencing foreign key column, such as manager_id in the EMPLOYEE table that references employee_id in the same table. Employees at the top of the hierarchy (who have no manager) will have a NULL value in manager_id.
• An M:N recursive relationship — for example, a friendship relationship where one person can befriend many others and each person can be befriended by many — requires a junction table. In a social network model, a FRIENDSHIP table with columns person_id_1 and person_id_2, both referencing the primary key of a PERSON table, would capture this structure.

An illustrative self-referencing table structure for the 1:N manager-employee hierarchy looks like this in SQL:

CREATE TABLE employee (
    employee_id   INT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    manager_id    INT NULL,
    FOREIGN KEY (manager_id) REFERENCES employee(employee_id)
);

Recursive relationships also appear in product hierarchies (a CATEGORY that has sub-categories), bill-of-materials structures (a COMPONENT that consists of other components), and prerequisite chains (a COURSE that requires other courses). In each case, recognizing the self-referencing nature of the relationship and applying appropriate role names and cardinality is essential to building a correct schema.

Representing Relationships in ER Diagrams

Two notations dominate practical ER diagram drawing: Chen notation and Crow's Foot notation. Both convey the same semantic information — entity types, relationship names, cardinality, and participation — but use different visual vocabularies.

In Chen notation:

• Entities are drawn as rectangles labeled with the entity name.
• Relationships are drawn as diamonds labeled with the relationship verb.
• Lines connect each entity rectangle to the relationship diamond.
• Cardinality ratios (1, N, M) are written alongside the connecting lines, near each entity.
• Total participation is indicated by a double line between the entity and the relationship diamond.
• Partial participation is indicated by a single line.
• In recursive relationships, role names are written on the two lines connecting the entity to the relationship diamond.

In Crow's Foot notation (used by many modern modeling tools such as Lucidchart, ERDPlus, and MySQL Workbench):

• Relationships are shown as lines directly connecting entity rectangles — no diamond shapes are used.
• The end of each line carries a symbol indicating cardinality and participation:
  • A single vertical bar ( | ) means "exactly one."
  • A crow's foot (three diverging lines resembling a bird's foot) means "many."
  • A circle (○) means "zero" (used alongside the other symbols to indicate optionality).
• Combining these symbols: a crow's foot with a circle means "zero or many" (partial participation, many side); a crow's foot with a single bar means "one or many" (total participation, many side); a single bar with a circle means "zero or one"; two single bars mean "exactly one."

The following table shows the combined cardinality and participation symbols used in Crow's Foot notation:

Regardless of notation, the goal of the ER diagram is to produce an unambiguous, communicable model that a developer, business analyst, or stakeholder can interpret correctly. Consistent use of notation conventions, meaningful entity and relationship names, clear role labels on recursive relationships, and accurate cardinality markings all contribute to a diagram that translates reliably into a well-structured relational database schema. A designer who is meticulous at this stage prevents a wide range of data integrity problems, redundant structures, and query complexity from arising later.