SQL 81 🛢️ Entity-Relationship Modeling and Cardinality - KandZ

⏱️ Estimated read: 18 min | |

A database schema is not designed by typing CREATE TABLE. It is designed by understanding what the application needs to store, how the pieces relate, and what constraints the relationships impose. The Entity-Relationship model is the notation that captures this understanding before a single line of SQL is written. An entity is a thing the application tracks. A relationship is an association between entities. A cardinality is the number of instances of one entity that can be associated with instances of another. Getting the cardinality wrong is one of the most expensive design mistakes, because it is discovered only after the schema is in production and the data has become inconsistent.

Key point: Cardinality is not a property of the data. It is a property of the business rule. The model says “one customer has many orders” because the business says so, and the schema enforces it with a foreign key. If the business later says “one order can be split across many customers,” the model was wrong, and the schema must change.

Why ER modeling exists

The communication problem. A schema is a technical artifact, but the rules it enforces are business rules. The stakeholders understand the business, and the developers understand the schema. The ER model is the shared language that lets the two agree on the design before the code is written.

The cardinality problem. A schema that allows a customer to have many orders and an order to have many customers is a many-to-many relationship, and it requires a junction table. A schema that allows a customer to have many orders and an order to have exactly one customer is a one-to-many relationship, and it requires a foreign key. The two are different, and the difference is the cardinality.

The optionality problem. A relationship can be mandatory or optional. A customer must have an address, or a customer may have an address. The optionality determines whether the foreign key column is NOT NULL, and it is part of the cardinality notation.

The redundancy problem. A model that stores the same fact in two places is a model that will eventually have two different versions of the fact. The ER model surfaces the redundancy before the schema is built, so the normalization can be applied at design time.

The evolution problem. The business changes. A new entity is introduced, an old relationship is removed, a cardinality flips. The model documents the current rules, and the schema is the implementation. When the two diverge, the model is the source of truth for the intent.

The documentation problem. A schema with a hundred tables is unreadable without a diagram. The ER diagram is the documentation, and it is the first artifact a new developer reads.

The tool problem. The modeling tools read the diagram and generate the CREATE TABLE statements. The diagram is the input, and the schema is the output. The two are consistent by construction.

a. The entities and the attributes

An entity is a thing the application tracks. It has a name and a set of attributes. Each entity has a key, which is the attribute (or set of attributes) that uniquely identifies an instance.

The entity

An entity is drawn as a rectangle with the name inside.

┌──────────────┐
│   Customer   │
└──────────────┘

The name is singular and capitalized. The entity represents the set of all customers, and each instance is one customer.

The attributes

The attributes are the properties of the entity. They are drawn as ellipses connected to the rectangle.

     ┌──────────┐
     │  email   │
     └────┬─────┘
          │
┌─────────┴─────────┐
│     Customer      │
└─────┬──────┬──────┘
      │      │
 ┌────┴───┐ ┌┴──────┐
 │  name  │ │  id   │
 └────────┘ └───────┘

The attributes are id, name, and email. The id is the key, and it is underlined in the diagram.

The key

The key is the attribute that uniquely identifies an instance. It is underlined.

     ┌──────────┐
     │  email   │
     └────┬─────┘
          │
┌─────────┴─────────┐
│     Customer      │
└─────┬──────┬──────┘
      │      │
 ┌────┴───┐ ┌┴──────┐
 │  name  │ │  id   │
 └────────┘ └───────┘
              ▲
              │
           underlined

A key can be a single attribute or a composite of several. A composite key is drawn with multiple attributes underlined.

The candidate keys

An entity can have more than one candidate key. The id is the surrogate key, and the email is a natural key. Both uniquely identify a customer.

The attribute types

The attributes have types: string, integer, date, boolean, and so on. The types are not part of the classic ER notation, but the modern tools include them.

Attribute	Type	Constraint
`id`	integer	Primary key
`name`	string	Not null
`email`	string	Unique, not null

The weak entity

A weak entity is an entity that cannot exist without another entity. An order line cannot exist without an order. The weak entity is drawn with a double rectangle, and its relationship with the strong entity is drawn with a double diamond.

┌──────────┐         ╔══════════════╗         ╔════════════╗
│  Order   │─────────║  Contains    ║─────────║ OrderLine  ║
└──────────┘         ╚══════════════╝         ╚════════════╝

The OrderLine is the weak entity, and the Contains relationship is the identifying relationship.

