Notes from https://www.hellointerview.com/learn/system-design/in-a-hurry/core-concepts

• Sounds simple but has massive downstream effects on your system

• First big choice is relational versus NoSQL

  Relational databases

  NoSQL

  • Relational databases like Postgres work great when you have structured data with clear relationships and need strong consistency.
    • Strong consistency
      • A consistency model where all reads reflect the most recent write
    • E.g user accounts linking to orders linking to products
    • Express complex queries with SQL
    • Use transactions to keep data consistent
    • Enforce foreign key constraints
  • NoSQL databases
    • DynamoDB or MongoDB
    • Shine when you need flexible schemas (data structure changes frequently) or you need to scale horizontally across many servers without complex joins

• Relational databases: Normalization and denormalization

  • Normalization means splitting data across tables to avoid duplication
    • Users table, orders table, and a products table
    • Each order references a user ID and product ID instead of copying the full user and product data into every order record
    • Keeps data consistent (update product name once and it’s updated everywhere), but means you need joins to get complete data
    • Joins get expensive when tables are huge or joining across multiple tables
  • Denormalization
    • Goes the other way
    • Duplicate data to avoid joins and make reads faster
    • Instead of joining users table every time to display an order, you store the username directly in each order record
    • Can fetch an order and display it without touching another table
    • Downside is updates
    • A user changes their name and you have to update it in the users table plus every order record that copied it
    • For read heavy systems where data rarely changes, this tradeoff is often worth it
  • For interview, safe default is to start with normalized relational model
    • Denormalize specific hot paths you identify read performance issues

• NoSQL databases

  • DynamoDB requires you to design your partition key and sort key based on your access patterns
  • If building a social media app and the most common query is “Get all posts for user X”, you’d use user_id as the partition key
    • Makes query a fast single-partition lookup
  • But now queries like “get all posts mentioning hashtag Y” require scanning the entire table because you didn’t design for that access pattern.
  • You have to know your queries upfront and design around them.

Notes from https://www.hellointerview.com/learn/system-design/core-concepts/data-modeling