ER Modeling — MySQL

Entity-Relationship (ER) Modeling

Entity-Relationship (ER) modeling is a technique for visually designing a database before writing a single line of SQL. You define what things (entities) exist in your domain, what facts (attributes) describe them, and how they relate to each other. The resulting ER diagram becomes the blueprint for your table definitions.

Core Concepts: Entities, Attributes, Relationships

Entity: A distinct "thing" in the real world that you need to track data about. Entities become tables. Examples: Customer, Product, Order, Employee.

Attribute: A fact about an entity. Attributes become columns. Examples: Customer.email, Product.price, Order.created_at.

Relationship: An association between two entities. Relationships become foreign keys, or junction tables for M:N cases.

Primary Key Attribute: The attribute (or combination) that uniquely identifies each instance. Shown underlined in ER diagrams.

Cardinality Notation — Crow's Foot

Cardinality describes how many instances of one entity can be related to instances of another. The crow's foot notation (used by MySQL Workbench and most modern tools) uses symbols at each end of a relationship line:

Symbol (at line end)	Meaning
\| (single line)	Exactly one (mandatory)
O (circle)	Zero (optional)
< (crow foot)	Many
O< (circle + crow foot)	Zero or many (0..*)
\|< (line + crow foot)	One or many (1..*)
\|\| (double line)	Exactly one (both sides mandatory)
O\| (circle + line)	Zero or one (0..1)

Reading a relationship: look at both ends. A Customer ||——O< Order means: "A customer must have exactly one side, and an order must belong to exactly one customer; a customer can have zero or many orders."

Strong vs Weak Entities

A strong entity exists independently — it has its own PK that does not depend on any other entity. Most entities are strong.

A weak entity cannot exist without a related strong entity, and its PK is partially or fully borrowed from the strong entity. Example: an OrderItem has no meaning without an Order.

SQL

-- Strong entity: Order exists independently
CREATE TABLE orders (
  id         INT NOT NULL AUTO_INCREMENT,
  created_at DATETIME,
  PRIMARY KEY (id)    -- standalone PK
);

-- Weak entity: OrderItem depends on Order
CREATE TABLE order_items (
  order_id   INT NOT NULL,  -- borrowed from Order (partial PK)
  item_num   INT NOT NULL,  -- position within the order
  product_id INT NOT NULL,
  quantity   INT NOT NULL,
  PRIMARY KEY (order_id, item_num),  -- composite PK includes parent's PK
  FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
);

Identifying vs Non-Identifying Relationships

Identifying relationship: The child entity's PK includes the parent's PK. The child cannot exist without the parent. Shown as a solid line in ER tools. Example: OrderItem identified by (order_id, item_num).

Non-identifying relationship: The child entity has its own independent PK. The FK is just a reference, not part of the PK. Shown as a dashed line in ER tools. Example: Order references Customer via customer_id, but Order has its own PK.

Feature	Identifying	Non-Identifying
Child PK	Contains parent PK	Independent of parent PK
Child existence	Requires parent	Can exist without parent (FK nullable)
ER diagram line	Solid line	Dashed line
SQL FK column	Part of PRIMARY KEY	Separate column
Example	order_items.order_id	orders.customer_id

Translating an ER Diagram to Tables

The translation rules are straightforward:

Each strong entity becomes a table; attributes become columns; the PK attribute becomes the PRIMARY KEY.
Each weak entity becomes a table with a composite PK (its own attributes + the parent PK).
Each 1:N relationship adds a FK column on the "many" (child) side.
Each 1:1 relationship adds a FK on whichever side is optional, with a UNIQUE constraint.
Each M:N relationship becomes a junction table with FKs to both sides.
Multi-valued attributes (an entity with multiple phone numbers) become a separate table.

Practical Blog ER Design

Let's design a blog system from scratch using ER thinking, then translate it to SQL.

Entities: User, Post, Comment, Tag, Category Relationships:

User writes many Posts (1:N)
Post has many Comments (1:N)
Comment written by a User (1:N)
Post belongs to one Category (1:N)
Post tagged with many Tags; Tag applied to many Posts (M:N)

SQL

-- Entity: User
CREATE TABLE users (
  id            INT          NOT NULL AUTO_INCREMENT,
  username      VARCHAR(50)  NOT NULL UNIQUE,
  email         VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
);

-- Entity: Category (self-referential for sub-categories)
CREATE TABLE categories (
  id        INT          NOT NULL AUTO_INCREMENT,
  name      VARCHAR(100) NOT NULL,
  slug      VARCHAR(100) NOT NULL UNIQUE,
  parent_id INT          DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE SET NULL
);

