Databases: Tables, Queries, and Relationships

Why This Matters (You’ll Use This Every Day)

Here’s the thing: almost every real-world application needs to store data. User accounts, product catalogs, transaction history, social media posts—it all lives in databases. I’ve never worked on a professional project that didn’t involve a database in some way.

Understanding databases isn’t just about learning SQL syntax. It’s about understanding how to organize information, how to ask questions of your data, and how to model relationships between different pieces of information. Once you get this, you’ll see database concepts everywhere—even in spreadsheets, CSV files, and JSON documents.

What Is a Database, Really?

A database is an organized collection of data that you can easily search, update, and manage. Think of it as a super-powered filing cabinet that can answer complex questions instantly.

Why not just use files?

You could store everything in text files or JSON files. But databases give you:

Fast searching - find one record among millions in milliseconds
Concurrent access - hundreds of users can read/write at the same time
Data integrity - ensures data follows rules and stays consistent
Relationships - link related data together intelligently
Transactions - all-or-nothing operations (crucial for payments!)

Tables: The Foundation of Databases

A table is like a spreadsheet. It has rows and columns.

Each row (also called a record) represents one item
Each column (also called a field) represents one piece of information about that item

Example: A Users Table

1users
2+----+----------+----------------------+------+
3| id | username | email                | age  |
4+----+----------+----------------------+------+
5|  1 | alice    | alice@example.com    |   28 |
6|  2 | bob      | bob@example.com      |   34 |
7|  3 | charlie  | charlie@example.com  |   22 |
8+----+----------+----------------------+------+

Table name: users
Columns: id, username, email, age
Rows: 3 users (Alice, Bob, Charlie)

Primary Keys: Unique Identifiers

Every table should have a primary key—a column that uniquely identifies each row. Usually, this is an id column.

Why? Because names aren’t unique. Two people can be named “John Smith.” But id=42 is always exactly one person.

Rules for primary keys:

Must be unique (no duplicates)
Can’t be null (must have a value)
Shouldn’t change over time

Data Types: What Goes in Each Column

Each column has a data type that defines what kind of information it holds.

Common data types:

INTEGER - whole numbers (1, 42, -7)
VARCHAR(n) - text with max length ('Alice', 'hello')
TEXT - unlimited-length text
DECIMAL(p,s) - precise decimal numbers (19.99, 1234.56)
DATE - dates ('2025-03-24')
TIMESTAMP - date and time ('2025-03-24 14:30:00')
BOOLEAN - true/false

Creating a Table (SQL)

Here’s how you define a table structure:

1CREATE TABLE users (
2    id INTEGER PRIMARY KEY,
3    username VARCHAR(50) NOT NULL,
4    email VARCHAR(100) NOT NULL UNIQUE,
5    age INTEGER,
6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
7);

This says:

id is the primary key
username is required (NOT NULL), max 50 characters
email is required, unique, max 100 characters
age is optional (can be NULL)
created_at automatically gets the current time when a row is created

Queries: Asking Questions of Your Data

SQL (Structured Query Language) is how you talk to databases. It’s remarkably readable—almost like English.

The Four Essential Operations (CRUD)

Create - add new data (INSERT)
Read - retrieve data (SELECT)
Update - modify existing data (UPDATE)
Delete - remove data (DELETE)

INSERT: Adding Data

1-- Add a new user
2INSERT INTO users (id, username, email, age)
3VALUES (4, 'diana', 'diana@example.com', 31);
4
5-- Add multiple users at once
6INSERT INTO users (id, username, email, age)
7VALUES
8    (5, 'eve', 'eve@example.com', 26),
9    (6, 'frank', 'frank@example.com', 29);

SELECT: Reading Data

 1-- Get all users
 2SELECT * FROM users;
 3
 4-- Get specific columns
 5SELECT username, email FROM users;
 6
 7-- Get users where age is greater than 25
 8SELECT * FROM users WHERE age > 25;
 9
