Entity-Relationship Diagrams and Database Relationship Types

An entity-relationship diagram, or ERD, visually represents the structure of a database domain. It shows the entities about which data is stored, their attributes, and the relationships connecting them.

ERDs help business analysts, data architects, database designers, and developers agree on a data structure before implementing it in a database.

This article covers:

• The purpose of an ERD
• Entities, attributes, keys, and relationships
• Chen and Crow’s Foot notation
• Cardinality and optionality
• One-to-one relationships
• One-to-many relationships
• Many-to-many relationships
• Converting ER relationships into relational tables

What Is an Entity-Relationship Diagram?

An ERD is a visual model of the entities and relationships within a system.

A library ERD might contain entities such as:

• Book
• Author
• Borrower
• Book copy
• Loan
• Publisher

It can represent business rules such as:

• A publisher can publish multiple books.
• A book may have multiple authors.
• A library can own several copies of one book.
• A borrower can receive multiple loans.
• Each loan refers to one particular book copy.

An ERD does not contain the database’s actual records. It describes the structure and rules those records must follow.

Why ERDs Are Useful

ERDs help teams:

• Identify the data a system must store
• Establish consistent business terminology
• Understand relationships between concepts
• Detect missing or duplicated entities
• Define identifiers and constraints
• Resolve many-to-many relationships
• Plan relational database tables
• Communicate database designs visually
• Document existing systems

ERDs can be created at conceptual, logical, or physical levels. Conceptual diagrams show high-level entities and relationships, while physical diagrams may include tables, columns, data types, keys, and indexes. IBM’s ERD overview describes these three levels of detail.

Fundamental ERD Components

The principal elements of an ERD are:

1. Entities
2. Attributes
3. Keys
4. Relationships
5. Cardinality
6. Optionality

Crow’s Foot notation is not itself a database component. It is one of several visual notation systems used to represent these components.

Entities

An entity represents a distinguishable person, object, place, event, transaction, or concept about which the system stores data.

Examples include:

• Author
• Book
• Customer
• Order
• Employee
• Department
• Loan

Entities are commonly represented with rectangles:

┌─────────────┐
│    AUTHOR   │
└─────────────┘

Entity Type and Entity Instance

An entity type describes a category:

AUTHOR

AUTHOR

An entity instance is one specific member of that category:

author_id: 1001
first_name: Octavia
last_name: Butler

author_id: 1001
first_name: Octavia
last_name: Butler

An ERD normally depicts entity types rather than individual records.

Attributes

Attributes describe the properties of an entity.

The BOOK entity might contain:

• Book ID
• Title
• Edition
• Publication year
• ISBN
• Price

The AUTHOR entity might contain:

• Author ID
• First name
• Last name
• Email
• City
• Country

In a relational database, an entity commonly becomes a table and its attributes become columns:

AUTHOR
-------------------
author_id
first_name
last_name
email
city
country

AUTHOR
-------------------
author_id
first_name
last_name
email
city
country

An attribute normally belongs to an entity or, in some models, to a relationship.

Keys

A key identifies an entity instance or connects related entities.

Primary Key

A primary key uniquely identifies each record.

AUTHOR.author_id
BOOK.book_id

AUTHOR.author_id
BOOK.book_id

No two author records should have the same author_id.

Foreign Key

A foreign key references a key in another table.

BOOK.publisher_id
    → PUBLISHER.publisher_id

BOOK.publisher_id
    → PUBLISHER.publisher_id

Foreign keys are used to implement relationships and maintain referential integrity.

Composite Key

A composite key contains more than one attribute.

For example:

BOOK_AUTHOR
-------------------
book_id    [PK, FK]
author_id  [PK, FK]

BOOK_AUTHOR
-------------------
book_id    [PK, FK]
author_id  [PK, FK]

The combination of book_id and author_id uniquely identifies an authorship relationship.

Relationships

A relationship describes an association between entities.

Examples include:

AUTHOR writes BOOK
CUSTOMER places ORDER
EMPLOYEE belongs to DEPARTMENT
BORROWER receives LOAN
PUBLISHER publishes BOOK

AUTHOR writes BOOK
CUSTOMER places ORDER
EMPLOYEE belongs to DEPARTMENT
BORROWER receives LOAN
PUBLISHER publishes BOOK

Relationships are often named with verbs because they describe how entities interact.

