Good database design is invisible. Bad database design haunts you forever. At ISKCON Organization, I designed a MySQL schema from scratch that needed to handle 100,000+ records efficiently. Here's the practical process I followed.
The Challenge
ISKCON's temple management system was running on a collection of spreadsheets and a poorly structured database with massive redundancy. Devotee information was duplicated across registration, donation, and event tables. A single address change required updates in multiple places, and data inconsistencies were common. The system handled 5,000+ daily registrations, and the lack of referential integrity meant orphaned records were accumulating steadily.
The Goal
Design a fully normalized (3NF) relational database schema that could manage 100K+ records across devotee profiles, events, donations, and financial transactions. The schema needed to eliminate data redundancy, enforce referential integrity, and support the RESTful API layer I was building with Node.js and Express.
The Approach
I started with entity-relationship modeling, identifying the core entities: devotees, events, donations, transactions, and venues. The critical insight was separating contact information into its own table with a one-to-many relationship to devotees — people have multiple addresses, phone numbers, and email addresses, and flattening these into the devotee table was the root cause of most redundancy.
For the donation system, I created a normalized structure with a donations table referencing both devotee_id and event_id (nullable for general donations), linked to a transactions table for payment processing details. This separation meant donation records remained clean even when payment processing details changed. I applied 3NF rigorously: every non-key attribute depends on the key, the whole key, and nothing but the key.
Indexing strategy was crucial for performance at scale. I created composite indexes on
the most common query patterns — devotee lookups by name and region, donation summaries
by date range, and event attendance by date. I also implemented soft deletes with an
is_active flag rather than hard deletes, preserving referential integrity
for historical reporting.
The Impact
The normalized schema managed 100K+ records with zero redundancy. Data inconsistencies dropped from a weekly occurrence to effectively zero thanks to foreign key constraints and proper normalization. Query performance was strong even at scale due to targeted indexing. The clean schema made the API layer straightforward to build — each REST endpoint mapped cleanly to one or two database operations.
Key Takeaways
Start with ER modeling before writing any SQL. Normalize to 3NF by default — denormalize only when you have measured performance data justifying it. Separate concerns in your schema just like you would in application code. Index for your actual query patterns, not for every column. And soft deletes preserve referential integrity while supporting historical reporting needs.
Questions? kanade.pra@northeastern.edu