Interview knowledge
Article

Understanding Database Types

by Alex Xu19 Apr 2023

The success of a software application often hinges on the choice of the right databases. As developers, we're faced with a vast array of database options. It is crucial for us to understand the differences between these options and how to select the ones that best align with our project's requirements. A complex application usually uses several different databases, each catering to a specific aspect of the application’s needs.

Read the original →

Highlights

Passages saved from the original.

SQL vs. NoSQL. Source

  • Relational databases are based on the relational model, which organizes data into tables with rows and columns
    • Structured data organization
    • Relationships and referential integrity: The relationships between tables in a relational database are defined by primary and foreign keys, ensuring referential integrity. This feature allows for efficient querying of related data and supports complex data relationships.
    • SQL support: Relational databases use Structured Query Language (SQL) for querying, manipulating, and managing data. SQL is a powerful and widely adopted language that enables developers to perform complex queries and data manipulations.
    • Transactions and ACID properties: Relational databases support transactions, which are sets of related operations that either succeed or fail as a whole. This feature ensures the ACID properties – Atomicity, Consistency, Isolation, and Durability – are maintained, guaranteeing data consistency and integrity.
    • Indexing and optimization: Relational databases offer various indexing techniques and query optimization strategies, which help improve query performance and reduce resource consumption.
    • Limited scalability: Scaling relational databases horizontally (adding more nodes) can be challenging, especially when compared to some NoSQL databases that are designed for distributed environments.
    • Rigidity: The predefined schema in relational databases can make it difficult to adapt to changing requirements, as altering the schema may require significant modifications to existing data and applications.

Summary AI-generated

Summarised by an AI assistant when this source was saved. Double-check against the original.

In summary, mastering database selection requires recognizing that complex applications benefit from polyglot persistence, where a carefully balanced combination of relational and NoSQL databases is used to address specific scalability, consistency, and data structure tradeoffs.

  • Successful applications often rely on a combination of specialized databases rather than a single solution to address diverse requirements.
  • Relational databases provide strong data consistency, ACID compliance, and robust querying but face challenges with horizontal scalability and rigid schemas.
  • Selecting the right database requires evaluating tradeoffs between data structure, query complexity, scalability needs, and performance costs.
  • Developers should follow a structured selection process and study real-world case studies to confidently match database capabilities to application components.

Notes this inspired

Also cited by

  • How to choose a databaseEstablished

    PostgreSQL. Strengths: transactions, joins, constraints, flexible queries. Weaknesses: horizontal scale/sharding complexity, not ideal for huge analytical scans. DynamoDB. Strengths: massive scale, predictable low-latency key access, operational simplicity. Weaknesses: access-pattern-driven modelling, limited relational…