The composite attribute

A composite attribute is an attribute that is composed of several sub-attributes. An address is composed of a street, a city, and a postal code.

        ┌──────────┐
        │ Address  │
        └────┬─────┘
             │
    ┌────────┼────────┐
    │        │        │
┌───┴──┐ ┌───┴──┐ ┌───┴───┐
│street│ │ city │ │postal │
└──────┘ └──────┘ └───────┘

The composite attribute is drawn as a tree.

The multivalued attribute

A multivalued attribute is an attribute that can have several values for one entity. A customer can have several phone numbers. The attribute is drawn with a double ellipse.

     ╔══════════╗
     ║  phone   ║
     ╚════╤═════╝
          │
┌─────────┴─────────┐
│     Customer      │
└───────────────────┘

The double ellipse is the multivalued attribute. A multivalued attribute is the first sign that the model needs a separate table.

The derived attribute

A derived attribute is an attribute that is computed from other attributes. An age is derived from the birthdate. The attribute is drawn with a dashed ellipse.

     ┌╌╌╌╌╌╌╌╌╌╌┐
     ╎   age    ╎
     └╌╌╌╌╌┬╌╌╌╌┘
           │
┌──────────┴────────┐
│     Customer      │
└───────────────────┘

The dashed ellipse is the derived attribute. The value is not stored; it is computed when needed.

b. The relationships and the cardinality

A relationship is an association between two entities. The cardinality describes how many instances of one entity are associated with instances of the other.

The relationship

A relationship is drawn as a diamond with a verb inside.

┌──────────┐         ┌──────────────┐         ┌──────────┐
│ Customer │─────────│   Places     │─────────│  Order   │
└──────────┘         └──────────────┘         └──────────┘

The Places relationship connects the Customer and the Order. The verb describes the action.

The cardinality notation

The cardinality is written on the lines that connect the relationship to the entities. The common notations are the crow’s foot and the (min, max).

The crow’s foot notation:

Symbol	Meaning
`│`	One
`○`	Zero
`<`	Many

The combination is read as (min, max). The ○< is “zero or many,” the │< is “one or many,” the ○│ is “zero or one,” and the ││ is “exactly one.”

The (min, max) notation:

Notation	Meaning
`(0, 1)`	Optional, at most one
`(1, 1)`	Mandatory, exactly one
`(0, N)`	Optional, many
`(1, N)`	Mandatory, at least one

The two notations carry the same information. The crow’s foot is more compact, and the (min, max) is more explicit.

The one-to-many

A customer places many orders. Each order is placed by exactly one customer.

┌──────────┐         ┌──────────────┐         ┌──────────┐
│ Customer │─────│<──│   Places     │───││────│  Order   │
└──────────┘         └──────────────┘         └──────────┘

The Customer side is (0, N), which is “zero or many.” The Order side is (1, 1), which is “exactly one.”

The relationship is implemented with a foreign key in the orders table:

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    order_date  DATE NOT NULL
);

The NOT NULL enforces the (1, 1) on the Order side.

The many-to-many

A student enrolls in many courses, and a course has many students.

┌──────────┐         ┌──────────────┐         ┌──────────┐
│ Student  │─────│<──│  Enrolls     │───││────│  Course  │
└──────────┘         └──────────────┘         └──────────┘

The relationship is many-to-many, and it cannot be implemented with a foreign key in either table. It requires a junction table.

CREATE TABLE enrollments (
    student_id INT NOT NULL REFERENCES students(id),
    course_id  INT NOT NULL REFERENCES courses(id),
    enrolled_at DATE NOT NULL,
    PRIMARY KEY (student_id, course_id)
);

The enrollments table is the junction. Its primary key is the combination of the two foreign keys.

The one-to-one

A user has one profile, and a profile belongs to one user.

┌──────────┐         ┌──────────────┐         ┌──────────┐
│   User   │─────││──│   Has        │───││────│ Profile  │
└──────────┘         └──────────────┘         └──────────┘

The relationship is one-to-one, and it is implemented with a foreign key in one of the tables, with a UNIQUE constraint.

CREATE TABLE profiles (
    id      BIGSERIAL PRIMARY KEY,
    user_id INT NOT NULL UNIQUE REFERENCES users(id),
    bio     TEXT
);

The UNIQUE enforces the (0, 1) on the Profile side. The NOT NULL enforces the (1, 1) on the User side.

