Database Design & SQL — ออกแบบฐานข้อมูลที่ดีและเขียน SQL อย่างมืออาชีพ 🗄️Database Design & SQL — Designing Good Databases and Writing SQL Like a Professional 🗄️

-- ═══════════════════════════════
-- สร้าง Tables สำหรับ E-Commerce
-- ═══════════════════════════════

-- Categories
CREATE TABLE categories (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_id   INT REFERENCES categories(id),  -- self-reference
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) NOT NULL UNIQUE,     -- URL-friendly name
    description TEXT,
    price       DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock       INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category_id INT REFERENCES categories(id),
    is_active   BOOLEAN DEFAULT true,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ═══════════════════════════════
-- INSERT — เพิ่มข้อมูล
-- ═══════════════════════════════

-- เพิ่ม 1 record
INSERT INTO customers (name, email, phone, password_hash)
VALUES ('สมชาย ใจดี', '[email protected]', '081-234-5678', '$2b$10$hash...');

-- เพิ่มหลาย records
INSERT INTO products (name, slug, price, stock, category_id) VALUES
    ('iPhone 16 Pro', 'iphone-16-pro', 45900.00, 50, 1),
    ('MacBook Air M4', 'macbook-air-m4', 42900.00, 30, 2),
    ('AirPods Pro 3', 'airpods-pro-3', 8900.00, 100, 3);

-- INSERT + RETURNING (ได้ค่ากลับมา)
INSERT INTO orders (customer_id, status, total)
VALUES (1, 'pending', 54800.00)
RETURNING id, ordered_at;

-- ═══════════════════════════════
-- INNER JOIN — เอาเฉพาะที่ match ทั้ง 2 ฝั่ง
-- ═══════════════════════════════

SELECT c.name, o.id AS order_id, o.total, o.status
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- LEFT JOIN — เอาทุก row จากซ้าย + match จากขวา (ถ้ามี)
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;
-- → ได้ลูกค้าทุกคน แม้ยังไม่เคยสั่ง (order_count = 0)

-- ═══════════════════════════════
-- สร้าง Index
-- ═══════════════════════════════

-- B-tree (default) — ใช้ได้กับ =, <, >, BETWEEN, ORDER BY
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_orders_status ON orders(status);

-- Unique index — ป้องกันค่าซ้ำ + เร็ว
CREATE UNIQUE INDEX idx_customers_email ON customers(email);

-- Composite index — หลาย columns
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

-- Partial index — index เฉพาะบาง rows
CREATE INDEX idx_active_products
ON products(name, price)
WHERE is_active = true;

EXPLAIN ANALYZE
SELECT p.name, p.price
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.name = 'Electronics'
  AND p.price < 50000
  AND p.is_active = true
ORDER BY p.price DESC
LIMIT 10;

-- ผลลัพธ์:
-- Limit  (cost=12.45..12.48 rows=10) (actual time=0.089..0.091 rows=10 loops=1)
--   ->  Sort  (cost=12.45..12.58 rows=50) (actual time=0.088..0.089 rows=10)
--         Sort Key: p.price DESC
--         ->  Nested Loop  (cost=0.43..11.89 rows=50) (actual time=0.031..0.072)
--               ->  Index Scan using idx_categories_name on categories c  ← ✅ ใช้ index!
--               ->  Index Scan using idx_products_category on products p  ← ✅ ใช้ index!

// migrations/20260307_001_create_products.js

