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.