You Have Been Reading ERD Notation Wrong

If you have ever designed a database schema, you have probably drawn an ERD with crow’s foot notation. You learned that a circle means optional, a line means mandatory, and a crow’s foot means many. You felt confident.

Then you opened your SQL editor and had to guess whether the FK should be NULL or NOT NULL. You were not sure if you needed UNIQUE. And nobody told you that one of the notation symbols cannot be enforced in SQL at all.

This article fixes that. We will cover what each symbol actually means, how to translate notation directly into SQL constraints, and where the notation is richer than what your database can enforce.

Throughout this article, we use one consistent convention: parent entity is always on the left, child entity is always on the right. This makes every notation example unambiguous.

A Brief History

The crow’s foot symbol was introduced by Gordon Everest in his 1976 paper “Basic Data Structure Models Explained with a Common Example.” Everest used an “inverted arrow” to represent the one-to-many relationship in hierarchical and network data structures, noting that it “inherently and visually depicts the one-to-many relationship” without implying a direction of access.

At that stage, the notation only expressed maximum degree: whether a link could have one or many instances. The minimum degree symbols (the circle and the additional line that express zero vs. one) were added later by other authors and tools as the notation evolved into what we now call crow’s foot or Information Engineering (IE) notation.

This origin matters for one reason: the symbol that most engineers obsess over - the minimum degree symbol - was not even part of the original notation. As we will see, there is a strong argument that it still should not be.

The Rule Most Tutorials Miss

Before explaining any symbol, there is one reading rule that almost every tutorial skips:

Symbols are read from the entity outward, in pairs. The symbols closest to an entity describe the constraints on that entity.

This is the part that trips most engineers up. The symbols on the left side of the line describe the left entity. The symbols on the right side describe the right entity. Not the other way around.

[users] || ----- o< [blogs]
         ↑       ↑
   read from   read from
   users out   blogs out

• || near users = a blog must have exactly one user (FK NOT NULL)
• o< near blogs = a user can have zero or many blogs

With that rule clear, everything else follows.

The Symbols

Crow’s foot notation uses three base symbols:

Each end of a relationship line uses a pair of symbols, read left to right as they visually appear. The symbol closest to the entity is the maximum. The symbol closest to the line is the minimum.

All Valid Combinations

Parent side (left)

Child side (right)

That is the complete set. Four combinations on the child side, two on the parent side. No others exist.

Notation to SQL: The Complete Mapping

Let us use a concrete domain: users, profiles, avatars, blogs, addresses, and attachments.

1. users and profiles - ||——||

A user must always have exactly one profile. A profile must always belong to exactly one user.

[users] ||——|| [profiles]

