Over the past days of my 100 Days of Code challenge, I have learned how to build frontend interfaces using React, style UI components, and transition into backend development with Node.js and Express.js. After understanding how to connect the frontend with the backend and how APIs send and receive requests and data, it became clear that modern applications require a reliable way to store, retrieve, and manage data.
This is where databases play a crucial role.
What is a database:
A database is an organized place to store data so it can be easily saved, found, and updated. Think of it like a super-powered Excel spreadsheet that can handle millions of rows, multiple users at once, and complex relationships between data.
Why Do Applications Need Databases?
Imagine Instagram without a database — every photo, like, and comment would vanish the moment you closed the app. Applications need databases to:
- Remember things — user accounts, orders, messages
- Share data — so all users see the same information
- Search efficiently — find one user out of 100 million instantly
- Stay reliable — data survives crashes, restarts, and updates
Without a database, an app has no memory — it's like a person with amnesia who forgets everything after sleeping.
Types of Databases
1. 🗃️ Relational (SQL)
Data stored in tables with rows and columns, like a spreadsheet. Tables can relate to each other.
- Examples: PostgreSQL, MySQL, SQLite
- Best for: Structured data, financial systems, e-commerce
- Query language: SQL
SELECT * FROM users WHERE age > 25;
2. 📄 Document
Data stored as JSON-like documents. Flexible structure — each record can look different.
- Examples: MongoDB, Firestore
- Best for: Content, catalogs, user profiles
{ "name": "Ali", "age": 25, "hobbies": ["coding", "chess"] }
3. ⚡ Key-Value
Simplest type — just a key → value pair. Extremely fast.
- Examples: Redis, DynamoDB
- Best for: Caching, sessions, leaderboards
4. 🕸️ Graph
Data stored as nodes and edges (relationships). Great when connections matter more than the data itself.
- Examples: Neo4j, Amazon Neptune
- Best for: Social networks, recommendation engines, fraud detection
5. 📊 Wide-Column
Data stored in columns rather than rows. Great for massive scale.
- Examples: Cassandra, HBase
- Best for: IoT data, time-series, analytics at huge scale
6. 🔍 Search Engines
Optimized for full-text search across large amounts of text.
- Examples: Elasticsearch, Solr
- Best for: Search bars, log analysis
7. 📈 Time-Series
Optimized for data that's recorded over time (timestamps are the key).
- Examples: InfluxDB, TimescaleDB
- Best for: Metrics, stock prices, sensor data
Quick Decision Guide
| Situation | Use |
|---|---|
| Structured data with relationships | Relational (PostgreSQL) |
| Flexible/nested data | Document (MongoDB) |
| Need blazing speed / caching | Key-Value (Redis) |
| Social graph / recommendations | Graph (Neo4j) |
| Logs, metrics, monitoring | Time-Series (InfluxDB) |
| Search functionality | Elasticsearch |
Most real-world apps combine 2–3 types — e.g., PostgreSQL as the main DB + Redis for caching + Elasticsearch for search.
Relations in Databases
A relation is how two tables are connected to each other through a common field (usually a key).
Types of Relations
1. One-to-One (1:1)
One record in Table A links to exactly one record in Table B.
User (1) ──── (1) UserProfile
Each user has one profile. One profile belongs to one user.
2. One-to-Many (1:N) (most common)
One record in Table A links to many records in Table B.
Customer (1) ──── (N) Orders
One customer can place many orders. Each order belongs to one customer.
3. Many-to-Many (M:N)
Many records in Table A link to many records in Table B. Needs a junction table in between.
Students (N) ──── [Enrollments] ──── (N) Courses
A student can enroll in many courses. A course can have many students.
JOINs in Databases
A JOIN combines rows from two or more tables based on a related column between them.
Example tables:
| Customers (id, name) | Orders (id, customer_id, item) |
|---|---|
| 1, Alice | 1, 1, Laptop |
| 2, Bob | 2, 1, Phone |
| 3, Carol | 3, 99, Tablet |
Types of Joins
1. INNER JOIN
Returns only rows where there's a match in both tables.
SELECT * FROM Customers INNER JOIN Orders ON Customers.id = Orders.customer_id;
→ Returns Alice & Bob (Carol has no orders, order #3 has no matching customer)
2. LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table, plus matched rows from the right. Unmatched right-side values are NULL.
SELECT * FROM Customers LEFT JOIN Orders ON Customers.id = Orders.customer_id;
→ Returns Alice, Bob, and Carol (with NULLs for order columns)
3. RIGHT JOIN (RIGHT OUTER JOIN)
Returns all rows from the right table, plus matched rows from the left. Unmatched left-side values are NULL.
SELECT * FROM Customers RIGHT JOIN Orders ON Customers.id = Orders.customer_id;
→ Returns Alice, Bob's orders, and the orphan order #3 (with NULLs for customer columns)
4. FULL OUTER JOIN
Returns all rows from both tables. NULL fills in wherever there's no match.
SELECT * FROM Customers FULL OUTER JOIN Orders ON Customers.id = Orders.customer_id;
→ Returns everything — Alice, Bob, Carol, and orphan order #3
5. CROSS JOIN
Returns the Cartesian product — every row from table A paired with every row from table B.
SELECT * FROM Customers CROSS JOIN Orders;
→ 3 customers × 3 orders = 9 rows. Use with caution on large tables!
6. SELF JOIN
A table joined with itself. Useful for hierarchical data (e.g., employees and their managers).
SELECT e.name, m.name AS manager
FROM Employees e JOIN Employees m ON e.manager_id = m.id;
Quick Visual Summary
A ●───● B → INNER JOIN (intersection)
A ●───○ B → LEFT JOIN (all of A)
A ○───● B → RIGHT JOIN (all of B)
A ○───○ B → FULL JOIN (everything)
The most commonly used in practice are INNER and LEFT JOIN.
🎯 Conclusion
Databases offer structured or flexible ways to store, manage, and connect information efficiently, with each database type suited for different application needs. They make it possible to organize data, maintain consistency, and scale storage as systems grow.
- Different database types (relational, document, key-value, graph, wide-column, search, time-series) solve different data problems
- Relationships like one-to-one, one-to-many, and many-to-many link tables meaningfully
- SQL
JOIN(INNER,LEFT,RIGHT,FULL,CROSS,SELF) allow related data to be combined and queried effectively
Together, these concepts form the backbone of modern software, enabling applications to handle complex datasets, retrieve information quickly, and maintain reliable connections among data across the system.
Thanks for reading. Feel free to share your thoughts!
Top comments (0)