A relationship set represents all instances of a particular relationship type. For example, the writes relationship set contains all known associations between authors and books.

Cardinality

Cardinality describes the maximum number of instances that may participate in a relationship.

The three principal relationship types are:

• One-to-one
• One-to-many
• Many-to-many

Cardinality answers questions such as:

• How many orders may one customer place?
• How many customers can one order belong to?
• How many authors may write one book?
• How many books may one author write?

Optionality

Optionality describes the minimum participation in a relationship.

The minimum is generally:

• Zero: participation is optional.
• One: participation is mandatory.

Therefore, a relationship endpoint is more precisely described with a minimum and maximum:

Cardinality and optionality should be defined separately. Saying that a relationship is “one-to-many” does not indicate whether either side is optional.

ERD Notation Systems

Several notation systems are used to draw ERDs. Two important ones are Chen notation and Crow’s Foot notation.

Chen Notation

Traditional Chen notation uses different shapes for model components:

A simplified representation might look like:

┌────────┐       ◇ WRITES ◇       ┌──────┐
│ AUTHOR │ ────────────────────── │ BOOK │
└────────┘                        └──────┘

Attributes are displayed separately:

                   (first_name)
                         |
(last_name) ─────── [AUTHOR] ─────── (email)
                         |
                    (author_id)

Chen notation can be useful for conceptual modeling, but diagrams become crowded when entities have many attributes.

Crow’s Foot Notation

Crow’s Foot notation places relationship symbols at the ends of lines connecting entities.

The symbols express minimum and maximum participation.

A conceptual text approximation is:

These elements can be combined:

o|    zero or one
||    exactly one
o<    zero or many
|<    one or many

o|    zero or one
||    exactly one
o<    zero or many
|<    one or many

Actual ERD software displays the “many” symbol as three diverging lines resembling a bird’s foot—not simply an ordinary greater-than or less-than character.

Do Not Mix Notation Rules

Chen notation and Crow’s Foot notation express similar modeling concepts but use different symbols.

For example:

• In Chen notation, a relationship is commonly represented by a diamond.
• In Crow’s Foot notation, the relationship is usually represented directly by a labeled line.
• In Chen notation, attributes may appear as ovals.
• In Crow’s Foot diagrams, attributes are usually listed inside entity boxes.

A diagram should use one notation consistently.

One-to-One Relationships

A one-to-one relationship means that one instance of Entity A is associated with at most one instance of Entity B, and one instance of Entity B is associated with at most one instance of Entity A.

Example:

PERSON 1 ───────── 1 PASSPORT

PERSON 1 ───────── 1 PASSPORT

Within a simplified model:

• One person can possess at most one current passport.
• One passport belongs to exactly one person.

A relational implementation might be:

CREATE TABLE person (
    person_id BIGINT PRIMARY KEY,
    full_name VARCHAR(200) NOT NULL
);

CREATE TABLE passport (
    passport_id BIGINT PRIMARY KEY,
    person_id BIGINT NOT NULL UNIQUE,
    passport_number VARCHAR(30) NOT NULL UNIQUE,

    CONSTRAINT fk_passport_person
        FOREIGN KEY (person_id)
        REFERENCES person (person_id)
);

CREATE TABLE person (
    person_id BIGINT PRIMARY KEY,
    full_name VARCHAR(200) NOT NULL
);

CREATE TABLE passport (
    passport_id BIGINT PRIMARY KEY,
    person_id BIGINT NOT NULL UNIQUE,
    passport_number VARCHAR(30) NOT NULL UNIQUE,

    CONSTRAINT fk_passport_person
        FOREIGN KEY (person_id)
        REFERENCES person (person_id)
);

The UNIQUE constraint on passport.person_id prevents multiple passport records from referencing the same person.

When to Use One-to-One

A one-to-one relationship can be useful when:

• Optional details are separated from a core table.
• Sensitive information requires different access controls.
• Two existing entities must remain distinct.
• Subtype-specific fields are separated.
• Large, rarely accessed fields should be isolated.

Not every one-to-one concept needs two tables. If both sides always exist and share the same lifecycle, combining them may produce a simpler design.

One-to-Many Relationships

A one-to-many relationship means that one instance of Entity A can relate to multiple instances of Entity B, while each instance of Entity B relates to one instance of Entity A.

Example:

PUBLISHER 1 ───────── many BOOK

PUBLISHER 1 ───────── many BOOK

Business rules:

• One publisher can publish many books.
• Each book in this simplified model has one publisher.

A relational implementation places the foreign key on the “many” side:

CREATE TABLE publisher (
    publisher_id BIGINT PRIMARY KEY,
    publisher_name VARCHAR(200) NOT NULL
);

CREATE TABLE book (
    book_id BIGINT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    publisher_id BIGINT NOT NULL,

    CONSTRAINT fk_book_publisher
        FOREIGN KEY (publisher_id)
        REFERENCES publisher (publisher_id)
);

CREATE TABLE publisher (
    publisher_id BIGINT PRIMARY KEY,
    publisher_name VARCHAR(200) NOT NULL
);

CREATE TABLE book (
    book_id BIGINT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    publisher_id BIGINT NOT NULL,

    CONSTRAINT fk_book_publisher
        FOREIGN KEY (publisher_id)
        REFERENCES publisher (publisher_id)
);

The publisher_id foreign key appears in book because many book rows can reference one publisher row.

Direction Matters

The same relationship can be described from either direction:

• Publisher to book: one-to-many
• Book to publisher: many-to-one

These are two perspectives on the same relationship.

Optional One-to-Many Relationships

Suppose a publisher may exist before any books have been registered, but every book must have a publisher.

The participation rules are:

PUBLISHER → zero or many BOOKS
BOOK → exactly one PUBLISHER

PUBLISHER → zero or many BOOKS
BOOK → exactly one PUBLISHER

This is more precise than simply stating “one-to-many.”

Optionality is represented differently on each side:

• A publisher’s participation in book records is optional.
• A book’s participation in the publisher relationship is mandatory.

Many-to-Many Relationships

A many-to-many relationship means that multiple instances of Entity A can relate to multiple instances of Entity B.

The author–book relationship is a common example:

AUTHOR many ───────── many BOOK

AUTHOR many ───────── many BOOK

Business rules:

• One author may write multiple books.
• One book may have multiple authors.

The source transcript inconsistently describes this as one-to-one and one-to-many in different places. Under the stated rules, it is a many-to-many relationship.

Resolving a Many-to-Many Relationship

Relational databases normally implement a many-to-many relationship through an associative entity, also called a:

• Junction table
• Bridge table
• Linking table
• Intersection table

The author–book model becomes:

AUTHOR 1 ─── many BOOK_AUTHOR many ─── 1 BOOK

AUTHOR 1 ─── many BOOK_AUTHOR many ─── 1 BOOK

Tables:

AUTHOR
-------------------
author_id [PK]
first_name
last_name

BOOK
-------------------
book_id [PK]
title
isbn

BOOK_AUTHOR
-------------------
book_id [PK, FK]
author_id [PK, FK]
author_order
contribution_role

AUTHOR
-------------------
author_id [PK]
first_name
last_name

BOOK
-------------------
book_id [PK]
title
isbn

BOOK_AUTHOR
-------------------
book_id [PK, FK]
author_id [PK, FK]
author_order
contribution_role

Physical implementation:

CREATE TABLE author (
    author_id BIGINT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL
);

CREATE TABLE book (
    book_id BIGINT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    isbn VARCHAR(20) UNIQUE
);

CREATE TABLE book_author (
    book_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    author_order INTEGER,
    contribution_role VARCHAR(100),

    CONSTRAINT pk_book_author
        PRIMARY KEY (book_id, author_id),

    CONSTRAINT fk_book_author_book
        FOREIGN KEY (book_id)
        REFERENCES book (book_id),

    CONSTRAINT fk_book_author_author
        FOREIGN KEY (author_id)
        REFERENCES author (author_id),

    CONSTRAINT chk_author_order
        CHECK (
            author_order IS NULL
            OR author_order > 0
        )
);

CREATE TABLE author (
    author_id BIGINT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL
);

CREATE TABLE book (
    book_id BIGINT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    isbn VARCHAR(20) UNIQUE
);

CREATE TABLE book_author (
    book_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    author_order INTEGER,
    contribution_role VARCHAR(100),

    CONSTRAINT pk_book_author
        PRIMARY KEY (book_id, author_id),

    CONSTRAINT fk_book_author_book
        FOREIGN KEY (book_id)
        REFERENCES book (book_id),

    CONSTRAINT fk_book_author_author
        FOREIGN KEY (author_id)
        REFERENCES author (author_id),

    CONSTRAINT chk_author_order
        CHECK (
            author_order IS NULL
            OR author_order > 0
        )
);

