This content originally appeared on DEV Community and was authored by Sibasish Mohanty
The second part of our System Design series dives into data fundamentals. Most systems fail not because of code, but because of bad data modeling decisions. Let’s break down four pillars:
- SQL vs NoSQL
- Data Modeling
- Indexing
- Normalization
1. SQL vs NoSQL
TL;DR: Pick based on access patterns, not hype.
- SQL (Relational): Strong consistency, structured schema, powerful joins. Great for transactions (e.g., payments).
- NoSQL: Flexible schema, horizontal scale, eventual consistency. Great for unstructured or high-write workloads (e.g., social feeds).
👉 Rule of thumb: If you need joins and ACID, use SQL. If you need scale and flexibility, lean toward NoSQL.
👉 Interview tie-in: “Design Twitter feed storage.” Likely NoSQL, with denormalized fan-out.
2. Data Modeling
TL;DR: Always model data around your hot queries.
- Think backwards: What queries will dominate traffic?
- Denormalize or restructure tables/collections for those.
- Optimize for reads if the system is read-heavy.
👉 Example: In an e-commerce app, Orders table needs fast lookup by userId. That index matters more than less-frequent joins.
👉 Interview tie-in: They want to see that you don’t design schemas in a vacuum — you design for usage.
3. Indexing
TL;DR: The right index makes queries fly; the wrong one makes writes crawl.
-
Primary index: Natural row identifier (e.g.,
id) - Secondary indexes: Speed up queries but slow down writes
- Composite indexes: Useful when filtering on multiple fields
👉 Example: WHERE userId = 123 AND status = 'ACTIVE' → composite index on (userId, status)
👉 Interview tie-in: Expect questions like “Why is this query slow despite indexing?” — hint: wrong index or low selectivity.
4. Normalization
TL;DR: Normalize for writes, denormalize for reads.
- Normalization: Reduce redundancy, avoid anomalies (great for OLTP)
- Denormalization: Duplicate data to speed up queries (great for OLAP)
👉 Example: User profile data normalized in Users table. For faster feed rendering, duplicate username and avatar in Posts.
👉 Interview tie-in: “Would you normalize user data in a news feed?” — no, you’d denormalize for speed.
✅ Takeaways
- Choose SQL vs NoSQL based on workload, not dogma
- Model around hot queries, not theoretical schemas
- Use indexes wisely — they’re a double-edged sword
- Normalize for writes, denormalize for reads
💡 Practice Question:
"Design the database schema for Instagram posts, likes, and comments. Which parts would you normalize, and which would you denormalize?"
This content originally appeared on DEV Community and was authored by Sibasish Mohanty
Sibasish Mohanty | Sciencx (2025-08-28T05:34:24+00:00) Data Design That Doesn’t Crumble at Scale. Retrieved from https://www.scien.cx/2025/08/28/data-design-that-doesnt-crumble-at-scale/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.