Database Basics

Overview

A database is like a digital filing cabinet that helps applications store and retrieve data efficiently. Whether you’re building a social media platform, an e-commerce site, or a simple blog, understanding databases is crucial for modern web development.

This guide covers fundamental database concepts and best practices for working with data in your applications.

Key Terminology

• Schema: The structural blueprint of a database that defines how data is organized, including tables, fields, relationships, and constraints. Think of it like an architect’s plan for a building.

• Primary Key: A unique identifier for each record in a table (like a social security number for people). No two records can have the same primary key.

• Foreign Key: A field that links to a primary key in another table, creating relationships between data. Think of it like a reference from one book to another in a library catalog.

🎥 Video: SQL Explained in 100 seconds

Relational vs. NoSQL Databases

Before diving into specific database types, it’s important to understand the two main categories of databases. Think of relational databases like spreadsheets with strict rules, while NoSQL databases are more like flexible documents that can change shape as needed.

Key Differences

Feature	Relational (SQL)	NoSQL
Data Structure	Tables with fixed columns and rows	Flexible documents, key-value pairs, graphs, or wide-columns
Schema	Strict, predefined (see above definition)	Flexible, can change on the fly
Scaling	Vertical (bigger servers)	Horizontal (more servers)
Relationships	Strong, enforced through foreign keys (see above definition)	Flexible, can be modeled through references
Example Use Cases	Financial systems, inventory management	Social media, content management, IoT (Internet of Things) data
ACID Compliance	Full support	Varies by database
Query Language	Standard SQL	Database-specific
Learning Curve	Steeper (SQL + schema design)	Generally easier to start

Relational Databases (e.g., PostgreSQL, MySQL)

• Use structured tables with predefined schemas (see above definition)
• Enforce data relationships through foreign keys (see above definition)
• Great for complex queries and transactions
• Examples: User accounts, financial records

-- Example of a relational database table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,  -- Unique identifier for each user
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

NoSQL Databases (e.g., MongoDB, Firebase)

• Flexible document structure
• Better for rapid changes and unstructured data
• Easier horizontal scaling
• Examples: Social media posts, IoT data (sensors, devices, etc.)

// Example of a NoSQL document
{
  "_id": ObjectId("507f1f77bcf86cd799439011"),  // Document's unique identifier
  "username": "john_doe",
  "email": "john@example.com",
  "preferences": {
    "theme": "dark",
    "notifications": true
  },
  "posts": [
    { "title": "Hello World", "likes": 42 }
  ]
}

🎥 Video: SQL vs NoSQL Explained (4 minutes)

SQL Fundamentals

SQL (Structured Query Language) is the standard language for working with relational databases. Think of SQL like a very specific language for talking to your database - it has its own grammar and vocabulary for asking questions about your data.

Basic SQL Operations (CRUD)

-- Create: Insert new data
INSERT INTO users (username, email) 
VALUES ('jane_doe', 'jane@example.com');

-- Read: Query existing data
SELECT username, email 
FROM users 
WHERE id = 1;

-- Update: Modify existing data
UPDATE users 
SET email = 'new.email@example.com' 
WHERE username = 'jane_doe';

-- Delete: Remove data
DELETE FROM users 
WHERE username = 'jane_doe';

Database operations flow:

🎥 Video: Learn SQL Basics in Just 15 Minutes

Data Relationships & Normalization

Data normalization is the process of organizing data to reduce redundancy and improve data integrity. Think of it like organizing a library - books are categorized by genre, author, etc., rather than having all information in one big list.

Types of Relationships

1. One-to-One (1:1)

-- Example: User and Profile
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE profiles (
    id SERIAL PRIMARY KEY,
    user_id INTEGER UNIQUE REFERENCES users(id),
    bio TEXT
);

1. One-to-Many (1:N)

-- Example: User and Posts
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    content TEXT
);

1. Many-to-Many (N:M)

-- Example: Posts and Tags
CREATE TABLE posts_tags (
    post_id INTEGER REFERENCES posts(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (post_id, tag_id)
);

Relationship visualization:

🎥 Video: One-to-many and many-to-many relationships in SQL

Indexing & Query Optimization

Indexes are like the table of contents in a book - they help the database find data quickly without scanning every record. Understanding indexing is crucial for database performance.

Index Types

-- Basic index
CREATE INDEX idx_username ON users(username);

-- Composite index
CREATE INDEX idx_name_email ON users(first_name, last_name);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

Query optimization flow:

🎥 Video: Database Indexing best practices (4 minutes)

Transactions & Concurrency

Transactions ensure that multiple database operations either all succeed or all fail together. Think of it like transferring money between bank accounts - either both accounts should be updated, or neither should change.

-- Basic transaction
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Transaction with rollback
BEGIN;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    -- Something goes wrong
    ROLLBACK;

Transaction states:

🎥 Video: What is transaction in SQL?

Object-Relational Mapping (ORMs)

ORMs translate between database tables and application objects. Instead of writing raw SQL, you work with your programming language’s objects and classes.

// Example using Prisma ORM
import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

// Create a user
async function createUser() {
  const user = await prisma.user.create({
    data: {
      email: 'jane@example.com',
      name: 'Jane Doe',
      posts: {
        create: {
          title: 'Hello World'
        }
      }
    }
  })
}

ORM workflow:

🎥 Video: Prisma in 100 seconds
🎥 Video: Learn Prisma In 60 Minutes

Migrations & Schema Evolution

Database migrations are like version control for your database structure. They help track and apply database changes across different environments. With Prisma, migrations are handled through schema changes and the Prisma CLI.

1. Define Your Schema

// schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
}

2. Create and Apply Migrations

# Create a migration
npx prisma migrate dev --name add_user_table

# Apply migrations to production
npx prisma migrate deploy

3. Making Schema Changes

// Updated schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  createdAt DateTime @default(now())
  // Adding new fields
  role      String   @default("USER")
  posts     Post[]
}

// Adding a new model
model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
  createdAt DateTime @default(now())
}

Migration process:

Example Usage with Prisma Client

import { PrismaClient } from '@prisma/client'

const prisma = new PrismaClient()

async function main() {
  // Create a new user
  const user = await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice',
      posts: {
        create: {
          title: 'Hello World',
          content: 'My first post!'
        }
      }
    }
  })

  // Query users with their posts
  const users = await prisma.user.findMany({
    include: {
      posts: true
    }
  })
}

🎥 Video: Prisma Migrations: A Step-by-Step Guide

Best Practices

1. Security

• Use prepared statements to prevent SQL injection
• Implement proper access controls
• Encrypt sensitive data
• Regularly backup your database

2. Performance

• Index frequently queried columns
• Optimize queries for performance
• Use connection pooling
• Monitor query execution times

3. Design

• Follow normalization rules
• Use appropriate data types
• Plan for scalability
• Document your schema

Additional Resources

• 📖 PostgreSQL Documentation
• 📖 MongoDB Manual
• 🎥 3-hour “Complete SQL Mastery” Course