10-- Get users, sorted by age
11SELECT * FROM users ORDER BY age DESC;
12
13-- Get just the first 10 users
14SELECT * FROM users LIMIT 10;
15
16-- Count how many users there are
17SELECT COUNT(*) FROM users;

UPDATE: Modifying Data

 1-- Update one user's email
 2UPDATE users
 3SET email = 'newemail@example.com'
 4WHERE id = 1;
 5
 6-- Increase everyone's age by 1 (birthday!)
 7UPDATE users
 8SET age = age + 1;
 9
10-- Update multiple columns
11UPDATE users
12SET username = 'alice123', age = 29
13WHERE id = 1;

DELETE: Removing Data

1-- Delete one user
2DELETE FROM users WHERE id = 3;
3
4-- Delete all users over 50
5DELETE FROM users WHERE age > 50;
6
7-- Delete everything (careful!)
8DELETE FROM users;

Pro tip: Always test your WHERE clause with a SELECT first!

1-- First, check what you're about to delete
2SELECT * FROM users WHERE age > 50;
3
4-- If that looks right, then delete
5DELETE FROM users WHERE age > 50;

Relationships: Connecting Tables Together

Real data isn’t isolated. Users write posts. Products have reviews. Students take courses. Relationships let you model these connections.

One-to-Many Relationships

One user can have many posts, but each post belongs to one user.

 1users
 2+----+----------+
 3| id | username |
 4+----+----------+
 5|  1 | alice    |
 6|  2 | bob      |
 7+----+----------+
 8
 9posts
10+----+----------------------------------------------+---------+
11| id | content                                      | user_id |
12+----+----------------------------------------------+---------+
13|  1 | Hello world!                                 |       1 |
14|  2 | I love databases!                            |       1 |
15|  3 | Just learned about foreign keys              |       2 |
16+----+----------------------------------------------+---------+

The user_id column in the posts table is a foreign key—it references the id column in the users table.

Creating this relationship:

1CREATE TABLE posts (
2    id INTEGER PRIMARY KEY,
3    content TEXT NOT NULL,
4    user_id INTEGER NOT NULL,
5    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
6    FOREIGN KEY (user_id) REFERENCES users(id)
7);

Querying across relationships:

 1-- Get all posts by alice (user_id = 1)
 2SELECT * FROM posts WHERE user_id = 1;
 3
 4-- Get all posts with the author's username (JOIN!)
 5SELECT posts.content, users.username
 6FROM posts
 7JOIN users ON posts.user_id = users.id;
 8
 9-- Get all posts by alice using username
10SELECT posts.*
11FROM posts
12JOIN users ON posts.user_id = users.id
13WHERE users.username = 'alice';

Many-to-Many Relationships

Students can take many courses. Courses can have many students.

You need a junction table (also called a join table) to model this.

 1students
 2+----+---------+
 3| id | name    |
 4+----+---------+
 5|  1 | Alice   |
 6|  2 | Bob     |
 7+----+---------+
 8
 9courses
10+----+------------+
11| id | name       |
12+----+------------+
13|  1 | Math 101   |
14|  2 | History 101|
15+----+------------+
16
17enrollments (junction table)
18+------------+-----------+
19| student_id | course_id |
20+------------+-----------+
21|          1 |         1 |  (Alice is in Math)
22|          1 |         2 |  (Alice is in History)
23|          2 |         1 |  (Bob is in Math)
24+------------+-----------+

Creating this relationship:

1CREATE TABLE enrollments (
2    student_id INTEGER NOT NULL,
3    course_id INTEGER NOT NULL,
4    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
5    PRIMARY KEY (student_id, course_id),
6    FOREIGN KEY (student_id) REFERENCES students(id),
7    FOREIGN KEY (course_id) REFERENCES courses(id)
8);

