Database in System Design | Relational & Non-relational Database

Welcome to Our System design Journey! 🌟

Hey there! We're diving into the world of databases in this blog. Join us as we explore the different types, like MySQL, PostgreSQL, and MongoDB. We'll learn about how they handle data in unique ways.

We'll also chat about important things like ACID properties and the tricks of sharding and indexing. It's all about understanding how these tools shape our digital world. Let's unlock the secrets of data together! Welcome aboard!

Database Overview 📊

The database stands as the vital platform where our system securely stores its vast array of data. It serves as the foundational bedrock for the digital infrastructure, manifesting in two primary forms:

Relational Databases

Relational databases, often referred to as SQL databases, embody a structured paradigm. They store information within pre-defined schemas, akin to meticulously arranging new phone numbers in a phone diary. This structured approach organizes data in a row-and-column format. Well-known SQL databases include:

• MySQL 💿
• Oracle 📊
• MS SQL Server 📈
• SQLite 🗄️
• PostgreSQL 🐘
• MariaDB 🌼

A Closer Look at a Few:

MySQL

An open-source relational database management system (RDBMS) that meticulously organizes data into tables and rows. It effectively utilizes SQL to handle data transfers and employs SQL joins to streamline queries. Notably, it operates on a client-server architecture and supports multithreading.

PostgreSQL

Known as Postgres, this open-source RDMS amplifies the potential of SQL. Employing its variant of SQL, PL/pgSQL, it caters to more complex queries. Maintaining transaction integrity through ACID properties, it mandates pre-designed schemas and configuration for its relational structure, incorporating foreign keys to ensure data normalization.

Exploring SQL Joins

SQL joins serve a dual purpose: enabling access to information across multiple tables while maintaining a normalized database. This normalization ensures significantly reduced data redundancy. With minimal data redundancy, record updates or deletions don't necessitate extensive data manipulation or iteration.

Non-Relational Databases

On the flip side, non-relational databases, also known as No-SQL databases, embrace a dynamic schema. These systems function like folders, housing an assorted range of data, from phone numbers and addresses to social media preferences. Among the common types are:

• Key-value stores such as Redis and DynamoDB 🔑
• Document databases like MongoDB and CouchDB 📄
• Wide-column databases like Cassandra and HBase 📊
• Graph databases like Neo4J and InfiniteGraph 📈

Deep Dive into MongoDB

MongoDB, a NoSQL database, bypasses the conventional table-based storage, favoring document-based structures. This model streamlines database operations, facilitating the simultaneous manipulation of associated data. With data documented in JSON, it allows a flexible structure tailored to specific use cases.

Choosing the Right Database

The selection of a database holds paramount significance in any system. It's imperative to consider factors such as speed, reliability, and accuracy. While relational databases fortify data validity, non-relational databases prioritize consistency. Considerations include:

• ACID principles
• BASE guidelines
• SQL Joins
• Normalization
• Persistence

Database Schemas and Queries

Database Schemas 🗃️

Database schemas function as abstract blueprints, defining the actual structure of data storage. These blueprints delineate data types and inter-table relationships, warranting a pre-analysis of schemas before actual coding. This preemptive schema modeling process ensures thorough data verification and standardization.

Key Aspects of Database Schemas:

• Inclusion of all critical and relevant data
• Enforcing consistent formatting for all data entries
• Provision of unique keys for all entries and database objects
• Each column in a table should be equipped with a name and data type

The scope and complexity of the schema correlate directly with the size and intricacy of the data and the project. The foresight to predetermine these schemas is coined as data modeling.

Database Queries 📝

Database queries represent the core channel for interacting with the database, allowing the manipulation or retrieval of specified data. These queries are predominantly associated with CRUD (Create, Read, Update, Delete) operations and are commonly written in various query languages, such as SQL and QBE in GraphQL.

Maintaining Database Integrity: ACID Properties

Maintaining the sanctity and integrity of the database necessitates strict adherence to the ACID properties—an acronym standing for Atomicity, Consistency, Isolation, and Durability. The four key aspects ensure:

• Transactions are atomic, mandating either the execution of all instructions or none at all.
• Database consistency is maintained post every transaction.
• Simultaneous transactions don’t interfere with one another, ensuring isolation.
• Commitments made to the database are unalterable, even in the event of software failures, assuring durability.

Data Sharding and Indexing

Data Sharding 🧩

Sharding a database involves segmenting it into smaller, more manageable units. This segmentation improves consistency and efficiency by reducing query loads. By redirecting queries to precise locations rather than iterating through the entire database, this approach enhances query speeds. The sharding process utilizes a sharding key and is implemented in two forms: vertical sharding and horizontal sharding.

Data Indexing 📑

Data indexing acts as a catalyst for simplifying search processes within the database. Indexed elements can be accessed directly by providing specific keys. This indexing reduces data retrieval times, consequently augmenting the overall application efficiency and responsiveness.

In essence, a comprehensive understanding of these database intricacies enables the selection, structuring, and maintenance of a robust data management system that underpins the foundations of various digital architectures and applications.

Join us as we unravel the wonders of data! Stay tuned for more chats to explore this exciting world further!