The ER Model Explained

The Entity-Relationship (ER) model is a way to describe the data your application needs before you write a single line of SQL. You sketch the real-world "things" (entities), the facts you want to record about them (attributes), and how they connect (relationships). That sketch — an ER diagram — becomes your blueprint for actual database tables.

Think of it like an architect's drawing. You would not pour concrete before drawing the floor plan, and you should not create tables before drawing your ER model.

The three building blocks

The ER model has three core pieces:

• Entity — a thing you store data about. A Student, a Course, a Payment. Each individual student is an entity instance; the general idea of "Student" is the entity type.
• Attribute — a fact about an entity. A Student has a name, a phone number, a date of birth.
• Relationship — how entities connect. A Student enrols in a Course.

In a diagram, entities are usually drawn as rectangles, attributes as ovals, and relationships as diamonds (this is the classic Chen notation). Modern tools often use simpler "crow's foot" boxes instead.

Types of attributes

Not all attributes behave the same way:

• Simple — cannot be split (e.g. age).
• Composite — made of parts (e.g. full_name → first + last).
• Multivalued — can hold several values (e.g. a student with two phone numbers).
• Derived — calculated from other data (e.g. age derived from date_of_birth).
• Key attribute — uniquely identifies an entity instance (e.g. student_id).

Relationships and cardinality

Cardinality answers: how many of one entity relate to how many of another? There are three patterns:

• One-to-one (1:1) — one student has one student ID card.
• One-to-many (1:N) — one teacher teaches many batches, but each batch has one lead teacher.
• Many-to-many (M:N) — many students enrol in many courses.

Many-to-many relationships cannot be stored directly in a single table. They are resolved into a separate junction (associative) entity when you convert the diagram to tables — for example an enrolment table linking student and course.

From ER diagram to tables

Here is the practical part. Suppose we model a small coaching-institute system: a Student enrols in many Courses, and each course belongs to one Department. The many-to-many between students and courses becomes a junction table.

-- PostgreSQL / MySQL compatible (standard SQL types)

-- Strong entity: Department
CREATE TABLE department (
    department_id   INTEGER PRIMARY KEY,
    name            VARCHAR(100) NOT NULL
);

-- Entity: Course — 1:N from department (one dept, many courses)
CREATE TABLE course (
    course_id       INTEGER PRIMARY KEY,
    title           VARCHAR(150) NOT NULL,
    -- Foreign key represents the "belongs to" relationship
    department_id   INTEGER NOT NULL REFERENCES department(department_id)
);

-- Entity: Student
CREATE TABLE student (
    student_id      INTEGER PRIMARY KEY,
    full_name       VARCHAR(120) NOT NULL,   -- composite collapsed to one column
    date_of_birth   DATE                     -- age is DERIVED, so we do NOT store it
);

-- M:N relationship resolved into a junction table
CREATE TABLE enrolment (
    student_id      INTEGER NOT NULL REFERENCES student(student_id),
    course_id       INTEGER NOT NULL REFERENCES course(course_id),
    enrolled_on     DATE NOT NULL,
    -- Composite primary key: a student can enrol in a course only once
    PRIMARY KEY (student_id, course_id)
);

Notice the conversion rules in action:

1. Each entity became a table.
2. Each 1:N relationship became a foreign key on the "many" side (course.department_id).
3. The M:N relationship became its own junction table (enrolment) with a composite key.
4. The derived attribute age was not stored — we compute it from date_of_birth when needed.

Weak vs strong entities

A strong entity can be identified on its own (a Student has a student_id). A weak entity cannot — it depends on another entity for its identity. A classic example is an installment that only makes sense in the context of a payment_plan; its key includes the parent's key.

Common mistakes

• Storing derived data. Recording both date_of_birth and age guarantees the two will eventually disagree. Compute derived values; do not store them.
• Trying to store M:N in one table. Cramming multiple course IDs into a comma-separated column breaks the relational model. Always create a junction table.
• Confusing attributes with entities. An "address" with its own street, city, and pincode that you query independently may deserve to be its own entity, not a flat attribute.
• Ignoring cardinality early. Getting 1:N vs M:N wrong forces a painful redesign later. Decide cardinality on the diagram, not after tables exist.
• Drawing relationships without a verb. "Student — Course" is unclear. "Student enrols in Course" tells you the relationship's meaning and direction.

FAQ

Is the ER model the same as the relational model? No. The ER model is a conceptual design tool — diagrams and notation. The relational model is the logical model of tables, rows, and columns that the ER diagram converts into.

Do I always need an ER diagram? For anything beyond a single table, yes. It is far cheaper to fix a relationship on paper than after data is loaded.

What notation should I learn? Crow's foot notation is the most common in industry tools today. Chen notation appears more in academic courses. Learn to read both.

Keep learning

Once your ER diagram is solid, the next step is understanding the relational model it maps onto, getting keys in DBMS right, and refining the design through normalization.

Want hands-on practice designing real schemas? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon, and learn database design from the diagram to the deployed table.