Relational Databases (SQL)
When to Use SQL
- Structured data: Fixed schemas, known relationships
- ACID compliance: Financial transactions, inventory
- Complex queries: JOINs, aggregations, subqueries
- Data integrity: Foreign keys, constraints
Popular Options
sql
-- PostgreSQL example
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
SELECT u.*, o.*
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01';NoSQL Databases
When to Use NoSQL
- Unstructured data: Flexible schemas, rapid iteration
- High write throughput: Social media feeds, logs
- Horizontal scaling: Distributed systems
- Document storage: JSON-like data structures
Popular Options
javascript
// MongoDB example
db.users.insertOne({
email: 'user@example.com',
profile: {
name: 'John',
preferences: {
theme: 'dark'
}
},
tags: ['developer', 'javascript']
});
db.users.find({
'profile.preferences.theme': 'dark'
});Decision Matrix
| Factor | Choose SQL | Choose NoSQL |
|---|---|---|
| Schema flexibility | Fixed schema | Flexible schema |
| Query complexity | Complex relationships | Simple queries |
| Scaling | Vertical scaling | Horizontal scaling |
| Transaction support | ACID required | Eventual consistency OK |
| Data structure | Relational | Document/Key-value/Graph |
Database Design Principles
Normalization (SQL)
sql
-- First Normal Form (1NF): Eliminate repeating groups
-- Second Normal Form (2NF): Remove partial dependencies
-- Third Normal Form (3NF): Remove transitive dependencies
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER NOT NULL,
order_date TIMESTAMP DEFAULT NOW()
);Denormalization (NoSQL)
javascript
// Embed related data for performance
db.users.insertOne({
_id: 'user123',
email: 'user@example.com',
orders: [
{ product: 'Widget A', quantity: 2, date: '2025-02-10' },
{ product: 'Widget B', quantity: 1, date: '2025-02-11' }
]
});Performance Optimization
Indexing
sql
-- SQL Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);javascript
// MongoDB Indexes
db.users.createIndex({ email: 1 });
db.orders.createIndex({ user_id: 1, order_date: -1 });Connection Pooling
javascript
// PostgreSQL connection pool
const { Pool } = require('pg');
const pool = new Pool({
host: 'localhost',
database: 'myapp',
max: 20, // Maximum pool size
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});Migration Strategies
SQL to NoSQL
- 1Assess schema: Identify relationships
- 2Denormalize: Combine related data
- 3Map data types: Convert SQL types to NoSQL
- 4Migrate data: Use ETL tools
- 5Update application: Modify queries
Conclusion
Choose SQL for structured data with relationships. Choose NoSQL for flexible schemas and horizontal scaling. Many applications use both!