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 KEY |
| Surrogate 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
);