Overview

>

Database-storage

Overview

Version Control Database Storage Backend Development Frontend Development

Database & Storage

Estimated reading time: ~20 minutes

Overview

In today’s digital world, data is at the heart of every application. Whether you’re building a simple website or a complex social media platform, understanding how to store, manage, and retrieve data efficiently is crucial. This module will introduce you to the fundamentals of databases and storage, guiding you through the essential concepts, interactive diagrams, and best practices in database design.

By the end of this module, you will:

  1. Understand why database design matters.
  2. Compare relational and non-relational databases.
  3. Learn core principles of database design, including data integrity and ACID properties.
  4. See how to design a simple social media database from scratch.
  5. Explore best practices for building scalable and maintainable databases.

Table of Contents

  1. Why Database Design Matters
  2. Types of Databases
    • Relational Databases
    • Non-Relational Databases
  3. Core Principles of Database Design
    • Data Integrity
    • ACID Properties
  4. Designing a Database for a Social Media App
    • Defining Requirements
    • Creating an Entity-Relationship Diagram
    • Implementing the Database Schema
  5. Best Practices
  6. Key Takeaways
  7. Additional Resources

1. Why Database Design Matters

A well-designed database is essential for:

  • Performance: Efficient data retrieval and manipulation.
  • Scalability: Ability to handle growing amounts of data and users.
  • Maintainability: Easier updates and management over time.
  • Data Integrity: Ensuring accuracy and consistency of data.

Interactive Tip:

Imagine a poorly designed database like a messy closet—finding what you need takes forever, and adding or removing items creates chaos. A well-designed database is like a neatly organized closet, where everything has its place.

Poor database design can lead to slow queries, data anomalies, and challenges in scaling your application.

2. Types of Databases

2.1 Relational Databases

Relational databases store data in tables with rows and columns. They use Structured Query Language (SQL) for defining and manipulating data.

Common Relational Databases:

  • MySQL
  • PostgreSQL
  • SQLite

When to Use:

  • Structured data with clear relationships.
  • Need for complex queries and transactions.

Try It Out:

2.2 Non-Relational Databases (NoSQL)

Non-relational databases, or NoSQL databases, store data differently than relational tables, often in more flexible data models.

Types of NoSQL Databases:

  • Document Stores: MongoDB, CouchDB
  • Key-Value Stores: Redis, DynamoDB
  • Column Stores: Cassandra, HBase
  • Graph Databases: Neo4j

When to Use:

  • Unstructured or semi-structured data.
  • High scalability and flexible schema requirements.
  • Need for rapid development with changing data structures.

Interactive Graph Idea:

Create a simple mind map or graph-based chart showing each type of NoSQL database and a short note on their primary use cases.

3. Core Principles of Database Design

3.1 Data Integrity

Data integrity ensures that the data is accurate and consistent throughout its lifecycle.

  • Entity Integrity: Each table has a primary key that uniquely identifies each record.
  • Referential Integrity: Foreign keys correctly reference primary keys in related tables.
  • Domain Integrity: Data stored in a column must be of the correct type and within valid constraints (e.g., an age column shouldn’t hold negative values).

In Practice:

  • If you have a users table, make sure each user can be uniquely identified by a user_id.
  • If a tweet references a user by user_id, ensure that user_id actually exists in the users table.

3.2 ACID Properties

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties guarantee reliable processing of database transactions.

  1. Atomicity: Transactions are all-or-nothing. If any part fails, the whole transaction fails.
  2. Consistency: Transactions bring the database from one valid state to another, ensuring that all rules (constraints, triggers, etc.) are followed.
  3. Isolation: Concurrent transactions do not interfere with each other.
  4. Durability: Once a transaction is committed, it remains so, even in the event of a system failure.

Real-World Example:

When you transfer money between bank accounts, you don’t want the amount to be deducted from one account without being added to the other. ACID properties ensure that both the withdrawal and deposit succeed—or neither does.

4. Designing a Database for a Social Media App

Let’s walk through designing a simple database for a social media application inspired by Twitter.

4.1 Defining Requirements

  1. Users can create accounts.
  2. Users can post tweets.
  3. Users can follow other users.
  4. Users can like tweets.

4.2 Creating an Entity-Relationship Diagram

An Entity-Relationship Diagram (ERD) visualizes the entities (tables) and the relationships between them.

Diagram Explanation:

  • A User can post many Tweets.
  • A User can follow many other Users.
  • A User can like many Tweets.

4.3 Implementing the Database Schema

Below is an example of how you might implement these tables in SQL. You can try this out in a local development environment or an online SQL playground.

1. users Table

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. tweets Table

CREATE TABLE tweets (
    tweet_id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(user_id),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3. follows Table

CREATE TABLE follows (
    follower_id INT REFERENCES users(user_id),
    following_id INT REFERENCES users(user_id),
    followed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (follower_id, following_id)
);

4. likes Table

CREATE TABLE likes (
    user_id INT REFERENCES users(user_id),
    tweet_id INT REFERENCES tweets(tweet_id),
    liked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, tweet_id)
);

Take It Further:

  • Try inserting sample data into each table.
  • Write queries to fetch a user’s tweets, followers, and likes.

5. Best Practices

  1. Normalize Your Data:
    • Reduce data redundancy.
    • Ensure data dependencies make sense.
  2. Use Indexing:
    • Improve the speed of data retrieval.
    • Index frequently queried columns (e.g., username, tweet_id).
  3. Implement Proper Constraints:
    • Use NOT NULL, UNIQUE, CHECK, FOREIGN KEY.
    • Ensure data integrity at the database level.
  4. Plan for Scalability:
    • Consider potential data growth.
    • Optimize queries and your database structure accordingly.
  5. Backup Regularly:
    • Protect against data loss.
    • Implement automated backup solutions.

Tip:

Use a combination of vertical scaling (upgrading your server) and horizontal scaling (sharding or splitting data across multiple servers) as your user base grows.

6. Key Takeaways

  1. Understand Your Data Requirements: Clearly define what data you need to store, how it will be used, and how different pieces of data relate to each other.
  2. Choose the Right Database Type: Consider relational vs. non-relational based on your application’s needs and data structure.
  3. Follow Design Principles: Ensure data integrity and consistency through careful design and the use of ACID properties.
  4. Plan for the Future: Design with scalability, performance, and maintainability in mind.

7. Additional Resources

Congratulations!

You’ve completed the Databases & Storage module! By mastering these concepts, you’re well on your way to building efficient and robust databases for your applications. Remember, a well-designed database forms the backbone of any successful app.

Next Steps:

  • Implement the database schema in your development environment.
  • Experiment with writing SQL queries to interact with your database.
  • Try an ORM (Object-Relational Mapping) tool like Sequelize or Prisma to simplify database operations in your applications.

You Did It!

Celebrate by sketching out your own database diagram for a project idea. Then, move on to the next module to start building out your backend APIs.