Foundations of Databases

Databases are an integral part of modern back-end development. They allow applications to store and retrieve data efficiently and provide powerful querying capabilities. However, interacting with databases requires careful consideration and planning to ensure efficiency, scalability, latency, and performance. While we will not take a deep dive into database internals and system design choices revolving normalization of relational databases or principles of sharding and replication, this lesson will aim to simply highlight the key pointers that we need to consider in our learning journey.

Choosing the right database

The first decision that developers need to make when interacting with databases is choosing the right database for their application. Choosing the right database depends on several factors, including the nature of the data, whether user behavior is predominantly to read or write, the required consistency and scalability guarantees, and the performance requirements of the application, to name a few. There are several types of databases available, each with its own set of strengths and weaknesses. The most common types are given below.

Relational databases

Relational databases store data in tables with predefined schemas and relations between the entities modeled in the schemas. They are strictly and very clearly structured in the way the data is stored as well as operated on, which might provide a benefit when dealing with complicated entities that are interdependent. They support SQL queries and provide strong consistency guarantees. Examples include MySQL, PostgreSQL, and Oracle.

NoSQL databases

NoSQL databases store data in a nontabular format, making them far more flexible and scalable when we don’t need to tie entities with strong relations. They typically support more relaxed consistency guarantees and provide far better performance for certain types of queries. Examples include MongoDB, Cassandra, and DynamoDB.

In-memory databases

In-memory databases store data in memory rather than on disk, providing very fast read-and-write performance while sacrificing the availability of cold storage. They are typically used for caching and real-time data processing. Examples include Redis and memcached.

Database modeling

Once developers have chosen the right database, the next step is to model the data in a way that optimizes querying efficiency. The database schema should be designed to simplify query resolution complexity.

Entity design

While normalizing—splitting data into smaller coherent entities—allows for better data reuse and easier update, it also leads to potentially complex queries and lower performance for read-heavy scenarios. Having smaller coherent tables means the data is modularly structured, but having too many would lead to a struggle to collate our data from multiple sources. Therefore, it becomes essential to work with database administrators and experts to model our schema correctly for our specific use cases before we start using it.

For example, consider the following schema for an e-commerce application: