Diagramming Relationship Types

Crow’s Foot ERDs express two things on each end of a relationship line: cardinality (max) and modality/participation (min). You read them at the ends nearest the entities:

• Max (cardinality): | = one, < = many
• Min (participation): | = at least one (mandatory), O = zero allowed (optional)

Three-pronged “Crow’s Foot” symbol

The three prongs represent “many.” The opposite end uses | (one) and optionally O (zero) to express minimum participation.

Four relationships shown with Crow’s Foot

Reading and ASCII cues

Max (cardinality): | = one, < = many
Min (participation): | = mandatory, O = optional

Examples:
1:1           |-|
1:N           |-<
0..N          O-<
0..1          O-|
M:N (logical) <->   -- requires a junction table

SQL patterns for each relationship

1) 1:1 (two common implementations)

Shared PK variant (tight coupling):

CREATE TABLE person (
  person_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name      VARCHAR2(100) NOT NULL
);

CREATE TABLE passport (
  person_id NUMBER PRIMARY KEY,                         -- shared PK
  number    VARCHAR2(30) UNIQUE NOT NULL,
  issue_dt  DATE NOT NULL,
  CONSTRAINT fk_passport_person
    FOREIGN KEY (person_id) REFERENCES person(person_id)
);

Unique FK variant (looser coupling):

CREATE TABLE employee (
  emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  last_name  VARCHAR2(60) NOT NULL
);

CREATE TABLE office (
  office_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  emp_id    NUMBER UNIQUE NOT NULL,                     -- enforces 1:1
  room_no   VARCHAR2(20) NOT NULL,
  CONSTRAINT fk_office_employee
    FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
);

2) 1:N (mandatory one on child; optional many on parent)

CREATE TABLE supplier (
  supplier_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  supplier_code VARCHAR2(20) UNIQUE NOT NULL,
  name          VARCHAR2(120) NOT NULL
);

CREATE TABLE product (
  product_id  NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  supplier_id NUMBER NOT NULL,  -- mandatory one
  sku         VARCHAR2(40) UNIQUE NOT NULL,
  title       VARCHAR2(120) NOT NULL,
  CONSTRAINT fk_product_supplier
    FOREIGN KEY (supplier_id) REFERENCES supplier(supplier_id)
);

3–4) M:N unresolved → resolved with junction

CREATE TABLE student (
  stud_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  student_no VARCHAR2(20) UNIQUE NOT NULL,
  last_name  VARCHAR2(60) NOT NULL
);

CREATE TABLE class (
  class_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  class_code VARCHAR2(20) UNIQUE NOT NULL,
  title      VARCHAR2(120) NOT NULL
);

CREATE TABLE student_class (          -- associative entity
  stud_id  NUMBER NOT NULL,
  class_id NUMBER NOT NULL,
  enroll_dt DATE DEFAULT SYSDATE,
  grade     VARCHAR2(2),
  CONSTRAINT pk_student_class PRIMARY KEY (stud_id, class_id),
  CONSTRAINT fk_sc_student FOREIGN KEY (stud_id) REFERENCES student(stud_id),
  CONSTRAINT fk_sc_class   FOREIGN KEY (class_id) REFERENCES class(class_id)
);

Elements of a Crow’s Foot ER diagram

Common pitfalls

• Modeling M:N without an associative entity.
• Confusing identifying with mandatory (identifying affects keys; mandatory affects minimum participation).
• Leaving optionality ambiguous (forgetting the O when zero is allowed).
• Dropping natural UNIQUE constraints when adding surrogate keys.

Explore more symbol details here: Crows Foot Notation.

ER diagram check