Querying many-to-many:

 1-- Get all courses Alice is taking
 2SELECT courses.name
 3FROM courses
 4JOIN enrollments ON courses.id = enrollments.course_id
 5JOIN students ON enrollments.student_id = students.id
 6WHERE students.name = 'Alice';
 7
 8-- Get all students in Math 101
 9SELECT students.name
10FROM students
11JOIN enrollments ON students.id = enrollments.student_id
12JOIN courses ON enrollments.course_id = courses.id
13WHERE courses.name = 'Math 101';

Using Databases in Your Code

You rarely write SQL directly in production apps. Instead, you use libraries that make database access easier.

Python: Using SQLite

SQLite is a simple, file-based database perfect for learning.

 1import sqlite3
 2
 3# Connect to database (creates it if it doesn't exist)
 4conn = sqlite3.connect('myapp.db')
 5cursor = conn.cursor()
 6
 7# Create a table
 8cursor.execute('''
 9    CREATE TABLE IF NOT EXISTS users (
10        id INTEGER PRIMARY KEY AUTOINCREMENT,
11        username TEXT NOT NULL,
12        email TEXT NOT NULL UNIQUE,
13        age INTEGER
14    )
15''')
16
17# Insert data
18cursor.execute(
19    "INSERT INTO users (username, email, age) VALUES (?, ?, ?)",
20    ('alice', 'alice@example.com', 28)
21)
22
23# Insert multiple rows
24users = [
25    ('bob', 'bob@example.com', 34),
26    ('charlie', 'charlie@example.com', 22)
27]
28cursor.executemany(
29    "INSERT INTO users (username, email, age) VALUES (?, ?, ?)",
30    users
31)
32
33# Commit the changes
34conn.commit()
35
36# Query data
37cursor.execute("SELECT * FROM users WHERE age > 25")
38results = cursor.fetchall()
39
40for row in results:
41    print(f"ID: {row[0]}, Username: {row[1]}, Email: {row[2]}, Age: {row[3]}")
42
43# Query with named columns
44cursor.execute("SELECT username, email FROM users WHERE username = ?", ('alice',))
45user = cursor.fetchone()
46if user:
47    print(f"Found user: {user[0]} ({user[1]})")
48
49# Close the connection when done
50conn.close()

Note the ? placeholders: This prevents SQL injection attacks. Never concatenate user input into SQL strings!

Python: Using PostgreSQL (with psycopg2)

For production apps, you’ll use a full database server like PostgreSQL.

 1import psycopg2
 2
 3# Connect to PostgreSQL
 4conn = psycopg2.connect(
 5    host="localhost",
 6    database="myapp",
 7    user="myuser",
 8    password="mypassword"
 9)
10cursor = conn.cursor()
11
12# Insert data
13cursor.execute(
14    "INSERT INTO users (username, email, age) VALUES (%s, %s, %s)",
15    ('alice', 'alice@example.com', 28)
16)
17conn.commit()
18
19# Query data
20cursor.execute("SELECT username, email FROM users WHERE age > %s", (25,))
21users = cursor.fetchall()
22
23for username, email in users:
24    print(f"{username}: {email}")
25
26cursor.close()
27conn.close()

Java: Using JDBC

