ER Modeling: Entities, Attributes, Relationships and Crow's Foot Notation

Version note: The SQL (Structured Query Language) queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10), and the Mermaid diagrams were verified by rendering them with Mermaid 12.3.0.

Guess First: Do These Two Lines Say the Same Thing?

In the previous post we extracted the "customer places order" relationship from the bookshop's requirements. Two people on the team draw this relationship in Mermaid in two different ways:

erDiagram
    CUSTOMER ||..o{ ORDER : places
    CUSTOMER |o..|{ ORDER : places

Both lines produce a valid diagram, and both draw a line between customer and order. Guess: in which one can an order exist without a customer? In which one can a customer not register without placing an order? Does your choice change whether the foreign key column can be NULL?

The answer: in the first line every order has exactly one customer (||), and a customer can have zero or many orders (o{). In the second line an order can be customerless (|o), and a customer cannot exist without placing at least one order (|{). The bookshop's reality is the first one: registration comes before ordering and there are no guest orders. The second line looks harmless on paper, but it lands in the database as orders.customer_id NULL and brings the concept of an "orphan order" into the model. In this post we learn to read such differences from a drawing: entities, attributes, relationships, cardinality and optionality; the Chen and Crow's Foot notations; and how to keep the ER (Entity-Relationship) diagram next to the code.

Entities and Attribute Types

An entity is something we want to keep information about and that can be told apart from others: customer, book, order. An entity instance is one occurrence of it: the customer called Ada Lovelace, the book called Dune. In the ER model, what we write in the box is the entity, and what goes into the table as a row is the instance. Peter Chen set out this split between entities and relationships in his 1976 paper in ACM (Association for Computing Machinery) Transactions on Database Systems.

An attribute is a property of an entity, and four kinds of attributes directly affect modeling decisions.

A simple attribute is a single value that cannot be split: a book's title, a customer's email. A composite attribute is made of meaningful parts: an address, from street, district, city and postal code. Whether you keep the address as one text column or split into its parts depends on whether you will query the parts. If the question "customers in Izmir" is coming, the city has to be a separate attribute.

A multivalued attribute holds several values for a single instance: a customer's phone numbers, a book's tags. In an ER diagram it is drawn as a double ellipse, and when moving to the logical model it usually becomes a separate table. Squeezing a multivalued attribute into one column with commas is exactly the mistake we saw in the books and authors columns of the order spreadsheet, the working material of this series.

A derived attribute can be computed from other attributes: an author's age from the birth year, an order's total from the quantities and prices of its items. In an ER diagram it is drawn as a dashed ellipse. Storing a derived attribute and not storing it are both legitimate decisions; but if you store it, you also have to write down, as part of the model, who updates it when its source changes. This decision comes back as denormalization in the 8th post of the series.

There is also the key attribute: an attribute or set of attributes that on its own tells one entity instance apart. In Chen notation it is underlined. Choosing keys matters so much that we dedicated the 4th post of the series entirely to it.

Relationships and Degree

A relationship is an association between entities and has its own name: a customer places an order, an order contains books. The number of entities taking part in a relationship is called its degree.

A unary (or recursive) relationship is an entity's relationship with itself. The Bookstore example is categories: Science Fiction is a category, Fiction is a category, and one is the parent of the other. A binary relationship is between two entities, and most models are made of these. A ternary relationship connects three entities at once: "This publisher holds the rights to print this book in this country" is a single fact about book, publisher and country. We will see in SQL in the next post what is lost when you try to split a ternary relationship into two binary ones.

A relationship can have attributes too. The quantity of the "contains" relationship between order and book, and the author order of the "writes" relationship between book and author, are such attributes. They belong to neither of the two entities and are not a "repetition" anywhere; they are facts about the relationship itself. In the logical model such relationships become associative entities.

Cardinality and Optionality

Describing a relationship as "1-N" is half the information. Each direction has a minimum and a maximum:

• A customer has at least 0 and at most many orders.
• An order has at least 1 and at most 1 customer.
• An order has at least 1 and at most many items.
• A book has at least 0 and at most 1 category (if there are books not yet classified).

The maximum side is called cardinality, the minimum side optionality (or participation). The maximum decides on which side the relationship is built with a foreign key and when it needs a separate table. The minimum turns directly into a constraint: if an order's customer is at least 1, orders.customer_id becomes NOT NULL; if a book's category is at least 0, books.category_id becomes nullable. The rule "an order has at least one item" is interesting: you cannot express it with a foreign key, because the rule is on the parent side. You protect it in application code, in a trigger, or through a single path that writes the order and its items in the same transaction. A diagram showing the rule does not mean the schema guarantees it on its own.

Chen and Crow's Foot: Two Drawings of the Same Model

Chen notation is the drawing language of Chen's 1976 paper: an entity is a rectangle, a relationship a diamond, an attribute an ellipse. Cardinality is shown with labels such as 1, N and M on the lines, and total participation with a double line. Its strength is conceptual clarity: because the relationship is a shape of its own, the relationship's attribute (quantity above) attaches directly to it, and a ternary relationship is drawn with one diamond. Its weakness is space; in a schema with twenty tables the ellipses become unreadable.

Crow's Foot notation speaks through the symbols at the ends of the lines. The literature usually traces it back to a 1976 conference paper by Gordon Everest; Everest called the symbol an "inverted arrow" at the time, and the "crow's foot" name stuck later. It spread with the Information Engineering method and is the default notation of most modeling tools today. Each line end carries two symbols: the one closer to the entity shows the maximum (a bar = one, a fork = many), the farther one the minimum (a bar = one, a circle = zero). Attributes are listed inside the box, which brings the diagram close to a table structure.

The lower half of the figure shows one more difference: in Chen, "contains" is a relationship and quantity is its attribute. In Crow's Foot, an M-N relationship is often drawn directly with an ORDER_ITEM box, because this notation is closer to the logical model. Both describe the same model; Chen reads more comfortably when you discuss concepts with the business, Crow's Foot when you discuss tables with developers.

The Bookstore's First ER Draft: In Code with Mermaid

The biggest problem with keeping an ER diagram in a drawing tool is that the diagram drifts away from the schema. Mermaid lets you write the diagram as plain text; the file lives in the repository, is diffed in pull requests, and GitHub and GitLab draw Mermaid blocks inside markdown directly. The Bookstore's first draft:

erDiagram
    CUSTOMER ||..o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    BOOK ||..o{ ORDER_ITEM : "sold as"
    AUTHOR }|..|{ BOOK : writes
    CATEGORY ||..o{ BOOK : classifies
    CATEGORY |o..o{ CATEGORY : "parent of"
    CUSTOMER ||..o{ REVIEW : writes
    BOOK ||..o{ REVIEW : receives

    CUSTOMER {
        int customer_id PK
        string email UK
        string first_name
        string last_name
        string phone
        string city
    }
    ORDER {
        int order_id PK
        int customer_id FK
        datetime ordered_at
        string status
    }
    ORDER_ITEM {
        int order_id PK, FK
        int line_no PK
        int book_id FK
        int quantity
        decimal unit_price
    }
    BOOK {
        int book_id PK
        string isbn UK
        string title
        decimal list_price
        int category_id FK
    }
    AUTHOR {
        int author_id PK
        string first_name
        string last_name
        int birth_year
    }
    CATEGORY {
        int category_id PK
        string name UK
        int parent_category_id FK
    }
    REVIEW {
        int review_id PK
        int book_id FK
        int customer_id FK
        int rating
    }

The syntax is the text form of Crow's Foot. Each relationship line has two ends, and each end has two characters: the outer one is the maximum, the inner one the minimum. || exactly one, |o zero or one, o{ zero or many, |{ one or many. The line in the middle carries meaning too: -- is an identifying relationship (solid line), .. a non-identifying relationship (dashed line). Identifying means the child entity's identity includes the parent: the key of ORDER_ITEM is (order_id, line_no), so an item cannot be identified without its order. REVIEW, on the other hand, has its own review_id; it is related to the book, but its identity does not depend on it. The relationship label is mandatory; Mermaid does not parse a relationship line without one.

I deliberately left two decisions in this draft. AUTHOR }|..|{ BOOK is an M-N relationship that has not yet been turned into an associative entity; that is right at the conceptual level, and at the logical level it becomes the book_authors table. CATEGORY ||..o{ BOOK draws R7 from the previous post (every book belongs to a category) as it is. In a moment we will see that the real database says something else about this. I verified with Mermaid 12.3.0 that the diagram parses and renders; on a line with a syntax error, Mermaid reports Parse error on line 2 and tells you which token it expected.

Extracting a Diagram from the Database, and Its Limits

If you already have a working database, you can also generate the ER diagram from it. Most tools do this, but to understand what they see and what they cannot see, let's do the same job with a query. I loaded the Bookstore schema of the SQL Temelleri series into the dm_02 database and generate Mermaid relationship lines from SQL Server's catalog views: if the foreign key column is nullable the parent end is |o, otherwise ||; if the foreign key columns are part of the child's primary key the line is --, otherwise ..; if there is a unique index exactly on the foreign key columns the child end is o|, otherwise o{.

SET NOCOUNT ON;
WITH fk AS (
    SELECT fk.object_id,
           fk.name                              AS fk_name,
           OBJECT_NAME(fk.referenced_object_id) AS parent_table,
           OBJECT_NAME(fk.parent_object_id)     AS child_table,
           fk.parent_object_id,
           MAX(CAST(c.is_nullable AS INT))      AS any_nullable
    FROM sys.foreign_keys AS fk
    JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id = fk.object_id
    JOIN sys.columns AS c ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id
    GROUP BY fk.object_id, fk.name, fk.referenced_object_id, fk.parent_object_id
),
index_flags AS (
    SELECT fk.object_id,
           CASE WHEN i.is_unique = 1 AND NOT EXISTS (
                    SELECT ic.column_id FROM sys.index_columns AS ic
                    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.key_ordinal > 0
                    EXCEPT
                    SELECT fkc.parent_column_id FROM sys.foreign_key_columns AS fkc
                    WHERE fkc.constraint_object_id = fk.object_id)
                THEN 1 ELSE 0 END AS fk_is_unique,
           CASE WHEN i.is_primary_key = 1 AND EXISTS (
                    SELECT 1 FROM sys.index_columns AS ic
                    JOIN sys.foreign_key_columns AS fkc
                      ON fkc.constraint_object_id = fk.object_id AND fkc.parent_column_id = ic.column_id
                    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id)
                THEN 1 ELSE 0 END AS fk_in_pk
    FROM fk
    JOIN sys.indexes AS i ON i.object_id = fk.parent_object_id
)
SELECT CONCAT('    ', fk.parent_table, ' ',
              IIF(fk.any_nullable = 1, '|o', '||'),
              IIF(MAX(f.fk_in_pk) = 1, '--', '..'),
              IIF(MAX(f.fk_is_unique) = 1, 'o|', 'o{'),
              ' ', fk.child_table, ' : "', fk.fk_name, '"') AS mermaid_line
FROM fk
JOIN index_flags AS f ON f.object_id = fk.object_id
GROUP BY fk.parent_table, fk.child_table, fk.fk_name, fk.any_nullable
ORDER BY fk.parent_table, fk.child_table;

mermaid_line
-----------------------------------------------------------
    authors ||--o{ book_authors : "FK_book_authors_authors"
    books ||--o{ book_authors : "FK_book_authors_books"
    books ||..o{ order_items : "FK_order_items_books"
    books ||..o{ reviews : "FK_reviews_books"
    books ||--o| stock_items : "FK_stock_items_books"
    categories |o..o{ books : "FK_books_categories"
    categories |o..o{ categories : "FK_categories_parent"
    customers ||..o{ orders : "FK_orders_customers"
    customers ||..o{ reviews : "FK_reviews_customers"
    orders ||..o{ order_items : "FK_order_items_orders"
    orders ||..o{ payments : "FK_payments_orders"

The output is valid Mermaid, and when rendered it shows the Bookstore's skeleton. It even found the 1-1 relationship between stock_items and books, because the primary key of stock_items is book_id. But look at what was lost.

The child end's minimum is always o. There is no constraint in the database that enforces "every order has at least one item", so the query has no way of knowing it. The draft's ORDER ||--|{ ORDER_ITEM became orders ||..o{ order_items here.

The weak entity disappeared. In the Bookstore schema order_items has its own surrogate key (order_item_id), so the relationship looks non-identifying. Conceptually an item still cannot exist without its order; what says so is no longer the key but ON DELETE CASCADE and the business rule.

M-N relationships turned into two separate 1-N ones: authors ||--o{ book_authors and books ||--o{ book_authors. Someone reading the diagram cannot tell whether book_authors is a concept or a link table.

The categories |o..o{ books line contradicts R7 in the draft. In the real schema books.category_id is nullable and two books have no category. This shows either that the requirement was written incompletely or that the schema does not enforce the rule; the diagram cannot say which, it only makes the difference visible. Whether a nullable foreign key means "unknown" or "none" is one of the topics of the next post.

The relationship names are gone too. Instead of "places", "writes" and "classifies" there are constraint names. Giving constraints consistent names (the "Constraint'ler ve Şema Değişikliği" post of the SQL Temelleri series) pays off here once more, but it does not replace a verb phrase.

So reverse engineering answers the question "What does the schema say right now?", not "What should the model be?" Putting the two side by side and discussing the differences can be the most productive ten minutes of a schema review.

In Production

Have a single source for the diagram. Either the hand-written model is the source and the schema is derived from it, or the schema is the source and the diagram is generated from it on every build, as above. Updating both by hand means one of them will always fall behind. A practical middle ground: keep the conceptual draft by hand in Mermaid, generate the physical diagram from the database in a CI (Continuous Integration) step, and store both in the same folder.

Split the diagram. A single diagram of a fifty-table schema tells nobody anything. Draw separate diagrams for areas such as ordering, catalogue and customers, and show the relationships between areas in a separate overview. This split usually lines up with the bounded context boundaries of the DDD (Domain-Driven Design) series.

Turn optionality into a constraint the moment you see it in the diagram. If an end drawn as || lands in the database as a nullable column, there is a gap between model and schema, and one day that gap shows up in the data.

Agree on a notation within the team. One person drawing in Chen, another in Crow's Foot and a third in UML (Unified Modeling Language) class diagrams in the same repository turns every review into a "What does this line mean?" discussion.

Common Pitfalls

Squeezing a multivalued attribute into one column. phones = '0532..., 0216...' or tags = 'novel,classic' looks practical on day one; with the first "Is there a customer registered with this phone?" question it turns into a table scan with LIKE '%...%', and with the first spelling difference into inconsistent data. The details of this anti-pattern are in the 9th post of the series.

Storing a derived attribute and forgetting to update it. Adding an orders.total_amount column can be a performance decision; but if it is not written down who updates the total, in which transaction, when the items change, that column will lie one day.

Writing the cardinality and skipping the optionality. A relationship drawn as "1-N" says nothing about whether the foreign key will be nullable. Draw the minimum at both ends.

Putting a relationship attribute on the wrong entity. Putting the author order on the authors table assumes the same author is in the same position on every book. If the answer to "Whose fact is this?" involves both entities, the answer is the relationship.

Taking a reverse-engineered diagram for the model. As we saw above, a tool can only draw what the schema says; a rule that is not in the schema is not in the diagram either.

A Small Experiment

★☆☆ Add a PAYMENT entity to the Bookstore draft: an order can have several payments (gift voucher and card), and each payment belongs to exactly one order. Write the relationship line in Mermaid and decide which end gets o and which gets |.

★★☆ Change the reverse-engineering query so that it also writes the foreign key's ON DELETE behaviour (sys.foreign_keys.delete_referential_action_desc) at the end of the relationship line. Which relationships are CASCADE, which NO_ACTION? Does this information help you find weak entities?

★★★ Try to draw a ternary relationship in Mermaid: the "printing rights" between publisher, book and country. Does Mermaid's erDiagram syntax have a shape for a ternary relationship? If not, how would you model it, and does that drawing say the same thing as the single diamond in Chen notation?

We have learned to draw relationships in an ER diagram. In the next post we take these relationships down to tables: building 1-N with a foreign key and M-N with an associative entity, the real reasons for 1-1, self-reference, why a ternary relationship cannot be split into two binary ones, and deriving ON DELETE behaviour from a weak entity.