CREATE TABLE "profiles" (
  "id" UUID DEFAULT gen_random_uuid(),
  "user_id" UUID NOT NULL,
  "display_name" TEXT NOT NULL DEFAULT '',
  "bio" TEXT NOT NULL DEFAULT '',
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "profiles" ADD CONSTRAINT "profiles_pkey" PRIMARY KEY ("id");
ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_key" UNIQUE ("user_id");
ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id");

• NOT NULL enforces that a profile must belong to a user
• UNIQUE enforces that a user can have at most one profile
• “A user must have a profile” is not enforced by SQL

2. users and avatars - ||——o|

A user can have zero or one avatar. If an avatar exists, it must belong to exactly one user.

[users] ||——o| [avatars]

CREATE TABLE "avatars" (
  "id" UUID DEFAULT gen_random_uuid(),
  "user_id" UUID NOT NULL,
  "url" TEXT NOT NULL,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "avatars" ADD CONSTRAINT "avatars_pkey" PRIMARY KEY ("id");
ALTER TABLE "avatars" ADD CONSTRAINT "avatars_user_id_key" UNIQUE ("user_id");
ALTER TABLE "avatars" ADD CONSTRAINT "avatars_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id");

• NOT NULL enforces the avatar must belong to a user
• UNIQUE still enforces at most one avatar per user
• Note: in SQL, NULL values are not considered equal, so multiple NULL values are allowed even with a UNIQUE constraint

3. users and blogs - ||——o<

A user can have zero or many blogs. A blog must belong to exactly one user.

[users] ||——o< [blogs]

CREATE TABLE "blogs" (
  "id" UUID DEFAULT gen_random_uuid(),
  "user_id" UUID NOT NULL,
  "slug" TEXT NOT NULL,
  "title" TEXT NOT NULL,
  "description" TEXT NOT NULL,
  "content" TEXT NOT NULL DEFAULT '',
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "blogs" ADD CONSTRAINT "blogs_pkey" PRIMARY KEY ("id");
ALTER TABLE "blogs" ADD CONSTRAINT "blogs_slug_key" UNIQUE ("slug");
ALTER TABLE "blogs" ADD CONSTRAINT "blogs_title_key" UNIQUE ("title");
ALTER TABLE "blogs" ADD CONSTRAINT "blogs_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id");

• NOT NULL enforces that every blog must have an owner
• No UNIQUE because one user can have many blogs
• This is the most common pattern in relational schemas

4. users and addresses - ||——|<

A user must have at least one address. An address must belong to exactly one user.

[users] ||——|< [addresses]

CREATE TABLE "addresses" (
  "id" UUID DEFAULT gen_random_uuid(),
  "user_id" UUID NOT NULL,
  "line_1" TEXT NOT NULL,
  "line_2" TEXT NOT NULL DEFAULT '',
  "postcode" TEXT NOT NULL,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "addresses" ADD CONSTRAINT "addresses_pkey" PRIMARY KEY ("id");
ALTER TABLE "addresses" ADD CONSTRAINT "addresses_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users" ("id");

• NOT NULL enforces that every address must belong to a user
• “A user must have at least one address” is not enforced by SQL

5. blogs and attachments - |o——o<

An attachment can optionally belong to a blog. A blog can have zero or many attachments.

[blogs] |o——o< [attachments]

CREATE TABLE "attachments" (
  "id" UUID DEFAULT gen_random_uuid(),
  "blog_id" UUID DEFAULT NULL,
  "url" TEXT NOT NULL,
  "created_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_pkey" PRIMARY KEY ("id");
ALTER TABLE "attachments" ADD CONSTRAINT "attachments_blog_id_fkey" FOREIGN KEY ("blog_id") REFERENCES "blogs" ("id");

• |o near blogs = FK is nullable, an attachment can exist without a blog
• This is the nullable FK case that most tutorials never explain

The Full Mapping Table

The Gap: What SQL Cannot Enforce

Here is the part that most tutorials never tell you.

Two of the six notation cases produce identical SQL but mean different things:

The SQL is the same. The |< minimum of one is purely a business rule. SQL has no constraint that says “this parent must have at least one child row.”

Similarly, ||——|| (user must have exactly one profile) is only half-enforced. The FK NOT NULL UNIQUE ensures every profile has a user and each user has at most one profile. But nothing in SQL prevents you from creating a user with no profile at all.

The unenforced cases

In both cases, the gap must be handled at the application level. This is intentional and correct.

Why this gap exists by design

This is not a flaw in crow’s foot notation. It is a deliberate design decision.

Carlis and Maguire, in their book Mastering Data Modeling (Addison-Wesley, 2001), argue explicitly against capturing minimum degree constraints on a data model diagram. Their reasoning is direct: minimum degree constraints “are so often false or are a function of processing” rather than a true property of the data itself.

They give a concrete example: a hospital clerk reports that “every patient has at least one diagnosis code.” This looks like a valid minimum constraint. But it is false - a patient admitted to the emergency room has no diagnosis code yet. The constraint only holds at a specific stage of the process, not at all times. It is a processing rule, not a data rule.

The same logic applies to your schema. “A user must have at least one address” is almost certainly a processing rule - enforced when a user checks out, or when an order is submitted, not at the moment the user row is created. SQL enforcing it at the database level would break every user registration flow that creates the user first and collects the address later.

Summary

Crow’s foot notation is more expressive than SQL. That is not a flaw, it is by design. The notation captures business intent. SQL enforces what it can. Your application handles the rest.

References

• Everest, G. C. (1976). “Basic Data Structure Models Explained with a Common Example.” Proceedings of the Fifth Texas Conference on Computing Systems, pages 39-46. IEEE Computer Society.
• Carlis, J. and Maguire, J. (2001). Mastering Data Modeling: A User-Driven Approach. Addison-Wesley.