Database Design

Database Design

Databases play a pivotal role in your infrastructure, serving as the lifeblood of your system. They form the foundation upon which everything else is built. While applications can be scaled and replicated across multiple servers, databases present unique challenges in terms of scalability and replication.

Unlike applications, which can be split across multiple servers to handle high volumes of requests, databases cannot be easily replicated to distribute the workload. Consequently, performance and optimization considerations are of utmost importance when designing databases.  Getting the database design right from the outset is crucial since making significant changes to an existing database infrastructure is akin to replacing the foundation of a building—a complex and arduous task.

Categories of Databases

To better understand databases, here’s some information on their categories based on their use.

Online Transactional Processing (OLTP)

OLTP databases are responsible for tracking all transactional updates, such as new order entries, customer entries, and event tracking. They serve as the “source of truth” and are critical to the operation of your systems. Losing or damaging an OLTP database can have severe consequences, making disaster recovery plans essential.​

Online Analytical Processing (OLAP)

OLAP databases gather and sometimes pre-aggregate data, typically sourced from OLTP and third-party systems. These databases provide the basis for analytical processing and can offer valuable insights into your business operations.

Cache Data

While Caching may not be a true database it is still vital to complex systems. It is a technique used to store frequently accessed data points to enhance performance. Data caches can significantly improve system efficiency and can be implemented at various levels and configured for different types of refresh cycles and triggers.

Database Data Models

For any given use case category of database noted above, the actual data model structure can vary or even be mixed to support the need.  Here’s a breakdown of types of databases based on their data models.

Relational Databases

Relational databases have served as the backbone of software systems for over 50 years. They prioritize data integrity and are an excellent choice for transactional data and critical OLTP systems. Relational databases excel in writing data (capturing transactions) efficiently and ensuring data integrity (ACID compliance, normalization, etc.).

NoSQL Databases

NoSQL databases offer alternatives to the heavily structured nature of relational databases. There are several types of databases within the NoSQL category, including document, graph, time series, and blockchain databases. While NoSQL databases may sacrifice some integrity compared to relational databases, they excel in storing large amounts of unstructured data and allow for fast querying and integration with analytics or AI systems.

Document Databases

Document databases are the most common type of NoSQL database and typically what comes to mind when the NoSQL term is used. They are well-suited for storing loosely defined content with a small set of known header fields. They are ideal for applications such as resume storage, email content, blog posts, and book inventorying.

Graph Databases

Graph databases are designed to map relationships between entities. Unlike relational databases, which rely on strict join operations between tables, graph databases enable the modeling of natural relationships. They are exceptionally powerful in uncovering data correlations and patterns that may not be readily apparent using other methods. Graph databases find applications in fraud detection and analysis, IT infrastructure planning and monitoring, recommendation engines, social network graphs, and identity access systems.

Time Series Databases

Time series databases (TSDBs) specialize in tracking common data points over time. While all types of databases can handle time-based data, TSDBs are specifically designed for measuring changes over time. They are well-suited for use cases such as weather analysis, logging and monitoring devices or systems, tracking stock prices, and storing IoT event data. TSDBs are optimized for read functions, are auto-partition capable, and highly scalable.

Blockchain Databases

Although once hailed as a revolutionary technology, blockchain has seen limited practical applications outside of cryptocurrencies. While it's worth mentioning due to its initial buzz, its relevance to database design is currently limited.

Big Data

Big data refers to entities like data warehouses, lakes, mart, and the like. Big data databases are often a mixture of other types. For instance, they might have relational data formed into a star or galaxy schema, containing some document or graph-style content and correlations.

Need help with database design? Dymeng’s team is highly skilled in this area. Founder Jack Leach has many years’ experience in database design and it’s always been his forte and passion. He was a 5-consecutive-year recipient of the Microsoft Most Valuable Professional award for their database-related products, and he brings his experience and passion to every project Dymeng takes on.