The optionality

The optionality is the min part of the cardinality.

Cardinality	Meaning	Implementation
`(1, 1)`	Mandatory, exactly one	`NOT NULL UNIQUE`
`(0, 1)`	Optional, at most one	`UNIQUE`
`(1, N)`	Mandatory, at least one	The inverse side is `(1, 1)`
`(0, N)`	Optional, many	The inverse side is `(0, 1)` or `(1, 1)`

The identifying relationship

An identifying relationship is the one that connects a weak entity to its strong entity. The weak entity’s primary key includes the strong entity’s key.

CREATE TABLE order_lines (
    order_id    BIGINT NOT NULL REFERENCES orders(id),
    line_number INT NOT NULL,
    product_id  INT NOT NULL REFERENCES products(id),
    quantity    INT NOT NULL,
    PRIMARY KEY (order_id, line_number)
);

The order_lines table’s primary key is (order_id, line_number). The order_id is the foreign key to the strong entity, and the line_number distinguishes the lines within the order.

The recursive relationship

A recursive relationship connects an entity to itself. An employee has a manager, and the manager is also an employee.

         ┌──────────────┐
         │   Manages    │
         └───┬──────┬───┘
             │      │
             ▼      │
       ┌──────────┐ │
       │ Employee │◀┘
       └──────────┘

The relationship is implemented with a self-referencing foreign key.

CREATE TABLE employees (
    id         BIGSERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    manager_id BIGINT REFERENCES employees(id)
);

The manager_id references the employees table itself. The NULL allows the top of the hierarchy.

The ternary relationship

A ternary relationship connects three entities. A supplier supplies a part to a project.

          ┌──────────┐
          │ Supplies │
          └──┬──┬──┬─┘
             │  │  │
    ┌────────┘  │  └────────┐
    ▼           ▼           ▼
┌────────┐ ┌────────┐ ┌────────┐
│Supplier│ │ Part   │ │Project │
└────────┘ └────────┘ └────────┘

A ternary relationship is implemented with a table that has foreign keys to all three entities.

CREATE TABLE supplies (
    supplier_id INT NOT NULL REFERENCES suppliers(id),
    part_id     INT NOT NULL REFERENCES parts(id),
    project_id  INT NOT NULL REFERENCES projects(id),
    PRIMARY KEY (supplier_id, part_id, project_id)
);

The three-way primary key enforces the combination.

The cardinality table

Relationship	Implementation
One-to-many	Foreign key in the “many” table
Many-to-many	Junction table with two foreign keys
One-to-one	Foreign key with `UNIQUE`
Recursive	Self-referencing foreign key
Ternary	Junction table with three foreign keys

c. The modeling process

The ER model is built in steps. Each step produces a more detailed diagram.

The conceptual model

The conceptual model identifies the entities and the relationships. It does not include the attributes or the keys. It is the highest-level view.

Customer ──< Places >── Order
Order ──< Contains >── Product

The model is a few boxes and a few lines. It is the shared understanding with the stakeholders.

The logical model

The logical model adds the attributes, the keys, and the cardinality. It does not depend on a specific database.

Customer (id, name, email)
Order (id, customer_id, order_date)
Product (id, name, price)
OrderLine (order_id, line_number, product_id, quantity)

The model is independent of the physical storage. It is the abstract schema.

The physical model

The physical model adds the types, the indexes, the constraints, and the storage details. It is specific to the database.

