MENU


Entity Relationship Modeling

An entity-relationship (ER) model is a conceptual blueprint of a database, drawn before any CREATE TABLE statement is written. It describes the things worth tracking (entities), the facts recorded about each one (attributes), and how they connect (relationships). MySQL has no notion of an ER diagram — the model exists purely as a design tool that is then translated into tables, columns, and foreign keys.

Entities and Attributes

An entity is a distinguishable object the database needs to store facts about — a customer, a product, an order. Each entity becomes a table. An attribute is a property of an entity — a customer's email, a product's price — and each attribute becomes a column.

Simple attributeHolds a single indivisible value, e.g. price. Maps directly to one column.
Composite attributeCan be split into parts, e.g. name into first_name / last_name. Usually modeled as separate columns rather than one.
Derived attributeComputed from other attributes, e.g. age from date_of_birth. Usually not stored at all — computed at query time or in a GENERATED column instead.
Multi-valued attributeCan hold more than one value at once, e.g. a customer having several phone numbers. Never becomes a single column — it becomes its own table (see First Normal Form).

Every entity needs a key attribute — one or more attributes that uniquely identify a row. This becomes the table's primary key; see Surrogate vs. Natural Keys for choosing between a natural business key and a generated one.

Relationships and Cardinality

A relationship connects two (or occasionally more) entities. Every relationship has a cardinality describing how many rows on each side can participate:

1:1One row in A relates to at most one row in B, and vice versa. Example: one employee has one company laptop.
1:NOne row in A relates to many rows in B, but each row in B relates to exactly one row in A. Example: one author writes many blog posts.
N:MMany rows in A relate to many rows in B, and vice versa. Example: many blog posts share many tags.

ER Diagram Notation

Entities are drawn as rectangles, attributes as their listed fields (the primary key underlined), and relationships as lines connecting entities. The most common convention for cardinality is crow's foot notation:

A single tick mark across the line"exactly one" on that side.
A crow's foot (three-pronged fork)"many" on that side.
A small circle before the tick/foot"zero" is allowed (optional participation), as opposed to a mandatory minimum of one.

A 1:N relationship is drawn with a single tick at the "one" entity and a crow's foot at the "many" entity. An N:M relationship has a crow's foot at both ends, which is exactly the case a relational database cannot store directly on either table — it needs a junction table (below).

Translating an ER Model into MySQL Tables

Strong entity — becomes a table. Its key attribute(s) become the PRIMARY KEY.
1:N relationship — add a foreign key column on the "many" side's table, referencing the primary key of the "one" side's table. No extra table is needed.
N:M relationship — create a separate junction table (also called a bridge or associative table) holding a foreign key to each side. Its primary key is normally the composite of both foreign keys, unless the relationship itself carries extra attributes that need their own surrogate key.
1:1 relationship — put a foreign key with a UNIQUE constraint on either side (conventionally the optional or dependent side); if one side is always present and the two entities are always created/deleted together, merging them into a single table is often simpler.

Column data types for keys should match exactly on both sides of a foreign key (see Data Types); indexes on foreign key columns are covered in Indexes, and the full CREATE TABLE syntax including FOREIGN KEY clauses is covered in Table Definitions.

Worked Example: a Blog Schema

A small blogging platform has these entities and relationships:


Applying the translation rules above gives the schema in the worked example below: authors and tags as independent entity tables, posts holding a foreign key to authors for the 1:N relationship, comments holding a foreign key to posts for its own 1:N relationship, and post_tags as the junction table for the N:M relationship between posts and tags, with a composite primary key preventing the same tag from being attached to the same post twice. 

-- Strong entities

CREATE TABLE authors (
    author_id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    display_name  VARCHAR(80)  NOT NULL,
    email         VARCHAR(255) NOT NULL,
    UNIQUE KEY uq_authors_email (email)
) ENGINE=InnoDB;

CREATE TABLE tags (
    tag_id   SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    UNIQUE KEY uq_tags_name (name)
) ENGINE=InnoDB;

-- "Many" side of authors (1) -- (N) posts

CREATE TABLE posts (
    post_id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_id    INT UNSIGNED NOT NULL,
    title        VARCHAR(200) NOT NULL,
    body         MEDIUMTEXT   NOT NULL,
    published_at DATETIME     NULL,
    CONSTRAINT fk_posts_author
        FOREIGN KEY (author_id) REFERENCES authors (author_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE INDEX idx_posts_author_id ON posts (author_id);

-- "Many" side of posts (1) -- (N) comments

CREATE TABLE comments (
    comment_id  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    post_id     INT UNSIGNED NOT NULL,
    author_name VARCHAR(80)  NOT NULL,
    body        TEXT         NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_comments_post
        FOREIGN KEY (post_id) REFERENCES posts (post_id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_comments_post_id ON comments (post_id);

-- Junction table for posts (N) -- (M) tags

CREATE TABLE post_tags (
    post_id  INT UNSIGNED     NOT NULL,
    tag_id   SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    CONSTRAINT fk_post_tags_post
        FOREIGN KEY (post_id) REFERENCES posts (post_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_post_tags_tag
        FOREIGN KEY (tag_id) REFERENCES tags (tag_id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- Every tag attached to every post, with author and post title
SELECT p.title, a.display_name AS author, t.name AS tag
FROM posts p
JOIN authors a  ON a.author_id = p.author_id
JOIN post_tags pt ON pt.post_id = p.post_id
JOIN tags t     ON t.tag_id = pt.tag_id
ORDER BY p.title, t.name;