Database Basics: From Zero to Real Tables

A database is a structured way to store, organize, and retrieve data. Think of it like a collection of spreadsheets (tables) that are connected to each other. In this guide, we'll learn database fundamentals with real-world examples — creating tables for an e-commerce system step by step.

1. What is a Database?

A database is an organized collection of data stored electronically. A relational database stores data in tables (rows and columns), and tables can be related to each other.

Real-world analogy: Imagine a school filing cabinet. Each drawer is a table (Students, Teachers, Classes). Each folder in a drawer is a row (one student). Each piece of info on the folder is a column (name, age, grade).

Key terms to know:

*Table — A collection of related data (like a spreadsheet)
*Row (Record) — A single entry in the table
*Column (Field) — A specific attribute (name, email, price)
*Primary Key (PK) — A unique identifier for each row
*Foreign Key (FK) — A column that links to another table's primary key

2. Common Data Types

Every column in a table has a data type that defines what kind of data it can hold:

Common SQL Data Types

-- Text Types
VARCHAR(255)    -- Variable-length string (max 255 chars), e.g. name, email
TEXT            -- Long text, e.g. description, bio
CHAR(10)        -- Fixed-length string (always 10 chars)

-- Number Types
INT             -- Whole numbers, e.g. age, quantity
BIGINT          -- Large whole numbers, e.g. id
DECIMAL(10,2)   -- Exact decimal, e.g. price (10 digits, 2 after dot: 99999999.99)
FLOAT           -- Approximate decimal

-- Date/Time Types
DATE            -- Date only: 2026-03-06
DATETIME        -- Date + time: 2026-03-06 14:30:00
TIMESTAMP       -- Auto-tracks date/time

-- Other Types
BOOLEAN         -- true/false (stored as 1/0)
ENUM('a','b')   -- Only allows listed values
JSON            -- JSON data

3. Create a Database

Let's build a real e-commerce database from scratch. First, create the database:

SQL

-- Create the database
CREATE DATABASE ecommerce_shop;

-- Select it to use
USE ecommerce_shop;

4. Create Users Table

Every e-commerce app needs users. Let's create a users table to store customer information:

SQL - Users Table

CREATE TABLE users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50) NOT NULL,
    email       VARCHAR(100) NOT NULL UNIQUE,
    password    VARCHAR(255) NOT NULL,
    phone       VARCHAR(20),
    address     TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Explanation:id is the primary key and auto-increments. NOT NULL means the field is required. UNIQUE ensures no duplicate emails. DEFAULT CURRENT_TIMESTAMP automatically sets the date when a row is created.

5. Create Categories Table

Products belong to categories. Let's create a simple categories table:

SQL - Categories Table

CREATE TABLE categories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    description TEXT,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

6. Create Products Table

Now the main products table. Notice how it references the categories table using a foreign key:

SQL - Products Table

