Weak Entities

Master entities that depend on other entities for their existence and learn how to design composite keys that make them work properly.

What Makes an Entity "Weak"?

Some entities cannot exist alone. They need another entity to give them meaning. Think of it this way: a room number means nothing without the building it belongs to. Room "101" could be anywhere. But "Room 101 in Building A" has clear identity.

This is exactly what happens with weak entities. They depend on a strong entity (also called the owner entity) for their existence and identification.

Why does BookVault need weak entities? Think about book reviews. A review by itself has no meaning. Review ID #5 tells us nothing. But "Review #5 for Book #42" gives us complete context. The review depends on the book for its identity.

BookVault Example: Order Items

The perfect weak entity example lives right in our BookVault schema. Order items cannot exist without orders. What does "item #3" mean? Nothing useful. But "item #3 in order #1001" tells the complete story.

Notice the dashed border around ORDER_ITEMS? That's the standard ER diagram symbol for weak entities. And see those two PK attributes? That's a composite primary key - both columns together create unique identification.

The Dependency Relationship

Order items have what we call an identifying relationship with orders. Delete an order, and all its items must disappear too. This isn't just good practice - it's logically required.

Amazon works exactly this way. When you cancel an order, every line item vanishes with it. The items cannot exist independently.

Composite Keys: The Heart of Weak Entities

Weak entities use composite primary keys by necessity. Here's why: the weak entity needs part of its owner's key, plus something unique within that context.

For BookVault order items, the composite key combines order_id (from the owner) plus book_id (unique within each order). Together, they create perfect identification.

-- Creating the weak entity with composite primary key
-- Reference: BookVault order_items table
CREATE TABLE order_items (
    order_id INTEGER NOT NULL,
    book_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    
    -- Composite primary key from both columns
    PRIMARY KEY (order_id, book_id),
    
    -- Foreign key to the strong entity (orders)
    FOREIGN KEY (order_id) REFERENCES orders(order_id) 
        ON DELETE CASCADE,
    
    -- Regular foreign key to books
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

That ON DELETE CASCADE is crucial. When an order gets deleted, its items automatically disappear. No orphaned records, no broken relationships.

Why Not Use order_item_id?

You might wonder: "Why not just add an order_item_id column?" That would make it a strong entity, but we'd lose the semantic meaning. The composite key tells us exactly what this record represents: a specific book within a specific order.

More BookVault Weak Entities

BookVault has several other potential weak entities. Each one depends on a strong entity for meaning and existence.

Book Reviews

Reviews belong to specific books. A review without a book makes no sense. Here's how we'd model it:

-- Weak entity: book reviews depend on books
-- Reference: BookVault review system
CREATE TABLE book_reviews (
    book_id INTEGER NOT NULL,
    customer_id INTEGER NOT NULL,
    rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    review_text TEXT,
    review_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Composite key: book + customer (one review per customer per book)
    PRIMARY KEY (book_id, customer_id),
    
    -- Identifying relationship to books
    FOREIGN KEY (book_id) REFERENCES books(book_id) 
        ON DELETE CASCADE,
    
    -- Regular relationship to customers
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

This design prevents duplicate reviews (one per customer per book) while maintaining the dependency relationship. Delete a book, and all its reviews vanish automatically.

Book Chapters

If BookVault sold technical books with detailed chapter information, chapters would be weak entities. Chapter 5 means nothing without knowing which book.

-- Weak entity: chapters belong to books
-- Reference: BookVault chapter system  
CREATE TABLE book_chapters (
    book_id INTEGER NOT NULL,
    chapter_number INTEGER NOT NULL,
    chapter_title VARCHAR(200) NOT NULL,
    page_start INTEGER,
    page_count INTEGER,
    
    -- Natural composite key: book + chapter number
    PRIMARY KEY (book_id, chapter_number),
    
    -- Identifying relationship
    FOREIGN KEY (book_id) REFERENCES books(book_id)
        ON DELETE CASCADE
);

Spotify uses this same pattern for album tracks. Track #3 needs the album context to have meaning. The track depends on the album for complete identification.

Querying Weak Entities

Working with weak entities requires understanding their composite keys. You always need both parts of the key for specific lookups.

-- Find specific order item (need both key parts)
-- Reference: BookVault order_items lookup
SELECT 
    oi.quantity,
    oi.unit_price,
    b.title,
    (oi.quantity * oi.unit_price) AS line_total
FROM order_items oi
JOIN books b ON oi.book_id = b.book_id
WHERE oi.order_id = 1001 
  AND oi.book_id = 42;

quantity | unit_price | title                    | line_total
---------|------------|--------------------------|------------
       2 |      24.99 | Database Design Mastery |      49.98

Finding All Items in an Order

More commonly, you'll want all items belonging to one strong entity instance:

-- Get complete order details with all items
-- Reference: BookVault order summary report
SELECT 
    o.order_id,
    o.order_date,
    o.status,
    b.title,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS line_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN books b ON oi.book_id = b.book_id
WHERE o.order_id = 1001
ORDER BY b.title;

order_id | order_date | status    | title                    | quantity | unit_price | line_total
---------|------------|-----------|--------------------------|----------|------------|-----------
    1001 | 2024-01-15 | completed | Database Design Mastery |        2 |      24.99 |      49.98
    1001 | 2024-01-15 | completed | SQL Query Optimization  |        1 |      29.99 |      29.99

When NOT to Use Weak Entities

Weak entities aren't always the right choice. Sometimes what looks like a weak entity should actually be strong.

Independent Existence Test

Ask yourself: Could this entity exist independently and still have meaning? If yes, make it strong.

Take BookVault categories. A category like "Science Fiction" has meaning even without books in it. We might create the category first, then add books later. Categories are strong entities with their own natural keys.

Performance Considerations

Composite keys can impact performance. Every query needs both key parts, and indexes become more complex. Use weak entities when the relationship semantics demand it, not as a performance optimization.

Netflix handles millions of user ratings (weak entities dependent on both user and movie). The semantic correctness outweighs any performance concerns - and proper indexing solves most query speed issues anyway.

Quiz