CREATE TABLE customers (
    id    BIGSERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE INDEX idx_customers_email ON customers(email);

The model is the CREATE TABLE statement, and it is what the database receives.

The normalization

The logical model is normalized to eliminate the redundancy.

The first normal form eliminates the multivalued attributes.
The second normal form eliminates the partial dependencies.
The third normal form eliminates the transitive dependencies.

The normalization is applied at the logical level, and the physical model implements the result.

The denormalization

The physical model may denormalize for performance. The denormalization is a deliberate trade-off, and it is documented in the model.

The reverse engineering

An existing schema can be read and turned into an ER diagram. The tools read the catalog and produce the model, which is useful for documentation and for understanding a legacy database.

The process table

Step	Output
Conceptual	Entities and relationships
Logical	Attributes, keys, cardinality
Physical	Types, indexes, constraints
Normalization	The normalized logical model
Denormalization	The optimized physical model

Complete Example Session

============================================
PART 1: THE ENTITIES
============================================

┌──────────┐    ┌──────────┐    ┌──────────┐
│ Customer │    │  Order   │    │ Product  │
└──────────┘    └──────────┘    └──────────┘

============================================
PART 2: THE ATTRIBUTES
============================================

Customer: id, name, email
Order:    id, order_date, status
Product:  id, name, price

============================================
PART 3: THE RELATIONSHIPS
============================================

Customer ──< Places >── Order
Order    ──< Contains >── Product

============================================
PART 4: THE CARDINALITY
============================================

Customer (0,N) ──── (1,1) Order
  A customer places zero or many orders.
  An order is placed by exactly one customer.

Order (1,N) ──── (0,N) Product
  An order contains one or many products.
  A product is in zero or many orders.

============================================
PART 5: THE LOGICAL MODEL
============================================

Customer (id, name, email)
Order    (id, customer_id, order_date, status)
Product  (id, name, price)
OrderLine (order_id, line_number, product_id, quantity)

============================================
PART 6: THE JUNCTION TABLE
============================================

The many-to-many between Order and Product
requires the OrderLine junction.

OrderLine:
  - order_id (FK to Order)
  - line_number (distinguishes lines within the order)
  - product_id (FK to Product)
  - quantity

Primary key: (order_id, line_number)

============================================
PART 7: THE PHYSICAL MODEL
============================================

CREATE TABLE customers (
    id    BIGSERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    order_date  DATE NOT NULL,
    status      VARCHAR(20) NOT NULL
);

CREATE TABLE products (
    id    BIGSERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

CREATE TABLE order_lines (
    order_id    BIGINT NOT NULL REFERENCES orders(id),
    line_number INT NOT NULL,
    product_id  BIGINT NOT NULL REFERENCES products(id),
    quantity    INT NOT NULL,
    PRIMARY KEY (order_id, line_number)
);

============================================
PART 8: THE CARDINALITY ENFORCEMENT
============================================

The (1,1) on the Order side:
  customer_id NOT NULL

The (0,1) on the Customer side:
  A customer can have zero orders.

The (1,N) on the OrderLine side:
  PRIMARY KEY (order_id, line_number)

The (0,N) on the Product side:
  A product can be in zero order lines.

============================================
PART 9: THE RECURSIVE RELATIONSHIP
============================================

CREATE TABLE employees (
    id         BIGSERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    manager_id BIGINT REFERENCES employees(id)
);

The manager_id is the self-reference.

============================================
PART 10: THE COMPLETE MODEL
============================================

Customer (id, name, email)
  │
  │ (1,N)
  ▼
Order (id, customer_id, order_date, status)
  │
  │ (1,N)
  ▼
OrderLine (order_id, line_number, product_id, quantity)
  ▲
  │ (0,N)
  │
Product (id, name, price)

Employee (id, name, manager_id) ← recursive

The model is the shared understanding,
the logical schema is the abstract design,
the physical schema is the implementation.

The ten parts showed the entities, the attributes, the relationships, the cardinality, the logical model, the junction table, the physical model, the cardinality enforcement, the recursive relationship, and the complete model.

Quick Reference

The ER Symbols

Symbol	Meaning
Rectangle	Entity
Double rectangle	Weak entity
Ellipse	Attribute
Double ellipse	Multivalued attribute
Dashed ellipse	Derived attribute
Diamond	Relationship
Double diamond	Identifying relationship
Underlined	Key

The Cardinality Notation

Crow’s foot	(min, max)	Meaning
`││`	(1, 1)	Exactly one
`○│`	(0, 1)	Zero or one
`│<`	(1, N)	One or many
`○<`	(0, N)	Zero or many

The Relationship Types

Type	Implementation
One-to-many	Foreign key in the “many” table
Many-to-many	Junction table
One-to-one	Foreign key with `UNIQUE`
Recursive	Self-referencing foreign key
Ternary	Junction table with three foreign keys

The Attribute Types

Type	Symbol
Simple	Ellipse
Key	Underlined ellipse
Composite	Tree of ellipses
Multivalued	Double ellipse
Derived	Dashed ellipse

The Model Steps

Step	Output
Conceptual	Entities, relationships
Logical	Attributes, keys, cardinality
Physical	Types, indexes, constraints
Normalization	The normalized logical model

Best Practices

✅ Do This:

-- Use a junction table for many-to-many.
CREATE TABLE enrollments (
    student_id INT NOT NULL REFERENCES students(id),
    course_id  INT NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);  -- ✅

-- Use NOT NULL for mandatory relationships.
customer_id BIGINT NOT NULL REFERENCES customers(id)  -- ✅

-- Use UNIQUE for one-to-one.
user_id INT NOT NULL UNIQUE REFERENCES users(id)  -- ✅

-- Use a self-referencing foreign key for hierarchies.
manager_id BIGINT REFERENCES employees(id)  -- ✅

-- Name the relationships with verbs.
-- Customer places Order, Order contains Product.  -- ✅

-- Document the cardinality in the model.
-- (0,N) on the Customer side, (1,1) on the Order side.  -- ✅

-- Normalize before denormalizing.
-- Apply 1NF, 2NF, 3NF first.  -- ✅

❌ Don’t Do This:

-- Don't implement many-to-many with a foreign key.
-- A single column cannot reference many rows.  -- ❌

-- Don't forget the NOT NULL for a mandatory relationship.
customer_id BIGINT REFERENCES customers(id)  -- ❌ Allows NULL

-- Don't forget the UNIQUE for one-to-one.
user_id INT NOT NULL REFERENCES users(id)  -- ❌ Allows many profiles

-- Don't use a multivalued attribute in a column.
phones VARCHAR(200)  -- ❌ Violates 1NF

-- Don't store a derived attribute.
age INT  -- ❌ Derive from the birthdate

-- Don't skip the conceptual model.
-- The stakeholders must agree on the design.  -- ❌

-- Don't denormalize without a documented reason.
-- The trade-off must be explicit.  -- ❌

Common Pitfalls

Pitfall	Why It Happens	Fix
Many-to-many with one FK	Misunderstanding of the cardinality	Use a junction table
Optional relationship is mandatory	Missing `NOT NULL`	Add the constraint
One-to-one is one-to-many	Missing `UNIQUE`	Add the constraint
Multivalued attribute in a column	Violates 1NF	Separate table
Derived attribute stored	Redundancy	Compute on read
Recursive relationship missing	Hierarchy not modeled	Add the self-reference
Cardinality flipped	Misunderstanding of the business rule	Verify with the stakeholders

Real-World Examples

1. One-to-Many

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id)
);

2. Many-to-Many

CREATE TABLE enrollments (
    student_id INT NOT NULL REFERENCES students(id),
    course_id  INT NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);

3. One-to-One

CREATE TABLE profiles (
    user_id INT NOT NULL UNIQUE REFERENCES users(id)
);

4. Recursive

CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,
    manager_id BIGINT REFERENCES employees(id)
);

5. Ternary

CREATE TABLE supplies (
    supplier_id INT NOT NULL REFERENCES suppliers(id),
    part_id     INT NOT NULL REFERENCES parts(id),
    project_id  INT NOT NULL REFERENCES projects(id),
    PRIMARY KEY (supplier_id, part_id, project_id)
);

6. The Weak Entity

CREATE TABLE order_lines (
    order_id    BIGINT NOT NULL REFERENCES orders(id),
    line_number INT NOT NULL,
    PRIMARY KEY (order_id, line_number)
);

7. The Key

id BIGSERIAL PRIMARY KEY

8. The Composite Key

PRIMARY KEY (order_id, line_number)

9. The Candidate Key

email VARCHAR(255) NOT NULL UNIQUE

10. The Cardinality

-- (0,N) customer ──── (1,1) order
customer_id BIGINT NOT NULL REFERENCES customers(id)

Visual

The Entity

┌────────────────────────────────────────────────────────────┐
│  THE ENTITY                                                │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  ┌──────────┐                                     │     │
│  │  │ Customer │  ← rectangle with the entity name  │     │
│  │  └────┬─────┘                                     │     │
│  │       │                                           │     │
│  │  ┌────┴────┐  ┌─────────┐  ┌─────────┐           │     │
│  │  │   id    │  │  name   │  │  email  │           │     │
│  │  │ (key)   │  │         │  │         │           │     │
│  │  └─────────┘  └─────────┘  └─────────┘           │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The entity has a name and attributes.                     │
│  The key is underlined.                                    │
└────────────────────────────────────────────────────────────┘

The Cardinality Notation

