Designing Entity-Relationship Models
1. Identifying Entities and Attributes
Concept Definition Example Entity Real-world object/concept stored as table Customer, Order, Product Entity Set Collection of similar entities All customers Attribute Property describing an entity name, email, price Simple Attribute Atomic, indivisible age, status Composite Attribute Decomposable into sub-parts address (street, city, zip) Derived Attribute Computed from others age from birthdate Multi-valued Multiple values per entity phone_numbers
2. Defining Primary Keys
Key Type Description Example Primary Key Unique + NOT NULL identifier id BIGSERIAL PRIMARY KEYSurrogate Key System-generated (auto-increment, UUID) UUID DEFAULT gen_random_uuid()Natural Key Real-world unique attribute SSN, ISBN, email Composite Key Multiple columns combined PRIMARY KEY(order_id, line_no)Candidate Key Any valid unique identifier email OR phone OR id Alternate Key Candidate not chosen as PK email (when id is PK)
Note: Prefer surrogate keys (UUID v7 or BIGSERIAL) for stability — natural keys can change.
3. Establishing Relationships
Relationship Cardinality Implementation One-to-One (1:1) Each A maps to one B FK with UNIQUE constraint One-to-Many (1:N) Each A relates to many B FK on the many side Many-to-Many (M:N) Many A to many B Junction/associative table Unary (Recursive) Entity relates to itself FK referencing same table
Example: M:N via Junction Table
CREATE TABLE students (id BIGSERIAL PRIMARY KEY , name TEXT ); CREATE TABLE courses (id BIGSERIAL PRIMARY KEY , title TEXT ); CREATE TABLE enrollments ( student_id BIGINT REFERENCES students(id) ON DELETE CASCADE , course_id BIGINT REFERENCES courses(id) ON DELETE CASCADE , enrolled_at TIMESTAMPTZ DEFAULT NOW (), PRIMARY KEY (student_id, course_id) );
4. Understanding Cardinality and Participation
Notation Meaning 1..1 Exactly one (mandatory) 0..1 Zero or one (optional) 1..* One or more (mandatory many) 0..* Zero or more (optional many) Total Participation Every entity must participate (double line) Partial Participation Some entities may not participate (single line)
5. Creating ER Diagrams
Symbol Meaning Rectangle Entity Double Rectangle Weak entity Diamond Relationship Oval Attribute Underlined Oval Primary key attribute Dashed Oval Derived attribute Double Oval Multi-valued attribute Crow's Foot Many side of relationship
┌──────────┐ ┌──────────┐ ┌──────────┐
│ Customer │1───────∞│ Order │∞───────∞│ Product │
└──────────┘ places └──────────┘contains └──────────┘
│ id (PK) │ id (PK) │ id (PK)
│ email │ customer_id (FK) │ name
│ name │ placed_at │ price
6. Handling Weak Entities and Identifying Relationships
Concept Description Weak Entity Has no PK of its own; depends on owner Identifying Relationship Connects weak entity to its owner Partial Key (Discriminator) Distinguishes weak entity within owner Composite PK owner_pk + partial_key
Example: Weak Entity (OrderItem)
CREATE TABLE order_items ( order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE , line_no INT NOT NULL , -- partial key product_id BIGINT NOT NULL REFERENCES products(id), qty INT NOT NULL , PRIMARY KEY (order_id, line_no) -- composite identifier );
7. Modeling Recursive Relationships
Pattern Example Self-FK employee.manager_id → employee.id Bill of Materials part contains parts (M:N self) Category Tree category.parent_id → category.id
Example: Employee-Manager
CREATE TABLE employees ( id BIGSERIAL PRIMARY KEY , name TEXT NOT NULL , manager_id BIGINT REFERENCES employees(id) );
8. Using Associative Entities
When to Use Reason M:N with attributes Junction table needs columns (e.g., enrolled_at, grade) Ternary relationships 3+ entities participate (Doctor-Patient-Hospital) Historical tracking Each association needs timestamp/audit
9. Defining Attribute Types
Type Description Example Simple Atomic value age, status Composite Decomposable full_name → first/last Single-valued One value per entity birth_date Multi-valued Multiple values phones, tags Stored Persisted on disk price Derived Computed total = price * qty Key Attribute Uniquely identifies entity id, ssn
10. Modeling Generalization and Specialization
Strategy Tables Trade-off Single Table Inheritance One table + type column Simple, but many NULLs Class Table Inheritance Parent + child tables with FK Normalized, requires joins Concrete Table Inheritance One table per concrete subclass No joins, schema duplication Generalization Bottom-up: combine subclasses Find common attributes Specialization Top-down: split into subclasses Distinguish by role/type
Example: Class Table Inheritance
CREATE TABLE person ( id BIGSERIAL PRIMARY KEY , name TEXT NOT NULL , email TEXT UNIQUE ); CREATE TABLE employee ( id BIGINT PRIMARY KEY REFERENCES person(id) ON DELETE CASCADE , salary NUMERIC ( 10 , 2 ) ); CREATE TABLE customer ( id BIGINT PRIMARY KEY REFERENCES person(id) ON DELETE CASCADE , loyalty_pts INT DEFAULT 0 );