Database Dilemmas: Cracking the Code to Your Perfect Database Match

Choosing the right database for your application is a pivotal decision that can significantly influence your projects’s success. With a plethora of options available, each offering unique advantages and suited for different use cases, the process can be daunting. Whether you’re building a small scale application or a large enterprise system, the database you select will impact performance, scalability, maintainability and cost. Having seem my fair share of costly database migrations in projects, which could have been avoided if some due diligence was done while picking the database, I thought it would be useful to pen down the key factors that should influence your decisions on which database to use.

The most obvious place to start would be choice of relational or non relational database to choose(SQL or NoSQL). There are principles like the CAP Theorem that can help in this choice(more on this later in the article). For the sake of simplicity lets use the data you want to store and the kind of application you plan to build to help us make the choice.

You should go with Relational Databases if:

Your data:

1. is hightly structured(well-defined and organized in a tabular format with rows and columns)
2. is highly relational (eg. Customers, Orders, Products)
3. is known and the schema is fixed
4. needs strong transactional integrity (ACID properties)
5. volume is moderate and usually is not categorised as big data

or if the application you develop:

1. handles financial data (needs strong transactional integrity)
2. is an Enterprise Resource Planning (ERP) System
3. is a Customer Relationship Management (CRM) System
4. is part of a Healthcare System
5. handles transactions in Retail and E-commerce

The above pointers should give you an idea when to go for the nosql databases, but just for sake of completeness.

You should go with NoSQL Databases if:

Your data:

1. is unstructured or semi structured(jsons, xmls, texts, logs, images etc)
2. can be represented in various models such as key-value pairs, documents, column families, or graphs.
3. does not have a fixed schema and can evolve over time
4. consistency is not the top priority, eventually consistent would do
5. is of high volume and velocity (big data)

or if the application you develop:

1. is like a content management system
2. is like E-commerce -product catalog
3. is like a social network application
4. does realtime data analytics
5. does gaming or is internet of things based

Now that we have the ‘SQL or No SQL’ question answered, its time to figure out which database among the available choices in each category is the best. I have tried my best to give a comprehensive coverage on available choices their strengths and common usecases:

If you are going for relational Databases(open source first):

MySQL:

• Strengths: Widely used, extensive documentation, large community, good performance for read-heavy applications.(Open source)
• Use Cases: Web applications, e-commerce platforms, content management systems.

PostgreSQL:

• Strengths: Advanced SQL compliance, strong performance with complex queries, extensive feature set. (Open source)
• Use Cases: Analytical applications, complex queries, geospatial applications, financial systems.

SQLite:

• Strengths: Lightweight, self-contained, serverless, zero-configuration.(Open source)
• Use Cases: Embedded applications, mobile apps, small to medium-sized websites.

MariaDB

• Strengths:A fork of MySQL with additional features and improvements, Compatible with MySQL, making migration straightforward. Advanced storage engines like Aria and ColumnStore.(Open source)
• Use Cases:Web applications, E-commerce platforms, General-purpose database applications

Proprietary databases, are developed and maintained by companies that charge for their use. These databases typically come with advanced features, professional support, and regular updates. Here are some of the most well-known licensed relational databases:

Oracle Database:

• Strengths: Comprehensive feature set, strong security, high performance, robust support for large-scale enterprise applications.
• Use Cases: Enterprise applications, large-scale data warehousing, mission-critical applications.

Microsoft SQL Server:

• Strengths: Tight integration with Microsoft ecosystem, strong BI and analytics capabilities, robust security features.
• Use Cases: Enterprise applications, business intelligence, data warehousing, applications within the Microsoft ecosystem.

IBM DB2:

• Strengths:High performance and scalability for large-scale enterprise applications.Advanced analytics and AI integration, with support for IBM Watson.Robust security features.High availability and disaster recovery options, such as Db2 pureScale.
• Use Cases: Enterprise resource planning (ERP) systems, Data warehousing and business intelligence, Banking and financial services, Healthcare systems

If you are going for non relational databases a major point to keep in mind is that it scales by distributing data across multiple servers to handle larger datasets and higher traffic. Lets first see what are the most popular NoSQL databases and their usecases:

MongoDB:

• Strengths: Flexible document-based data model (JSON-like BSON format), Rich query language with support for complex queries, indexing, and aggregation.Horizontal scalability with sharding and high availability with replication.
• Use Cases:Content Management Systems (CMS), Real-time Analytics, Internet of Things (IoT)

Cassandra:

• Strengths: Distributed architecture designed for high availability and scalability.Linear scalability with no single point of failure.Tunable consistency levels for trade-offs between consistency and availability.
• Use Cases: Time-Series Data, Messaging Systems, High-Write, Low-Latency Applications

Redis:

• Strengths: In-memory data store with support for various data structures (strings, hashes, lists, sets, sorted sets).Extremely fast read and write operations.Persistence options for durability and data replication.
• Use Cases: Caching, Session Management,Pub/Sub Messaging

Couchbase:

• Strengths:High performance with low-latency read and write operations.Flexible data model with support for JSON documents and key-value access.Built-in caching and memory-first architecture.
• Use Cases:Web Applications, User Profile Management, Content and Metadata Stores, Mobile and Edge Computing Applications

HBase:

• Strengths: Distributed, scalable, and consistent key-value store.Built on top of Hadoop Distributed File System (HDFS).Optimized for read-heavy workloads and large-scale data processing.
• Use Cases: Big Data Analytics, Real-time Querying of Massive Datasets, Internet of Things (IoT) Data Storage

Elasticsearch:

• Strengths:Distributed search and analytics engine.Full-text search capabilities with near real-time indexing.Scalable and highly available, with support for multi-tenancy.
• Use Cases: Log and Event Data Analysis, Full-Text Search, Real-Time Analytics, Monitoring and Observability

Lately there is a ton of nosql database options to choose from, something that can assist the process is CAP Theorem.CAP Theorem was introduced in 1998, also known as Brewer’s theorem, is a fundamental principle in distributed computing that describes the trade-offs between three desirable properties of a distributed system: Consistency, Availability, and Partition tolerance. According to the CAP theorem, in a distributed system, it is impossible to simultaneously guarantee all three of these properties Consistency, Availability, and Partition tolerance. You can have any two but not all three together.Understanding the CAP theorem helps in designing and choosing appropriate distributed systems architectures based on the desired trade-offs.

Examples:

• Consistency and Availability (CA):Traditional relational databases (e.g., Oracle, SQL Server) prioritize consistency and availability but may sacrifice partition tolerance. They typically operate in a single data center with synchronous replication.
• Consistency and Partition tolerance (CP):Some distributed databases (e.g., HBase, Cassandra) prioritize consistency and partition tolerance, sacrificing availability under network partitions. They use eventual consistency models.
• Availability and Partition tolerance (AP):Many NoSQL databases (e.g., DynamoDB, Couchbase) prioritize availability and partition tolerance, sacrificing strong consistency guarantees. They provide high availability and can handle network partitions but may have eventual consistency.

By carefully assessing your requirements, considering factors such as data model, scalability, consistency, and latency, you can narrow down the options and choose a database that best fits your needs. Remember, the most suitable database today may not be the best choice tomorrow as your application evolves, so it’s important to periodically reassess your database choice to ensure it continues to meet your needs. Ultimately, a well-informed decision can pave the way for a robust and efficient application that delivers value to your users.

You can compare the ranking of the above mentioned databases and more here:https://db-engines.com/en/ranking