Data Modeling: Entity-Relationship Fundamentals

1. What data modeling delivers before any SQL is written

Data modeling with the entity-relationship approach is the conceptual step that should happen before every single CREATE TABLE statement. Instead of designing tables and columns directly, you first identify the relevant objects of the business domain, their properties, and how they relate to each other. This intermediate step can feel like unnecessary overhead to experienced developers, but it pays off in any project that goes beyond a weekend prototype, because it surfaces modeling mistakes before they get baked into migration scripts and application code.

The result of data modeling is an entity-relationship diagram, or ER diagram, which uses three basic building blocks: entities as rectangles, attributes as properties of those entities, and relationships as connections between entities. This diagram is deliberately database agnostic, it contains no SQL data types and no implementation details such as indexes. Only in a second step, mapping onto a relational schema, does this conceptual model turn into actual tables with columns, primary keys and foreign keys.

2. Defining entities and attributes

An entity is a standalone object of the business domain that the database should store information about, for example a book, an author or a library member. The rule of thumb in data modeling: if an object has its own identity and can exist independently of other objects, it is a good candidate for its own entity. A book exists independently of whether it is currently checked out, so "book" is an entity. The borrowing act itself connects two entities and typically becomes its own entity once it carries its own attributes such as a borrow date.

Attributes are the properties of an entity: a book has a title, an ISBN and a publication year, a member has a name and an email address. Every entity needs at least one attribute, or a combination of attributes, that uniquely identifies it, the so-called candidate key. In data modeling a distinction is made between simple attributes, which carry a single atomic value, composite attributes such as an address made up of street, postal code and city, and multivalued attributes, which typically get extracted into their own table later, because relational databases do not handle lists inside a single column well.

3. Relationships: verbs between entities

A relationship describes how two or more entities interact with each other, and in an ER diagram it is usually named with a verb: a member "borrows" a book, an author "writes" a book. This choice of language is not accidental, it is a useful trick of data modeling: if a relationship cannot be expressed with a simple verb, the modeling itself is often still unclear and should be revised before it gets translated into a schema.

Relationships can be unary, when an entity relates to itself, for example when a book is a sequel to another book, binary, when exactly two entities are involved, which is by far the most common case, or ternary, when three entities participate in a single relationship at once, for example a member, a book and a specific copy in a loan at a particular branch. Ternary relationships are rarely truly necessary in data modeling, the same information can often be expressed more cleanly through two binary relationships, which significantly simplifies the later mapping onto tables.

4. Cardinality and crow's foot notation

Cardinality describes how many instances of one entity can participate in a relationship with how many instances of another entity. The three basic forms are 1:1, 1:n and n:m. An example of 1:1: every library member has exactly one membership card, and every membership card belongs to exactly one member. An example of 1:n: an author may have written several books, but in a simplified model every book has exactly one primary author. An example of n:m: a member can borrow many books, and a book can be borrowed by many different members over time.

Crow's foot notation is the most widely used standard in practice for data modeling diagrams. A single tick mark at one end of the connecting line means "exactly one", a circle means "zero or optional", and the namesake crow's foot, three fanning lines, means "many". Combined, they form the familiar symbols: a tick plus a circle means "zero or one", a crow's foot plus a tick means "one or more", a crow's foot plus a circle means "zero or more". This notation makes it visible at a glance whether a relationship is optional or mandatory, information that later translates directly into NOT NULL constraints.

5. Weak entities and identification

A weak entity is an entity that cannot meaningfully exist without another, identifying entity, and that has no fully independent primary key of its own. A typical example in the data modeling of a library system: a physical copy of a book. A copy has an inventory number, but that number is only unique within a specific book, not globally. Without a reference to the owning book, the inventory number alone carries no meaning.

In an ER diagram, weak entities are marked with a double-bordered rectangle, and the identifying relationship with a double-bordered diamond. When implementing in SQL, the primary key of a weak entity is usually modeled as a composite key, consisting of the foreign key to the owning entity plus a local discriminator. In practice, though, a global surrogate key is often assigned to weak entities anyway, because it simplifies downstream JOINs, while the business dependency is still enforced through a NOT NULL foreign key column. This pragmatic deviation is a deliberate compromise between pure data modeling theory and practical implementation.