Why the Associative Entity Matters

The BOOK_AUTHOR entity does more than connect the two tables.

It can also store attributes belonging to the relationship:

• Author order
• Contribution role
• Royalty percentage
• Date assigned
• Credited name

These properties do not describe only the author or only the book. They describe that author’s participation in that particular book.

For example, the same author may be:

• The primary author of one book
• A co-author of another
• An editor of a third

Recursive Relationships

An entity can have a relationship with itself.

Example:

EMPLOYEE manages EMPLOYEE

EMPLOYEE manages EMPLOYEE

One employee may manage multiple employees, while an employee may report to one manager.

CREATE TABLE employee (
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(200) NOT NULL,
    manager_id BIGINT,

    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES employee (employee_id)
);

CREATE TABLE employee (
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(200) NOT NULL,
    manager_id BIGINT,

    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES employee (employee_id)
);

The manager_id column points back to another row in the same table.

Recursive relationships can represent:

• Organizational hierarchies
• Category trees
• Folder structures
• Bill-of-materials structures
• Referral relationships

Relationship Attributes

Some attributes belong to a relationship rather than either participating entity.

Consider:

STUDENT enrolls in COURSE

STUDENT enrolls in COURSE

The relationship may have:

• Enrollment date
• Grade
• Enrollment status
• Completion date

These values describe one student’s enrollment in one course.

The relational design should therefore introduce an associative entity:

ENROLLMENT
-------------------
student_id [PK, FK]
course_id [PK, FK]
enrollment_date
status
grade

ENROLLMENT
-------------------
student_id [PK, FK]
course_id [PK, FK]
enrollment_date
status
grade

Identifying and Non-Identifying Relationships

Physical Crow’s Foot diagrams may distinguish between identifying and non-identifying relationships.

Identifying Relationship

The parent key forms part of the child entity’s primary key.

Example:

ORDER
- order_id [PK]

ORDER_ITEM
- order_id [PK, FK]
- line_number [PK]

ORDER
- order_id [PK]

ORDER_ITEM
- order_id [PK, FK]
- line_number [PK]

An order item is identified by its order and line number.

Non-Identifying Relationship

The foreign key does not form part of the child’s primary key.

Example:

BOOK
- book_id [PK]
- publisher_id [FK]

BOOK
- book_id [PK]
- publisher_id [FK]

The book has its own independent primary key.

Different ERD tools may represent identifying relationships with solid lines and non-identifying relationships with dashed lines.

Common ERD Mistakes

Confusing Entity Types with Individual Records

AUTHOR is an entity type. “Octavia Butler” is an instance.

Treating Every Noun as an Entity

Some concepts are better represented as attributes.

For example, publication_year is usually an attribute of BOOK, not a separate entity.

Missing Primary Keys

Each independently stored relational entity should have a dependable identifier.

Putting the Foreign Key on the Wrong Side

In a one-to-many relationship, the foreign key normally belongs on the many side.

One publisher → many books
Foreign key: BOOK.publisher_id

One publisher → many books
Foreign key: BOOK.publisher_id

Implementing Many-to-Many Directly

A relational database needs an associative table to represent a many-to-many relationship properly.

Ignoring Optionality

“One-to-many” alone is incomplete. Determine whether the minimum participation is zero or one on each side.

Using Ambiguous Relationship Names

Prefer descriptive verbs:

CUSTOMER places ORDER
AUTHOR writes BOOK
EMPLOYEE belongs to DEPARTMENT

CUSTOMER places ORDER
AUTHOR writes BOOK
EMPLOYEE belongs to DEPARTMENT

Avoid vague labels such as has when a more precise term is available.

Mixing Notation Systems

Do not combine Chen ovals and diamonds with Crow’s Foot endpoints without a deliberate explanation.

Modeling Today’s Sample Instead of the Business Rule

A current dataset might show one author per book, but the business may permit several authors. Model the allowed business rule, not merely the limited sample currently available.

Developing an ERD

A practical ERD workflow includes the following steps.

1. Define the Scope

Identify the business process or domain:

Library lending

Library lending