JDBC (Java Database Connectivity) is the standard way to access databases in Java.

 1import java.sql.*;
 2
 3public class DatabaseExample {
 4    public static void main(String[] args) {
 5        String url = "jdbc:sqlite:myapp.db";
 6
 7        try (Connection conn = DriverManager.getConnection(url)) {
 8
 9            // Create table
10            String createTableSQL = """
11                CREATE TABLE IF NOT EXISTS users (
12                    id INTEGER PRIMARY KEY AUTOINCREMENT,
13                    username TEXT NOT NULL,
14                    email TEXT NOT NULL,
15                    age INTEGER
16                )
17                """;
18
19            try (Statement stmt = conn.createStatement()) {
20                stmt.execute(createTableSQL);
21            }
22
23            // Insert data using PreparedStatement (prevents SQL injection)
24            String insertSQL =
25                "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";
26
27            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
28                pstmt.setString(1, "alice");
29                pstmt.setString(2, "alice@example.com");
30                pstmt.setInt(3, 28);
31                pstmt.executeUpdate();
32            }
33
34            // Query data
35            String querySQL =
36                "SELECT username, email, age FROM users WHERE age > ?";
37
38            try (PreparedStatement pstmt = conn.prepareStatement(querySQL)) {
39                pstmt.setInt(1, 25);
40
41                try (ResultSet rs = pstmt.executeQuery()) {
42                    while (rs.next()) {
43                        String username = rs.getString("username");
44                        String email = rs.getString("email");
45                        int age = rs.getInt("age");
46
47                        System.out.println(
48                            username + ": " + email + " (age " + age + ")");
49                    }
50                }
51            }
52
53        } catch (SQLException e) {
54            System.out.println("Database error: " + e.getMessage());
55        }
56    }
57}

Java: Using JPA/Hibernate (ORM)

ORMs (Object-Relational Mappers) let you work with objects instead of SQL.

 1import javax.persistence.*;
 2
 3// Define an entity (maps to a table)
 4@Entity
 5@Table(name = "users")
 6public class User {
 7    @Id
 8    @GeneratedValue(strategy = GenerationType.IDENTITY)
 9    private Long id;
10
11    @Column(nullable = false, length = 50)
12    private String username;
13
14    @Column(nullable = false, unique = true)
15    private String email;
16
17    private Integer age;
18
19    // Constructors, getters, setters
20    public User() {}
21
22    public User(String username, String email, Integer age) {
23        this.username = username;
24        this.email = email;
25        this.age = age;
26    }
27
28    // Getters and setters...
29}
30
31// Using the entity
32public class Main {
33    public static void main(String[] args) {
34        EntityManagerFactory emf = Persistence.createEntityManagerFactory("myapp");
35        EntityManager em = emf.createEntityManager();
36
37        // Insert
38        em.getTransaction().begin();
39        User alice = new User("alice", "alice@example.com", 28);
40        em.persist(alice);
41        em.getTransaction().commit();
42
43        // Query
44        TypedQuery<User> query = em.createQuery(
45            "SELECT u FROM User u WHERE u.age > :age", User.class);
46        query.setParameter("age", 25);
47
48        List<User> users = query.getResultList();
49        for (User user : users) {
50            System.out.println(user.getUsername() + ": " + user.getEmail());
51        }
52
53        em.close();
54        emf.close();
55    }
56}

Common Database Patterns

Pattern 1: User Authentication

 1CREATE TABLE users (
 2    id INTEGER PRIMARY KEY AUTOINCREMENT,
 3    username VARCHAR(50) NOT NULL UNIQUE,
 4    email VARCHAR(100) NOT NULL UNIQUE,
 5    password_hash TEXT NOT NULL,  -- Never store plain passwords!
 6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 7);
 8
 9-- Check if username exists
10SELECT id FROM users WHERE username = 'alice';
11
12-- Verify login (compare password hash)
13SELECT id, password_hash FROM users WHERE username = 'alice';

Pattern 2: Blog Posts with Comments

 1CREATE TABLE posts (
 2    id INTEGER PRIMARY KEY AUTOINCREMENT,
 3    title VARCHAR(200) NOT NULL,
 4    content TEXT NOT NULL,
 5    author_id INTEGER NOT NULL,
 6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 7    FOREIGN KEY (author_id) REFERENCES users(id)
 8);
 9
10CREATE TABLE comments (
11    id INTEGER PRIMARY KEY AUTOINCREMENT,
12    content TEXT NOT NULL,
13    post_id INTEGER NOT NULL,
14    author_id INTEGER NOT NULL,
15    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
16    FOREIGN KEY (post_id) REFERENCES posts(id),
17    FOREIGN KEY (author_id) REFERENCES users(id)
18);
19
20-- Get a post with all its comments
21SELECT
22    posts.title,
23    posts.content,
24    comments.content AS comment_content,
25    users.username AS commenter
26FROM posts
27LEFT JOIN comments ON posts.id = comments.post_id
28LEFT JOIN users ON comments.author_id = users.id
29WHERE posts.id = 1;