CREATE TABLE products (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(200) NOT NULL,
    description   TEXT,
    price         DECIMAL(10,2) NOT NULL,
    stock         INT NOT NULL DEFAULT 0,
    category_id   INT,
    image_url     VARCHAR(500),
    is_active     BOOLEAN DEFAULT TRUE,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Foreign Key: links to categories table
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

Foreign Key: category_id links each product to a category. ON DELETE SET NULL means if a category is deleted, the product's category becomes NULL instead of being deleted too.

7. Create Orders Table

When a user buys something, we create an order. The orders table links to the users table:

SQL - Orders Table

CREATE TABLE orders (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    total_amount    DECIMAL(10,2) NOT NULL,
    status          ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled')
                    DEFAULT 'pending',
    shipping_address TEXT NOT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Foreign Key: links to users table
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

8. Create Order Items Table

Each order can have multiple products. The order_items table connects orders and products (this is a many-to-many relationship):

SQL - Order Items Table

CREATE TABLE order_items (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    order_id    INT NOT NULL,
    product_id  INT NOT NULL,
    quantity    INT NOT NULL DEFAULT 1,
    unit_price  DECIMAL(10,2) NOT NULL,

    -- Foreign Keys
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

Why a separate table? One order can have many products, and one product can appear in many orders. The order_items table (junction table) connects them.

9. Insert Real Data

Let's populate our tables with real data:

SQL - Insert Data

-- Insert categories
INSERT INTO categories (name, description) VALUES
('Electronics', 'Phones, laptops, and gadgets'),
('Clothing', 'Men and women fashion'),
('Books', 'Physical and digital books');

-- Insert users
INSERT INTO users (first_name, last_name, email, password, phone, address) VALUES
('John', 'Doe', 'john@example.com', 'hashed_password_123', '012-345-678', '123 Main St, Phnom Penh'),
('Jane', 'Smith', 'jane@example.com', 'hashed_password_456', '098-765-432', '456 Central Ave, Siem Reap'),
('Bob', 'Wilson', 'bob@example.com', 'hashed_password_789', '077-111-222', '789 River Rd, Battambang');

-- Insert products
INSERT INTO products (name, description, price, stock, category_id) VALUES
('iPhone 15 Pro', '256GB, Titanium Blue', 1199.99, 50, 1),
('Samsung Galaxy S24', '128GB, Phantom Black', 899.99, 75, 1),
('MacBook Pro 14"', 'M3 Pro chip, 18GB RAM', 1999.99, 30, 1),
('Cotton T-Shirt', '100% organic cotton, White', 29.99, 200, 2),
('Slim Fit Jeans', 'Dark blue denim', 59.99, 150, 2),
('Learning SQL', 'Master SQL from basics to advanced', 39.99, 100, 3);

-- Insert an order for John (user_id = 1)
INSERT INTO orders (user_id, total_amount, status, shipping_address) VALUES
(1, 1259.98, 'processing', '123 Main St, Phnom Penh');

-- Insert order items (John bought iPhone + T-Shirt)
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 1199.99),   -- 1x iPhone 15 Pro
(1, 4, 2, 29.99);     -- 2x Cotton T-Shirt

10. Query Data (SELECT)

Now let's retrieve data using common queries:

SQL - Queries

-- Get all products
SELECT * FROM products;

-- Get only name and price, sorted by price
SELECT name, price FROM products ORDER BY price DESC;

-- Get products under $100
SELECT name, price FROM products WHERE price < 100;

-- Get products in Electronics category (category_id = 1)
SELECT name, price, stock
FROM products
WHERE category_id = 1 AND is_active = TRUE;

-- Count products per category
SELECT category_id, COUNT(*) AS total_products
FROM products
GROUP BY category_id;

-- Search products by name
SELECT * FROM products WHERE name LIKE '%iPhone%';

11. JOIN — Combine Tables

JOINs let you combine data from multiple tables. This is the real power of relational databases:

SQL - JOIN Queries

-- Get products WITH their category name
SELECT
    p.name AS product_name,
    p.price,
    c.name AS category_name
FROM products p
INNER JOIN categories c ON p.category_id = c.id;

-- Result:
-- | product_name     | price   | category_name |
-- |------------------|---------|---------------|
-- | iPhone 15 Pro    | 1199.99 | Electronics   |
-- | Samsung Galaxy   | 899.99  | Electronics   |
-- | Cotton T-Shirt   | 29.99   | Clothing      |
-- | ...              | ...     | ...           |

-- Get order details: who bought what
SELECT
    u.first_name,
    u.last_name,
    o.id AS order_id,
    o.total_amount,
    o.status,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
INNER JOIN users u ON o.user_id = u.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

-- Result:
-- | first_name | last_name | order_id | total  | status     | product        | qty | price   |
-- |------------|-----------|----------|--------|------------|----------------|-----|---------|
-- | John       | Doe       | 1        | 1259.98| processing | iPhone 15 Pro  | 1   | 1199.99 |
-- | John       | Doe       | 1        | 1259.98| processing | Cotton T-Shirt | 2   | 29.99   |

-- Get total spending per user
SELECT
    u.first_name,
    u.last_name,
    SUM(o.total_amount) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.first_name, u.last_name;

12. Update & Delete Data

Modify and remove data from tables:

SQL - Update & Delete

-- Update product price
UPDATE products
SET price = 999.99, updated_at = NOW()
WHERE id = 1;

-- Update order status
UPDATE orders
SET status = 'shipped'
WHERE id = 1;

-- Reduce stock after purchase
UPDATE products
SET stock = stock - 1
WHERE id = 1;

-- Delete a product
DELETE FROM products WHERE id = 6;

-- Delete all cancelled orders
DELETE FROM orders WHERE status = 'cancelled';

-- DANGER: This deletes ALL rows! Never do this without WHERE!
-- DELETE FROM products;  <-- DO NOT RUN THIS

Warning: Always use a WHERE clause with UPDATE and DELETE! Without it, ALL rows will be affected.

13. Our Database Diagram

Here's how all our tables relate to each other:

E-Commerce Database Relationships

  users              orders             order_items          products          categories
+----------+      +------------+      +-------------+      +-----------+      +------------+
| id (PK)  |----->| id (PK)    |----->| id (PK)     |      | id (PK)   |<-----| id (PK)    |
| first_name|      | user_id(FK)|      | order_id(FK)|----->| name      |      | name       |
| last_name |      | total      |      | product_id(FK)|--->| price     |      | description|
| email     |      | status     |      | quantity    |      | stock     |      +------------+
| password  |      | address    |      | unit_price  |      | category_id(FK)
| phone     |      | created_at |      +-------------+      | is_active |
| address   |      +------------+                           +-----------+
+----------+

Relationships:
  users    ---< orders      (One user has many orders)
  orders   ---< order_items (One order has many items)
  products ---< order_items (One product in many orders)
  categories --< products   (One category has many products)

Summary

You now understand the fundamentals of relational databases:

✓What databases, tables, rows, and columns are
✓Common data types (INT, VARCHAR, DECIMAL, TEXT, etc.)
✓Creating tables with PRIMARY KEY and FOREIGN KEY
✓INSERT, SELECT, UPDATE, DELETE operations
✓JOIN queries to combine data from multiple tables
✓Building a real e-commerce database schema