Entity Relationship Modeling

Entity-Relationship Modeling

Concept of the skill

Use when designing database tables, reviewing schema changes, planning migrations, or translating conceptual models into physical database structures.

Domain Context

What is this skill? This skill provides entity-relationship (ER) modeling patterns for designing database schemas from domain requirements: Chen notation and Crow's Foot notation, entity identification and attribute analysis, primary/foreign key design, normalization (1NF through BCNF), denormalization trade-offs, junction table patterns, inheritance mapping strategies (single-table, class-table, concrete-table), temporal data modeling, and schema evolution/migration patterns. Covers the ER-to-SQL translation pipeline, indexing strategy from access patterns, constraint specification (NOT NULL, UNIQUE, CHECK, FK), and anti-patterns like EAV abuse, polymorphic associations, and over-normalization. Use when designing new database tables, reviewing schema changes, planning migrations, or translating conceptual models into physical database structures. Do NOT use for conceptual domain analysis (use conceptual-modeling), formal ontology (use ontology), or cross-system data mapping (use relational-mapping).

Coverage

Entity-relationship modeling for database schema design: Chen notation and Crow's Foot notation, entity identification and attribute analysis, primary/foreign key design (natural vs. surrogate, UUID vs. serial), normalization forms (1NF through BCNF) with trade-off analysis, denormalization patterns for read performance, junction table design for M:N relationships, inheritance mapping strategies (single-table, class-table, concrete-table), temporal data modeling (SCD Type 1/2/3, bi-temporal), schema evolution and migration patterns, ER-to-SQL translation, indexing strategy from access patterns, constraint specification (NOT NULL, UNIQUE, CHECK, FK, EXCLUDE), and anti-patterns (EAV, polymorphic associations, over-normalization, mega-tables). Does not cover conceptual domain analysis (conceptual-modeling), formal ontology (ontology), or cross-system data mapping (relational-mapping).

Philosophy of the skill

A database schema is a commitment about what the business considers true. Every table is a claim that a category of things exists; every foreign key is a claim that two categories are related; every constraint is a claim about what the business considers valid. Bad ER design does not just cause slow queries — it causes business logic bugs, data integrity violations, and migration nightmares. This skill exists because agents commonly produce schemas that "work" for the happy path but fail under real-world conditions: concurrent updates, schema evolution, multi-tenancy, and audit requirements. The goal is schemas that are correct first, performant second, and evolvable always.

1. Entity Identification

What Makes a Good Entity

Entity vs. Attribute vs. Relationship

2. Primary Key Design

For philosophical identity questions (what makes two entities "the same"), see ontology. This section covers the database implementation of those decisions.

Rules:

• Default to UUID v7 for new tables in multi-tenant SaaS (sortable, no sequence leakage, distributed-safe).
• Never expose serial IDs externally (information leakage: competitor can count your orders).
• Natural keys are tempting but dangerous — "immutable" business identifiers change more often than you think.

3. Relationship Patterns

Cardinality Implementation

Junction Table Design

-- Simple M:N
CREATE TABLE product_categories (
  product_id    UUID REFERENCES products(id) ON DELETE CASCADE,
  category_id   UUID REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, category_id)
);

-- M:N with attributes (promoted to entity)
CREATE TABLE order_line_items (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id      UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id    UUID NOT NULL REFERENCES products(id),
  quantity      INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents >= 0),
  created_at    TIMESTAMPTZ DEFAULT now()
);

4. Normalization

Rules:

• Normalize to 3NF by default for transactional tables.
• Denormalize deliberately for read-heavy analytics with documented justification.
• Never skip normalization analysis — even if you plan to denormalize, understanding the normal form reveals the true dependencies.

5. Denormalization Patterns

Rules:

• Every denormalization must document: what is denormalized, why, and how consistency is maintained.
• Prefer materialized views (DB-maintained) over application-level redundancy (app-maintained).
• JSON columns are not a substitute for proper entity design; use only for genuinely flexible/unstructured data.

6. Inheritance Mapping

Decision Matrix

Rules:

• Default to single-table inheritance for <= 3 subtypes with mostly shared attributes.
• Switch to class-table when subtypes diverge significantly (many subtype-specific columns).
• Concrete-table only when subtypes are operationally independent and never queried together.

7. Temporal Data Modeling

Rules:

• Financial data requires at minimum SCD Type 2 (full audit trail).
• Use valid_to IS NULL or a boolean is_current flag for efficient current-value queries.
• Bi-temporal modeling is necessary when you need to answer "what did we think was true on date X about date Y?"

8. Constraint Specification

Rules:

• Push validation to the database whenever possible. Application-level validation can be bypassed; DB constraints cannot.
• Every financial amount column needs CHECK (amount >= 0) or explicit handling of negatives.
• Prefer ON DELETE CASCADE for composition (line items); ON DELETE RESTRICT for association (customer has orders).

9. Anti-Patterns

Verification

Scope note: This checklist covers the implementation (ER) layer — primary keys, foreign keys, normalization, and index strategy. For relationship-level verification (named associations, semantic cardinality), use [conceptual-modeling]. For axiom-level verification (formal class definitions, property domains/ranges), use [ontology].

After applying this skill, verify:

• Primary keys are defined for every entity with an explicit strategy (UUID v7 preferred for new tables)
• Foreign keys correctly reference parent PKs with explicit ON DELETE behavior (CASCADE for composition, RESTRICT for association)
• Normalization level is documented (3NF for OLTP default; any denormalization is justified and documented)
• Index strategy is documented — at minimum, covering PKs, FK columns, and query predicates for hot paths
• Financial columns have CHECK constraints for valid ranges (amount >= 0 or explicit negatives handling)
• Temporal data has the appropriate SCD type for audit requirements
• No EAV or polymorphic association patterns without explicit justification

Do NOT Use When

Version 1.0.0 — 2026-03-29. Initial creation.

Skill Graph context

Classification

• Subject: software-architecture
• Public: true
• Domain: engineering/modeling
• Scope: Use when designing database tables, reviewing schema changes, planning migrations, or translating conceptual models into physical database structures. Covers ER notation, entity/attribute/key design, normalization and denormalization, junction tables, inheritance mapping, temporal modeling, ER-to-SQL translation, indexing, and constraints. Do NOT use for conceptual domain analysis (use conceptual-modeling), formal ontology (use ontology), or cross-system API contracts (use system-interface-contracts).

When to use

• Triggers: er-modeling-skill, database-design-skill

Related skills

• Verify with: code-review, data-modeling-fundamentals
• Related: entity-relationship-modeling, database-migration

Keywords

• entity relationship, ER diagram, ER model, database design, schema design, normalization, foreign key, primary key, junction table, database modeling