Pattern 3: E-commerce Products and Orders

 1CREATE TABLE products (
 2    id INTEGER PRIMARY KEY AUTOINCREMENT,
 3    name VARCHAR(200) NOT NULL,
 4    price DECIMAL(10, 2) NOT NULL,
 5    stock_quantity INTEGER NOT NULL DEFAULT 0
 6);
 7
 8CREATE TABLE orders (
 9    id INTEGER PRIMARY KEY AUTOINCREMENT,
10    user_id INTEGER NOT NULL,
11    total_amount DECIMAL(10, 2) NOT NULL,
12    status VARCHAR(20) DEFAULT 'pending',
13    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
14    FOREIGN KEY (user_id) REFERENCES users(id)
15);
16
17CREATE TABLE order_items (
18    id INTEGER PRIMARY KEY AUTOINCREMENT,
19    order_id INTEGER NOT NULL,
20    product_id INTEGER NOT NULL,
21    quantity INTEGER NOT NULL,
22    price DECIMAL(10, 2) NOT NULL,  -- Price at time of order
23    FOREIGN KEY (order_id) REFERENCES orders(id),
24    FOREIGN KEY (product_id) REFERENCES products(id)
25);
26
27-- Get all items in an order
28SELECT
29    products.name,
30    order_items.quantity,
31    order_items.price,
32    (order_items.quantity * order_items.price) AS subtotal
33FROM order_items
34JOIN products ON order_items.product_id = products.id
35WHERE order_items.order_id = 1;

Real-World War Story

Let me tell you about a painful lesson I learned. I was building a simple app and decided to store user data in a JSON file instead of using a database. “It’s just a few users,” I thought. “Why complicate things?”

Everything was fine until we hit about 100 users. Then the app started slowing down. Why? Because every time someone logged in, the code had to:

Read the entire JSON file from disk
Parse all the JSON into memory
Loop through every user to find the matching username
Verify the password

With 1,000 users, login took 2 seconds. With 10,000 users, it was timing out.

The fix: Switched to SQLite with an indexed username column. Login went from 2 seconds back to 20 milliseconds. The database could find the right user instantly, even with millions of records.

The lesson: Databases aren’t overkill. They’re the right tool for structured data. Use them.

What You Need to Remember

Here’s what I wish someone had told me when I was learning this:

Tables are like spreadsheets - rows are records, columns are fields
Primary keys uniquely identify rows - usually an id column
SQL is surprisingly readable - SELECT, INSERT, UPDATE, DELETE
Foreign keys create relationships - connect tables together
One-to-many uses a foreign key - posts have a user_id
Many-to-many uses a junction table - students ↔ courses needs enrollments
Always use parameterized queries - ? or %s, never string concatenation
ORMs are nice, but learn SQL first - you need to understand what’s happening

How This Helps Your Career

Databases are absolutely fundamental to software development:

Backend development - every API needs persistent storage
Data engineering - transforming and loading data between systems
Full-stack development - connecting frontends to data
Mobile development - local databases for offline functionality
Analytics - querying data to answer business questions

Every technical interview for backend or full-stack roles will ask about databases. You’ll be asked to design schemas, write SQL queries, or explain relationships. Understanding these concepts makes you immediately more valuable.

Six months from now, when you’re building a real application, you’ll automatically think about how to structure your tables, what relationships you need, and how to query your data efficiently. You’ll understand why your ORM generates certain SQL queries and how to optimize slow ones.

Trust me, this is one of those skills that compounds over time. Start with simple tables and queries, then experiment with relationships and joins. Build a small project that stores real data. You’ll be amazed how quickly database design becomes second nature.

Now go create some tables and start storing data!