┌────────────────────────────────────────────────────────────┐
│  THE CARDINALITY NOTATION                                  │
│                                                            │
│  CROW'S FOOT          (min, max)         MEANING           │
│  ────────────         ──────────         ───────           │
│  ││                   (1, 1)             Exactly one       │
│  ○│                   (0, 1)             Zero or one       │
│  │<                   (1, N)             One or many       │
│  ○<                   (0, N)             Zero or many      │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Customer (0,N) ──── (1,1) Order                  │     │
│  │                                                   │     │
│  │  The Customer side is zero or many.               │     │
│  │  The Order side is exactly one.                   │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Relationship Types

┌────────────────────────────────────────────────────────────┐
│  THE RELATIONSHIP TYPES                                    │
│                                                            │
│  ONE-TO-MANY                                               │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Customer (0,N) ──── (1,1) Order                  │     │
│  │  The foreign key is in the orders table.          │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  MANY-TO-MANY                                              │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Student (0,N) ──── (0,N) Course                  │     │
│  │  The junction table has two foreign keys.         │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  ONE-TO-ONE                                                │
│  ┌──────────────────────────────────────────────────┐     │
│  │  User (1,1) ──── (0,1) Profile                    │     │
│  │  The foreign key has a UNIQUE constraint.         │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  RECURSIVE                                                 │
│  ┌──────────────────────────────────────────────────┐     │
│  │  Employee (0,N) ──── (0,1) Employee               │     │
│  │  The foreign key references the same table.       │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Complete Model

┌────────────────────────────────────────────────────────────┐
│  THE COMPLETE MODEL                                        │
│                                                            │
│  ┌──────────┐                                              │
│  │ Customer │                                              │
│  └────┬─────┘                                              │
│       │ (1,N)                                              │
│       ▼                                                    │
│  ┌──────────┐                                              │
│  │  Order   │                                              │
│  └────┬─────┘                                              │
│       │ (1,N)                                              │
│       ▼                                                    │
│  ┌──────────┐         ┌──────────┐                        │
│  │OrderLine │◀────────│ Product  │                        │
│  └──────────┘  (0,N)  └──────────┘                        │
│                                                            │
│  ┌──────────┐                                              │
│  │ Employee │──┐                                           │
│  └──────────┘  │ (0,N)                                     │
│       ▲        │                                           │
│       └────────┘  (0,1) recursive                         │
│                                                            │
│  The model captures the entities, the attributes,          │
│  the relationships, and the cardinality.                   │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
Entity	A thing the application tracks
Attribute	A property of an entity
Key	The attribute that identifies an instance
Relationship	An association between entities
Cardinality	The number of associated instances
One-to-many	A foreign key in the “many” table
Many-to-many	A junction table
One-to-one	A foreign key with `UNIQUE`
Recursive	A self-referencing foreign key
Ternary	A junction table with three foreign keys

Key takeaways:

An entity is a thing the application tracks. It has a name, a set of attributes, and a key. The key uniquely identifies an instance, and it is underlined in the diagram.
A relationship is an association between entities. It is drawn as a diamond with a verb, and the cardinality is written on the lines that connect it to the entities.
The cardinality describes how many instances are associated. The (min, max) notation and the crow’s foot notation carry the same information. The min is the optionality, and the max is the multiplicity.
The one-to-many relationship is the most common. It is implemented with a foreign key in the “many” table, and the NOT NULL enforces the mandatory side.
The many-to-many relationship requires a junction table. The junction table has a foreign key to each entity, and its primary key is the combination. The table is the implementation of the relationship.
The one-to-one relationship uses a foreign key with UNIQUE. The UNIQUE enforces the at-most-one, and the NOT NULL enforces the mandatory side.
A recursive relationship references the same table. The self-referencing foreign key models the hierarchy, and the NULL allows the top of the tree.
A weak entity cannot exist without its strong entity. The weak entity’s primary key includes the strong entity’s key, and the identifying relationship connects the two.
The model has three levels: conceptual, logical, and physical. The conceptual is the shared understanding, the logical is the abstract design, and the physical is the implementation. The normalization is applied at the logical level.

Remember: The entity-relationship model is the design notation for a database. The entities are the things the application tracks, the relationships are the associations between them, and the cardinality is the number of associated instances. The one-to-many is a foreign key, the many-to-many is a junction table, the one-to-one is a foreign key with UNIQUE, and the recursive is a self-reference. The model is built in three levels: the conceptual, the logical, and the physical. The result is a schema that reflects the business rules, that enforces the cardinality, and that the stakeholders and the developers both understand.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!