exports.up = function(knex) {
  return knex.schema.createTable('products', (table) => {
    table.increments('id');
    table.string('name', 255).notNullable();
    table.string('slug', 255).notNullable().unique();
    table.text('description');
    table.decimal('price', 10, 2).notNullable();
    table.integer('stock').defaultTo(0);
    table.integer('category_id')
      .references('id').inTable('categories');
    table.boolean('is_active').defaultTo(true);
    table.timestamps(true, true);   // created_at, updated_at

    // Indexes
    table.index('category_id');
    table.index('price');
    table.index(['is_active', 'price']);  // composite
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('products');
};

// ❌ String concatenation (อย่าทำ!!!)
// User input: ' OR '1'='1' --
const query = `SELECT * FROM users
  WHERE email = '${email}'
  AND password = '${password}'`;

// กลายเป็น:
SELECT * FROM users
WHERE email = '' OR '1'='1' --'
AND password = 'anything'
// → Login ได้โดยไม่ต้องรู้ password!

// ✅ Parameterized queries (ทำแบบนี้เสมอ!)
const query = 'SELECT * FROM users WHERE email = $1 AND password = $2';
db.query(query, [email, password]);

-- Transaction — การสั่งซื้อต้อง atomic
-- ถ้า step ไหน fail → rollback ทั้งหมด!
BEGIN;

-- 1. สร้าง order
INSERT INTO orders (customer_id, status, total)
VALUES (1, 'confirmed', 54800.00)
RETURNING id INTO order_id;

-- 2. เพิ่ม order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (order_id, 1, 1, 45900.00),
    (order_id, 3, 1, 8900.00);

-- 3. ลด stock
UPDATE products SET stock = stock - 1 WHERE id = 1;
UPDATE products SET stock = stock - 1 WHERE id = 3;

COMMIT;
-- ถ้าทุกอย่าง OK → COMMIT
-- ถ้ามี error → ROLLBACK อัตโนมัติ

Every app you use every day has a database behind it — LINE (messages, contacts), Shopee (products, orders), YouTube (videos, comments), Banking App (accounts, transactions) 🏦

💡 "App without a database is just a pretty picture" — code can change every day, frameworks can change every year, but a database schema you designed wrong → a headache for the rest of your life

---

📊 Database Types — Which One Should You Pick?

Before we dive into SQL, we need to understand that there are several kinds of database, each with its own strengths and its own best-fit workload. Choosing the right database from the start is what lets your project grow efficiently.

In the database world there are two big families to choose between: Relational Database (SQL), where the data has a clear structure and clear relationships, and NoSQL Database, which is far more flexible about structure. Each exists for a different reason.

From the comparison above you can see that a Relational Database is still the main choice for most applications, because data in an ordinary app tends to be related: customers to orders, posts to comments, users to friends.

Within the relational family, PostgreSQL is the most recommended choice today, thanks to its breadth of capability and its modern feature set — but plenty of organizations still run MySQL as their mainstay. Let us compare the two.

PostgreSQL vs MySQL — A Comparison

	PostgreSQL ⭐	MySQL
License	BSD (genuinely free)	GPL (Oracle)
ACID	✅ Full	✅ (InnoDB)
JSON support	⭐ JSONB (fast)	JSON (slower)
Full-text search	✅ Built-in	✅ Basic
Extensions	PostGIS, pg_trgm, pgvector, timescale	Limited
Complex queries	⭐ CTE, Window functions, lateral	✅ OK
Performance	Better on complex queries	Better on simple reads
Used by	Instagram, Stripe, Discord, Reddit	Facebook, Uber, Airbnb, Twitter

From the table above you can see that PostgreSQL has the advantage on several fronts, especially its JSONB support, which lets you store NoSQL-shaped data inside the same relational database — plus powerful extensions such as PostGIS for spatial data.

MySQL still has its strengths: fast simple reads and a very large community. But for a new project PostgreSQL is usually the better choice, because of its flexibility and how complete its feature set is.

💡 Recommended: start with PostgreSQL — it can do everything
SQLite for prototypes / mobile / embedded

---

🏗️ Database Design — How Do You Design It Well?

A good database design is the foundation of an efficient application. A bad one means painful repairs later — risky work that eats a lot of time.

The core principle of design is to reduce Data Redundancy (duplicated data) and preserve Data Integrity (correct data). Let us look at a simple e-commerce system and see how the right and wrong designs differ.

Example: An E-Commerce System

Suppose we need to store customers, products and orders. Many people start by cramming everything into a single table, which looks easy and creates a large problem down the road.

The example above shows the difference between the wrong and the right design very plainly. The wrong one duplicates data, is hard to change, and drifts into inconsistency easily, while the right one splits the data across several tables and uses foreign keys to express the relationships.

Splitting tables with Primary Keys and Foreign Keys gives you this:

• Customer data is stored once → fix it in one place and the whole system follows
• A product goes out of stock → update it once and it shows everywhere
• Add a new feature → no need to alter the old table
• Queries get faster → smaller tables, more effective indexes

This principle is called Normalization: a systematic process for reducing data redundancy.

Normalization — Cutting the Redundancy

Each level of normalization has a clear purpose and a clear payoff. 1NF gives the data its basic structure, 2NF removes the duplication that composite keys invite, and 3NF cuts out the indirect dependencies.

In real work, designing to 3NF is usually enough; going further gets more complicated than it needs to be. Denormalization (folding tables back together) should only happen when there is a genuine, measurable performance problem.

💡 In practice: designing to 3NF is enough
Denormalize only when performance genuinely demands it

Once the database is designed, the next step is learning SQL (Structured Query Language), the language for working with data in every relational database. The fundamentals of SQL are much the same everywhere, but each engine has its own extensions and special features.

---

📝 SQL Essentials — The Statements You Must Know

CREATE TABLE — Creating a Table

Creating a table is the first step of working with a database. The CREATE TABLE statement defines the table's structure, including data types, constraints and relationships.

This example creates the tables for the e-commerce system we discussed earlier, using PostgreSQL syntax. Note the constraints — PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE — that keep the data correct.

-- ═══════════════════════════════
-- Create the tables for E-Commerce
-- ═══════════════════════════════

-- Categories
CREATE TABLE categories (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_id   INT REFERENCES categories(id),  -- self-reference
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products
CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    slug        VARCHAR(255) NOT NULL UNIQUE,     -- URL-friendly name
    description TEXT,
    price       DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    stock       INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    category_id INT REFERENCES categories(id),
    is_active   BOOLEAN DEFAULT true,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In the example above you can see the important constraints at work:

• SERIAL PRIMARY KEY → creates an auto-increment ID
• NOT NULL → forbids an empty value
• UNIQUE → forbids duplicates (email, slug)
• CHECK → sets a condition (price >= 0)
• REFERENCES → creates a foreign key linking tables
• DEFAULT → the value used when none is supplied

Choosing the right data type matters a great deal, because it drives storage space, performance and the correctness of the data. Here are the types you will use most often, by category.

The Data Types You Will Use Most

Picking the right data type saves storage and buys performance. VARCHAR suits data of uncertain length, TEXT suits long content such as articles, INT suits whole numbers, and DECIMAL suits any calculation that has to be exact.

TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) in PostgreSQL deserves a special mention: it is strongly recommended because it handles time zones for you, so you never have to nurse a headache over times differing from country to country.

💡 Money → DECIMAL, not FLOAT!
FLOAT: 0.1 + 0.2 = 0.30000000000000004
DECIMAL: 0.1 + 0.2 = 0.3 ✅

Once the tables exist, the next step is working with the data — what we call CRUD Operations, which cover every basic thing you need to do with a database.

CRUD — Create, Read, Update, Delete

-- ═══════════════════════════════
-- INSERT — adding data
-- ═══════════════════════════════

-- add 1 record
INSERT INTO customers (name, email, phone, password_hash)
VALUES ('Somchai Jaidee', '[email protected]', '081-234-5678', '$2b$10$hash...');

-- add several records
INSERT INTO products (name, slug, price, stock, category_id) VALUES
    ('iPhone 16 Pro', 'iphone-16-pro', 45900.00, 50, 1),
    ('MacBook Air M4', 'macbook-air-m4', 42900.00, 30, 2),
    ('AirPods Pro 3', 'airpods-pro-3', 8900.00, 100, 3);

-- INSERT + RETURNING (get the values back)
INSERT INTO orders (customer_id, status, total)
VALUES (1, 'pending', 54800.00)
RETURNING id, ordered_at;

The INSERT statements above show how to add a single record and several records at once. INSERT ... RETURNING is a genuinely useful feature: you get the ID, or any other value the database generated, straight back.

But inserting data is not enough on its own — we also have to query across several tables at once. This is where JOIN takes center stage.

JOIN — Combining Data from Several Tables

-- ═══════════════════════════════
-- INNER JOIN — only rows that match on both sides
-- ═══════════════════════════════

SELECT c.name, o.id AS order_id, o.total, o.status
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- LEFT JOIN — every row from the left + matches from the right (if any)
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;
-- → every customer, even those who have never ordered (order_count = 0)

JOIN is the heart of a relational database: it is what lets us pull data from several tables and present it together. INNER JOIN returns only the rows that match on both sides, while LEFT JOIN returns everything from the left side and only the matching rows from the right.

Understanding the JOIN types matters, because they change the result. Counting each customer's orders with an INNER JOIN returns only the customers who have ordered; with a LEFT JOIN you get every customer, including the ones who never ordered (shown as 0).

But as the data grows, queries slow down — and that is where Indexing comes in and makes them enormously faster.

---

⚡ Indexing — Making Queries 100x Faster

The comparison above shows the power of an index very clearly. Scanning a million records takes the database 2.3 seconds; with an index it takes 0.002 seconds. That is a thousand times faster.

An index is a book's table of contents — with no index you have to read the whole book to find the page you want.

But an index is not magic you can sprinkle without limit. Each index costs storage and slows INSERT and UPDATE down, because the index has to be updated too. So create indexes intelligently, according to how the data is actually used.

-- ═══════════════════════════════
-- Creating indexes
-- ═══════════════════════════════

-- B-tree (default) — works with =, <, >, BETWEEN, ORDER BY
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_orders_status ON orders(status);

-- Unique index — blocks duplicates + fast
CREATE UNIQUE INDEX idx_customers_email ON customers(email);

-- Composite index — several columns
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

-- Partial index — indexes only some rows
CREATE INDEX idx_active_products
ON products(name, price)
WHERE is_active = true;

The index examples above cover several kinds you will use in real work:

• B-tree Index (default) → suits equality and range queries
• Unique Index → prevents duplicate data and is faster than an ordinary index
• Composite Index → indexes several columns at once, for queries whose WHERE uses several columns
• Partial Index → indexes only the rows matching a condition, saving space and running faster

Once the index exists we need to check whether the database actually uses it, via EXPLAIN ANALYZE — the essential tool for performance tuning.

EXPLAIN ANALYZE — Analyzing Performance

EXPLAIN ANALYZE
SELECT p.name, p.price
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.name = 'Electronics'
  AND p.price < 50000
  AND p.is_active = true
ORDER BY p.price DESC
LIMIT 10;

-- Result:
-- Limit  (cost=12.45..12.48 rows=10) (actual time=0.089..0.091 rows=10 loops=1)
--   ->  Sort  (cost=12.45..12.58 rows=50) (actual time=0.088..0.089 rows=10)
--         Sort Key: p.price DESC
--         ->  Nested Loop  (cost=0.43..11.89 rows=50) (actual time=0.031..0.072)
--               ->  Index Scan using idx_categories_name on categories c  ← ✅ index used!
--               ->  Index Scan using idx_products_category on products p  ← ✅ index used!

The EXPLAIN ANALYZE output shows the query plan the database chose. Seeing "Index Scan" means our index was used successfully; seeing "Seq Scan" (Sequential Scan) means there is no suitable index and one needs to be created.

The "cost" and "actual time" figures let us compare performance, and "rows" tells us how many records were processed. Together they tell us whether another index is warranted.

But creating indexes is only one part of managing a database. We also need a systematic way to manage schema changes — and that is where Database Migrations come in.

---

🔄 Migrations — Managing Schema Changes

Why migrations? → every schema change is a code file, version controlled (git), re-runnable, rollback-able, and everyone on the team ends up with the same schema.

Migrations with Knex.js (Node.js)

// migrations/20260307_001_create_products.js

exports.up = function(knex) {
  return knex.schema.createTable('products', (table) => {
    table.increments('id');
    table.string('name', 255).notNullable();
    table.string('slug', 255).notNullable().unique();
    table.text('description');
    table.decimal('price', 10, 2).notNullable();
    table.integer('stock').defaultTo(0);
    table.integer('category_id')
      .references('id').inTable('categories');
    table.boolean('is_active').defaultTo(true);
    table.timestamps(true, true);   // created_at, updated_at

    // Indexes
    table.index('category_id');
    table.index('price');
    table.index(['is_active', 'price']);  // composite
  });
};

exports.down = function(knex) {
  return knex.schema.dropTable('products');
};

The migration above shows the right shape: an up() function for the change and a down() function to roll it back if something goes wrong. Because a migration is code, it can live in git, be reviewed, and be worked on as a team.

What migrations buy you:

• Version Control → schema changes are tracked in git
• Reproducible → the same run on dev, staging and production
• Rollback → go back if something goes wrong
• Team Sync → everyone on the team gets the same schema
• Automation → they run automatically in CI/CD

Having handled schema and performance, there is one more thing we must not forget: Security, and above all preventing SQL injection, one of the most dangerous vulnerabilities there is.

---

🛡️ Security — Preventing SQL Injection

SQL Injection — the most dangerous flaw in web apps

// ❌ String concatenation (never do this!!!)
// User input: ' OR '1'='1' --
const query = `SELECT * FROM users
  WHERE email = '${email}'
  AND password = '${password}'`;

// becomes:
SELECT * FROM users
WHERE email = '' OR '1'='1' --'
AND password = 'anything'
// → logged in without knowing the password!

// ✅ Parameterized queries (always do this!)
const query = 'SELECT * FROM users WHERE email = $1 AND password = $2';
db.query(query, [email, password]);

The example above shows the difference between dangerous string concatenation and safe parameterized queries. With parameterized queries the database keeps the SQL structure and the data apart, so no extra SQL can be injected.

The golden rules for preventing SQL injection:

• Never concatenate user input straight into SQL → no exceptions
• Always use parameterized queries → $1, $2, ? or :name
• Validate input → check type, range and format
• Use an ORM or query builder → the protection is built in
• Least Privilege → the DB user gets only the rights it needs

Beyond security, we also need to know how to handle complex operations that must "all succeed or not happen at all" — which is the idea behind Database Transactions.

---

🔄 Transactions — All or Nothing

-- Transaction — placing an order has to be atomic
-- if any step fails → roll all of it back!
BEGIN;

-- 1. create the order
INSERT INTO orders (customer_id, status, total)
VALUES (1, 'confirmed', 54800.00)
RETURNING id INTO order_id;

-- 2. add the order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
    (order_id, 1, 1, 45900.00),
    (order_id, 3, 1, 8900.00);

-- 3. decrement stock
UPDATE products SET stock = stock - 1 WHERE id = 1;
UPDATE products SET stock = stock - 1 WHERE id = 3;

COMMIT;
-- if everything is OK → COMMIT
-- if there is an error → ROLLBACK automatically

ACID Properties:

• A - Atomicity → all of it happens, or none of it does
• C - Consistency → the data is always valid
• I - Isolation → transactions do not see each other
• D - Durability → once committed = permanently recorded

The transaction above shows an order that takes several steps at once. If any step fails — out of stock, bad data — everything rolls back to its previous state, which is what stops inconsistent data from appearing.

The ACID properties are what make a database trustworthy:

• Atomicity → prevents half-finished data
• Consistency → preserves the business rules
• Isolation → transactions do not disturb each other
• Durability → committed data does not vanish

Now that we understand SQL fundamentals, the next question is whether to write raw SQL or reach for a helper such as an ORM or a query builder. Let us compare which suits which situation.

---

🏗️ ORM vs Query Builder vs Raw SQL

	ORM	Query Builder	Raw SQL
Examples	Prisma, SQLAlchemy, TypeORM	Knex.js, Kysely	pg, psycopg2
Abstraction	High	Medium	Low
Learning	Easy	Medium	You must know SQL
Complex queries	Gets harder	Flexible	⭐ Most flexible
Type safety	⭐ Excellent	✅ OK	❌ None
Performance	Good (but N+1 problem)	⭐ Close to raw	⭐ Fastest
Best for	CRUD apps, rapid dev	Complex queries	Analytics, optimization

The comparison above shows that each approach has a different strength. ORM suits developers who want type safety and high productivity, but it can be limiting on complex queries and carries the risk of the N+1 query problem.

Query Builder is a good middle ground: nearly as flexible as raw SQL while keeping some type safety. It suits a team with solid SQL skills that wants more control over its queries.

Raw SQL gives the most performance and the most flexibility, and suits analytics, reporting or advanced optimization — but you have to stay careful about SQL injection and about maintenance.

💡 Recommended: a Prisma/SQLAlchemy ORM for ordinary CRUD + raw SQL for complex analytics queries

Once we understand the techniques, what matters is applying them systematically. The checklist below will help you design and run a database like a professional.

---

📋 Database Design Checklist

The checklist above covers every important dimension of working with a database: design, performance, security and sound operations. Following it will keep your database in good shape and ready for the application to grow.

Good database design is not something you get right in a single night; it is a skill built through practice and experience. What matters is starting from the right principles and improving steadily as your understanding deepens.

The point to hold on to is that the database is the foundation of the application. Time invested in understanding it and designing it well at the start saves an enormous amount of time and trouble later.

---

🔑 Key Takeaways

1. PostgreSQL → the recommended first choice — it does everything, JSONB, extensions, free
2. Normalize to 3NF → cut the duplication, denormalize when performance demands it
3. Index every FK + WHERE column → EXPLAIN ANALYZE to confirm
4. Parameterized queries, always → prevents SQL injection (never concatenate strings!)
5. Transactions → ACID = all or nothing, use them for multi-step operations
6. DECIMAL for money → never FLOAT! (0.1 + 0.2 ≠ 0.3)
7. Migrations → every schema change is code, version controlled
8. ORM + Raw SQL → ORM for CRUD, raw SQL for analytics
9. Window Functions → RANK, SUM OVER, ROW_NUMBER — advanced analytics
10. Soft delete → do not really DELETE, set is_active = false