-- Entity: Post (references User and Category)
CREATE TABLE posts (
  id          INT          NOT NULL AUTO_INCREMENT,
  title       VARCHAR(255) NOT NULL,
  slug        VARCHAR(255) NOT NULL UNIQUE,
  body        MEDIUMTEXT   NOT NULL,
  author_id   INT          NOT NULL,
  category_id INT          DEFAULT NULL,
  status      VARCHAR(20)  NOT NULL DEFAULT 'draft',
  published_at DATETIME    DEFAULT NULL,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (author_id)   REFERENCES users      (id) ON DELETE RESTRICT,
  FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL,
  INDEX idx_author   (author_id),
  INDEX idx_category (category_id),
  INDEX idx_status_published (status, published_at)
);

-- Weak entity: Comment (depends on Post)
CREATE TABLE comments (
  id         INT      NOT NULL AUTO_INCREMENT,
  post_id    INT      NOT NULL,
  author_id  INT      NOT NULL,
  body       TEXT     NOT NULL,
  parent_id  INT      DEFAULT NULL,  -- self-referential for threaded replies
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (post_id)   REFERENCES posts    (id) ON DELETE CASCADE,
  FOREIGN KEY (author_id) REFERENCES users    (id) ON DELETE RESTRICT,
  FOREIGN KEY (parent_id) REFERENCES comments (id) ON DELETE CASCADE,
  INDEX idx_post   (post_id),
  INDEX idx_author (author_id)
);

-- Entity: Tag
CREATE TABLE tags (
  id   INT         NOT NULL AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  slug VARCHAR(50) NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

-- Junction table: Post <-> Tag (M:N)
CREATE TABLE post_tags (
  post_id INT NOT NULL,
  tag_id  INT NOT NULL,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
  FOREIGN KEY (tag_id)  REFERENCES tags  (id) ON DELETE CASCADE
);

Practical E-Commerce ER Design

SQL

-- Simplified e-commerce ER -> SQL

CREATE TABLE customers (
  id    INT          NOT NULL AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  name  VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE addresses (
  id          INT          NOT NULL AUTO_INCREMENT,
  customer_id INT          NOT NULL,
  line1       VARCHAR(255) NOT NULL,
  city        VARCHAR(100) NOT NULL,
  country     CHAR(2)      NOT NULL,
  is_default  TINYINT(1)   NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE
);

CREATE TABLE products (
  id          INT            NOT NULL AUTO_INCREMENT,
  sku         VARCHAR(50)    NOT NULL UNIQUE,
  name        VARCHAR(255)   NOT NULL,
  price       DECIMAL(10, 2) NOT NULL,
  stock_qty   INT            NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

CREATE TABLE orders (
  id              INT            NOT NULL AUTO_INCREMENT,
  customer_id     INT            NOT NULL,
  shipping_addr   INT            NOT NULL,
  status          VARCHAR(20)    NOT NULL DEFAULT 'pending',
  subtotal        DECIMAL(10, 2) NOT NULL DEFAULT 0,
  shipping_cost   DECIMAL(10, 2) NOT NULL DEFAULT 0,
  total           DECIMAL(10, 2) NOT NULL DEFAULT 0,
  created_at      DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id)   REFERENCES customers (id) ON DELETE RESTRICT,
  FOREIGN KEY (shipping_addr) REFERENCES addresses (id) ON DELETE RESTRICT
);

CREATE TABLE order_items (
  order_id   INT            NOT NULL,
  product_id INT            NOT NULL,
  quantity   INT            NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id)   REFERENCES orders   (id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT
);

MySQL Workbench EER Diagram

MySQL Workbench includes an Enhanced Entity-Relationship (EER) diagram tool that lets you design your schema visually and then generate the SQL DDL automatically.

Open MySQL Workbench and choose "Create EER Model".
Drag tables from the palette onto the canvas.
Double-click a table to add columns, set types, and define the PK.
Use the relationship tools (1:N, M:N) to draw connections between tables.
Right-click the canvas and choose "Forward Engineer" to generate CREATE TABLE SQL.
Use "Reverse Engineer" to generate an EER diagram from an existing database.

Bash

# Forward engineer from MySQL Workbench (CLI equivalent)
# Database > Forward Engineer... -> generates CREATE TABLE statements

# Or use mysqldump to get the DDL from an existing database:
mysqldump --no-data --routines mydb > schema.sql

Tip

When working with a team, keep your EER diagram in version control (MySQL Workbench saves as a .mwb file). This gives you a visual history of schema changes alongside your SQL migration files.

ER Modeling Best Practices

Name entities as singular nouns: Customer, not Customers.
Name relationships with a verb: Customer PLACES Order.
Identify all M:N relationships early — they each need a junction table.
Document cardinality constraints on every relationship line.
Design for the queries you will run, not just data storage.
Keep the ER diagram updated as the schema evolves — stale diagrams mislead.
Use UNIQUE constraints to enforce 1:1 relationships at the database level.
Add indexes to FK columns from the start — do not wait for slow queries.