-- Weak entity modeled with a composite key: local number only
-- unique within its owning book, not globally
CREATE TABLE book_copies (
    book_id       INT NOT NULL,
    copy_number   INT NOT NULL,
    condition     VARCHAR(20) NOT NULL DEFAULT 'good',
    PRIMARY KEY (book_id, copy_number),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Pragmatic alternative: global surrogate key, dependency still enforced
CREATE TABLE book_copies_surrogate (
    copy_id       INT PRIMARY KEY AUTO_INCREMENT,
    book_id       INT NOT NULL,  -- NOT NULL enforces the identifying relationship
    copy_number   INT NOT NULL,
    condition     VARCHAR(20) NOT NULL DEFAULT 'good',
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    UNIQUE (book_id, copy_number)
);

6. From entity to table: mapping rules

The transition from an ER diagram to a relational table follows a fixed set of rules. Every strong entity becomes its own table, every simple attribute becomes a column, and the chosen candidate key becomes the primary key. A 1:n relationship gets mapped by placing the foreign key column on the "many" side of the relationship, meaning in the table that can potentially have many instances: since an author can write many books, the books table gets an author_id column, not the other way around.

A 1:1 relationship can be mapped in two ways: either the foreign key is placed with an additional UNIQUE constraint in one of the two tables, or you consider whether the two entities belong together closely enough in content that merging them into a single table makes more sense. This decision often depends on whether the second entity carries optional, rarely needed attributes that you want to keep separate for performance reasons. In either case the data modeling rule stays fixed: the foreign key always references the primary key of the target table, never the other way around.

-- 1:n mapping: foreign key goes on the "many" side
CREATE TABLE authors (
    author_id   INT PRIMARY KEY AUTO_INCREMENT,
    full_name   VARCHAR(150) NOT NULL
);

CREATE TABLE books (
    book_id     INT PRIMARY KEY AUTO_INCREMENT,
    title       VARCHAR(255) NOT NULL,
    isbn        VARCHAR(20) NOT NULL UNIQUE,
    author_id   INT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

-- 1:1 mapping: foreign key with a UNIQUE constraint
CREATE TABLE members (
    member_id   INT PRIMARY KEY AUTO_INCREMENT,
    full_name   VARCHAR(150) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE membership_cards (
    card_id     INT PRIMARY KEY AUTO_INCREMENT,
    member_id   INT NOT NULL UNIQUE,  -- UNIQUE enforces 1:1
    issued_on   DATE NOT NULL,
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

7. Many-to-many relationships and junction tables

An n:m relationship cannot be mapped directly in a relational schema, because a single column can only ever carry one single foreign key value. The solution that every data modeling effort applies at this point is a dedicated junction table, often also called an association table, which contains two foreign keys, one to each of the participating entities. This junction table structurally resolves the n:m relationship into two 1:n relationships: a member has many rows in the loans table, and a book also has many rows in that same table.

The composite primary key of the junction table typically consists of both foreign keys, unless the relationship itself carries additional attributes that justify its own identity, such as a borrow date, a return date or a processing status. In that case the junction table often gets its own surrogate primary key, and the two foreign keys instead form a composite UNIQUE index, to prevent the same member from borrowing the same book twice at the same time.

-- n:m mapping: junction table resolves the many-to-many relationship
CREATE TABLE loans (
    loan_id      INT PRIMARY KEY AUTO_INCREMENT,
    member_id    INT NOT NULL,
    book_id      INT NOT NULL,
    borrowed_on  DATE NOT NULL,
    returned_on  DATE,  -- NULL means the book is still checked out
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- Prevent the same member from having two open loans of the same book
CREATE UNIQUE INDEX idx_one_active_loan
    ON loans (member_id, book_id)
    WHERE returned_on IS NULL;

8. Complete example: the library system as DDL

Putting all the previous steps of data modeling together produces a complete, consistent schema for the library system: authors and books in a 1:n relationship, members and membership_cards in a 1:1 relationship, and members with books through the loans junction table in an n:m relationship. Every one of these tables came directly from an entity or relationship identified in the ER diagram, without new structural decisions having to be improvised during implementation.

This methodical path, modeling conceptually first and then mapping mechanically onto tables, significantly reduces the risk of rework. If a new requirement gets added later, for example that a book can have several authors, the ER diagram immediately shows that the cardinality changes from 1:n to n:m and a new junction table is needed, instead of that insight only surfacing while writing a failing query.

-- A query enabled directly by the modeled relationships:
-- all currently active loans with member and book details
SELECT
    m.full_name  AS member_name,
    b.title      AS book_title,
    l.borrowed_on
FROM loans AS l
JOIN members AS m ON m.member_id = l.member_id
JOIN books   AS b ON b.book_id   = l.book_id
WHERE l.returned_on IS NULL
ORDER BY l.borrowed_on;

-- Complete library schema derived directly from the ER diagram
CREATE TABLE authors (
    author_id  INT PRIMARY KEY AUTO_INCREMENT,
    full_name  VARCHAR(150) NOT NULL
);

CREATE TABLE books (
    book_id    INT PRIMARY KEY AUTO_INCREMENT,
    title      VARCHAR(255) NOT NULL,
    isbn       VARCHAR(20) NOT NULL UNIQUE,
    author_id  INT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE members (
    member_id  INT PRIMARY KEY AUTO_INCREMENT,
    full_name  VARCHAR(150) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE membership_cards (
    card_id    INT PRIMARY KEY AUTO_INCREMENT,
    member_id  INT NOT NULL UNIQUE,
    issued_on  DATE NOT NULL,
    FOREIGN KEY (member_id) REFERENCES members(member_id)
);

CREATE TABLE loans (
    loan_id      INT PRIMARY KEY AUTO_INCREMENT,
    member_id    INT NOT NULL,
    book_id      INT NOT NULL,
    borrowed_on  DATE NOT NULL,
    returned_on  DATE,
    FOREIGN KEY (member_id) REFERENCES members(member_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

9. Common modeling mistakes

The most common mistake in data modeling is turning an attribute into its own entity, even though it has no identity of its own and no relationships of its own to other entities. A book genre such as "novel" or "non-fiction" is usually just an attribute, not its own entity, unless the system needs to manage genres with their own properties such as a description or a responsible editor. The test question: does this concept need its own primary key and its own attributes, or is a single value in a column of the parent entity enough.

A second common mistake is modeling an n:m relationship incorrectly as 1:n, because at the time of modeling only a single example per side is known. A book "mostly" has one author, so the relationship gets modeled as 1:n, until a book with two co-authors shows up and the schema has to be rebuilt afterward. The rule of thumb: for every relationship, explicitly ask whether "exactly one" is really guaranteed by the business domain, or is just the most common case in the current data. A third mistake is leaving foreign key columns without NOT NULL, even though the cardinality in the diagram shows a mandatory relationship, which dilutes the cardinality carefully documented in the ER diagram back out of the final schema.

10. Summary

Data modeling with entity-relationship diagrams deliberately separates the conceptual question "what exists in this business domain and how does it connect" from the technical question "what does the actual table look like". Entities become tables, attributes become columns, relationships become foreign keys, and the cardinality of a relationship decides which side the foreign key ends up on and whether a junction table is needed. Crow's foot notation makes it visible at a glance whether a relationship is optional or mandatory.

The biggest practical benefit of data modeling does not show up in the initial design, but in later requirement changes: a well-maintained ER diagram immediately reveals which cardinality is changing and which tables are affected, instead of that insight only surfacing while debugging a broken query. Anyone who consistently thinks through entities, attributes and relationships before implementation saves considerable migration effort as a system grows.

11. FAQ: Entity-relationship fundamentals of data modeling