Course contentsShow
← Computer Science
Lesson 1651 of 2,87237. Database Design and NormalizationFree lesson

Using Triggers and Application Logic

Implementing database triggers and application-level code to maintain consistency when using denormalized schemas.

Using Triggers and Application Logic

You'll learn how to maintain data consistency in denormalized databases using automated triggers and custom application code.

The Problem with Denormalization

When you denormalize a schema for better read performance, you introduce redundancy—the same data exists in multiple places. For example, you might store both individual order items and a pre-calculated order total. Now when an item price changes, you must update both places, or your data becomes inconsistent.

Two Enforcement Strategies

Database Triggers

Triggers are special stored procedures that fire automatically when specific data changes occur. They act like database "watchers."

CREATE TRIGGER update_order_total
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
  UPDATE orders 
  SET total_amount = (SELECT SUM(price * quantity) 
                      FROM order_items 
                      WHERE order_id = NEW.order_id)
  WHERE id = NEW.order_id;
END;

Pros: Consistency is guaranteed at the database level, regardless of which application touches the data.
Cons: Can be hard to debug, may hurt write performance, and not all databases support them equally.

Application Logic

Instead of relying on the database, your application code explicitly updates all related data when making changes.

def update_item_price(item_id, new_price):
    # Update the item
    update_order_item(item_id, new_price)
    # Recalculate order total in code
    order_id = get_order_id(item_id)
    new_total = calculate_order_total(order_id)
    update_order(order_id, new_total)

Pros: More visible, easier to test and debug, portable across databases.
Cons: Every developer must remember to maintain consistency; bugs can slip through.

Key Takeaway: Denormalization trades normalization's automatic consistency for performance, requiring you to explicitly maintain data integrity through database triggers, application logic, or both.