Data Modeling

Choosing what data to store and how to structure it directly affects performance, scalability, and maintenance.

Relational databases are useful when you have structured data with clear relationships and need strong consistency (transaction-based actions, enforcing foreign key constraints). NoSQL databases shine for flexible schemas or when you need to scale horizontally across many servers without complex joins.

That said, the overlap between relational DBs and NoSQL ones is substantial, so focus on the DB that your using and how it helps solve the problem at hand, e.g., NoSQL DBs can express relationships too, SQL DBs can have JSON columns with flexible schemas, etc.

Normalization refers to splitting data across tables to avoid duplication, e.g., you have a users table, and orders table, and a products table, where each order references a userId and productId. Normalized data is easy to keep consistent, e.g., updating a product name is limited to the products table. However, getting complete data needs joins, which get expensive on huge/many tables.

Denormalization involves duplicating data, e.g., orders table contains the userName as well. This allows faster reads. However, updates are costly, e.g., an updated userName needs to be propagated across multiple records that have a copy.

NoSQL databases also require specifying the partition key and sort key. For a social media app, userId as the partition key makes “get all posts for user X” fast at the expense of queries like “get all posts mentioning hashtag Y”.

Database Indexing

Indexes make database queries fast. Most relational databases use B-trees, which support exact lookups (find user with email X) and range queries (find all orders between date A and date B). Hash indexes are faster for exact matches but can’t do range queries.

There are external systems that go beyond what the primary DB provider has. Elasticsearch is the go-to for full-text search. PostGIS in Postgres is popular for geospatial queries. Such extensions typically sync from your primary DB and so the search index will lag slightly behind. However, the increased functionality is usually worth it.

Search optimized databases build inverted indexes, a data structure that maps from words to documents that contain them, e.g.,

{
  "word1": ["doc1", "doc2", "doc3"],
  "word2": ["doc2", "doc3", "doc4"],
  "word3": ["doc1", "doc3", "doc4"]
}

… so that finding documents containing word1 is a quick lookup. Breaking a piece of text into individual words, e.g., doc1 -> ["word1", "word3"], is called tokenization. Closely related is stemming, which reduces words to their root form, e.g., “running” and “runs” both reducing to “run”, so that searching for “running” can surface search hits for “runs”.

Search optimized DBs come with fuzzy search as well, e.g., “rnning” surfaces search hits for “running”. This is achieved using algorithms that tolerate slight variations in the search term, e.g., an edit distance of less than \(k\).

References

Core Concepts for System Design Interviews. www.hellointerview.com . Accessed Jun 6, 2026.
System Design Key Technologies. www.hellointerview.com . Accessed Jul 14, 2026.