Course contentsShow
← Software Engineering
Lesson 867 of 1,93327. Database Design in PracticeFree lesson

Maintaining Consistency in Denormalized Data

Learn strategies for keeping redundant data in sync: triggers, application logic, and eventual consistency.

Maintaining Consistency in Denormalized Data

You'll learn how to keep duplicate data synchronized when you've denormalized your database schema.

When you denormalize data—storing the same information in multiple places for performance—you create a new problem: how do you keep all those copies in sync? If a user changes their email address, and that email is stored in three different tables, you need a strategy to update all three reliably.

Three Core Strategies

1. Database Triggers

Database triggers automatically execute code when data changes. When you update a user's email in the users table, a trigger can immediately update the denormalized copy in the orders table. This keeps the database itself responsible for consistency, but adds overhead to every write operation.

Think of it like a domino effect—one change automatically cascades to others.

2. Application Logic

Your application code handles all updates across multiple tables within a transaction. When updating user data, your code explicitly updates both the normalized source and all denormalized copies. This gives you full control but requires disciplined development—every developer must remember to update all locations.

3. Eventual Consistency

Accept that copies won't be instantly synchronized. Update the primary record immediately, then use background jobs or message queues to propagate changes asynchronously. The system is temporarily inconsistent but eventually becomes consistent. This scales well but requires handling stale data gracefully.

Like sending group texts—everyone gets the message, but not at exactly the same millisecond.

Choosing Your Approach

Use triggers for critical consistency needs with moderate write volume. Use application logic when you need transaction guarantees and full control. Use eventual consistency for high-scale systems where perfect real-time sync isn't critical.

Key Takeaway: Denormalized data requires explicit synchronization strategies—choose between immediate consistency (triggers/application logic) and eventual consistency based on your tolerance for stale data and write performance needs.