2. Identify Candidate Entities

Book
Author
Book Copy
Borrower
Loan
Publisher

3. Define Identifiers

book_id
author_id
copy_id
borrower_id
loan_id
publisher_id

book_id
author_id
copy_id
borrower_id
loan_id
publisher_id

4. Add Important Attributes

Add only the details appropriate for the diagram’s level.

5. Identify Relationships

Author writes Book
Book has Book Copy
Borrower receives Loan
Loan concerns Book Copy
Publisher publishes Book

Author writes Book
Book has Book Copy
Borrower receives Loan
Loan concerns Book Copy
Publisher publishes Book

6. Establish Cardinality

Determine the maximum participation on each side.

7. Establish Optionality

Determine whether participation can be zero.

8. Resolve Many-to-Many Relationships

Introduce associative entities such as BOOK_AUTHOR.

9. Validate Business Rules

Review the model with subject-matter experts.

10. Translate It into a Physical Schema

Add:

• Tables
• Columns
• Database data types
• Primary and foreign keys
• Constraints
• Indexes

Example Library Model

A simplified logical model could be:

PUBLISHER
- publisher_id [PK]
- publisher_name

BOOK
- book_id [PK]
- title
- isbn
- publisher_id [FK]

AUTHOR
- author_id [PK]
- first_name
- last_name
- email

BOOK_AUTHOR
- book_id [PK, FK]
- author_id [PK, FK]
- author_order

BOOK_COPY
- copy_id [PK]
- book_id [FK]
- acquisition_date
- status

BORROWER
- borrower_id [PK]
- borrower_name
- email

LOAN
- loan_id [PK]
- borrower_id [FK]
- copy_id [FK]
- checkout_date
- due_date
- return_date

PUBLISHER
- publisher_id [PK]
- publisher_name

BOOK
- book_id [PK]
- title
- isbn
- publisher_id [FK]

AUTHOR
- author_id [PK]
- first_name
- last_name
- email

BOOK_AUTHOR
- book_id [PK, FK]
- author_id [PK, FK]
- author_order

BOOK_COPY
- copy_id [PK]
- book_id [FK]
- acquisition_date
- status

BORROWER
- borrower_id [PK]
- borrower_name
- email

LOAN
- loan_id [PK]
- borrower_id [FK]
- copy_id [FK]
- checkout_date
- due_date
- return_date

The principal relationships are:

PUBLISHER 1 ─── many BOOK

BOOK 1 ─── many BOOK_COPY

AUTHOR 1 ─── many BOOK_AUTHOR
BOOK 1 ─── many BOOK_AUTHOR

BORROWER 1 ─── many LOAN

BOOK_COPY 1 ─── many LOAN

PUBLISHER 1 ─── many BOOK

BOOK 1 ─── many BOOK_COPY

AUTHOR 1 ─── many BOOK_AUTHOR
BOOK 1 ─── many BOOK_AUTHOR

BORROWER 1 ─── many LOAN

BOOK_COPY 1 ─── many LOAN

The last relationship allows a copy to appear in many historical loans, although business constraints should prevent it from having more than one active loan simultaneously.

Key Takeaways

• An ERD visually represents entities, attributes, relationships, and business rules.
• Entities represent distinguishable concepts about which data is stored.
• Attributes describe entity properties.
• Keys identify records and connect related entities.
• Cardinality expresses the maximum number of relationship participants.
• Optionality expresses whether the minimum participation is zero or one.
• One-to-one relationships associate at most one instance on each side.
• One-to-many relationships place the foreign key on the many side.
• Many-to-many relationships require an associative entity in a relational design.
• The author–book relationship is generally many-to-many when books can have several authors and authors can write several books.
• Chen and Crow’s Foot are different notation systems and should not be mixed unintentionally.
• An ERD should represent business rules, not merely patterns found in a limited sample.

Conclusion

Entity-relationship diagrams provide a bridge between business requirements and database implementation. They reveal what information must be stored, how concepts interact, and which constraints the final system must enforce.

The most important part of an ERD is not its visual appearance. It is the precision of its business rules. Correctly defining cardinality, optionality, identifiers, and associative entities prevents ambiguity and produces a stronger database design.

One-sentence summary: An ERD models database entities and their relationships, using cardinality and optionality to distinguish one-to-one, one-to-many, and many-to-many business rules.