Menguasai SQL Joins dan Relationships di PostgreSQL

#Pengenalan

SQL joins adalah tulang punggung database relasional. Namun banyak engineer yang kesulitan dengan joins—bukan karena joins secara inheren kompleks, tetapi karena mereka sering diajarkan secara terisolasi, terpisah dari keputusan desain skema dunia nyata yang membuat joins diperlukan.

Artikel ini mengambil pendekatan berbeda. Kami akan membangun database e-commerce yang realistis dari awal, menjelajahi setiap tipe relationship yang akan Anda temui dalam sistem produksi, dan menunjukkan cara membuat query mereka secara efisien. Di akhir, Anda akan memahami tidak hanya bagaimana menulis joins, tetapi mengapa relationship tertentu ada dan kapan menggunakannya.

Baik Anda mengoptimalkan query yang lambat, merancang skema baru, atau mempersiapkan wawancara system design, panduan ini mencakup pola praktis yang penting.

#Daftar Isi

#Memahami Fundamental Database Relasional

Sebelum mendalami joins, mari kita tetapkan mengapa mereka ada. Database relasional menerapkan normalisasi—praktik mengorganisir data untuk meminimalkan redundansi dan mempertahankan integritas data.

Pertimbangkan menyimpan informasi pelanggan. Anda bisa menduplikasi alamat pelanggan di setiap baris order, tetapi itu menciptakan masalah:

Data anomalies: Update alamat pelanggan di satu order, dan itu tidak konsisten di tempat lain
Storage waste: Alamat yang sama diulang ribuan kali
Query complexity: Menemukan semua order untuk pelanggan memerlukan pemindaian data yang redundan

Joins menyelesaikan ini dengan membiarkan Anda membagi data di seluruh tabel dan merakitnya kembali sesuai permintaan. Biayanya? Anda perlu memahami relationships.

#Tipe-Tipe Relationship Inti

#One-to-One Relationships

Relationship one-to-one berarti setiap baris di tabel A sesuai dengan tepat satu baris di tabel B, dan sebaliknya.

Contoh dunia nyata: Seorang user memiliki tepat satu profile, dan profile milik tepat satu user.

One-to-One Schema

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE user_profiles (
  id SERIAL PRIMARY KEY,
  user_id INTEGER UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  bio TEXT,
  avatar_url VARCHAR(255),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Constraint UNIQUE pada user_id menerapkan relationship one-to-one. Tanpanya, multiple profiles bisa mereferensi user yang sama.

Contoh query:

Fetch user dengan profile

SELECT 
  u.id,
  u.email,
  p.bio,
  p.avatar_url
FROM users u
LEFT JOIN user_profiles p ON u.id = p.user_id
WHERE u.id = 42;

Kami menggunakan LEFT JOIN di sini karena user mungkin belum memiliki profile. Jika kami menggunakan INNER JOIN, user tanpa profile akan dikecualikan.

#One-to-Many Relationships

One-to-many adalah relationship paling umum. Satu baris di tabel A bisa memiliki multiple baris di tabel B, tetapi setiap baris di B milik tepat satu baris di A.

Contoh dunia nyata: Seorang customer memiliki banyak orders, tetapi setiap order milik satu customer.

One-to-Many Schema

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  total_amount DECIMAL(10, 2),
  status VARCHAR(50) DEFAULT 'pending'
);
 
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Index pada customer_id sangat penting. Tanpanya, query yang memfilter berdasarkan customer menjadi full table scans.

Contoh query:

Fetch customer dengan semua orders

SELECT 
  c.id,
  c.name,
  c.email,
  o.id AS order_id,
  o.order_date,
  o.total_amount,
  o.status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE c.id = 5
ORDER BY o.order_date DESC;

Ini mengembalikan satu baris per order. Jika customer memiliki 100 orders, Anda mendapatkan 100 baris. Ini adalah perilaku yang benar—disebut "fan-out" dan diharapkan dalam one-to-many joins.

#Many-to-Many Relationships

Many-to-many berarti baris di tabel A bisa berhubungan dengan multiple baris di tabel B, dan sebaliknya. Anda memerlukan junction table (juga disebut join table atau bridge table) untuk merepresentasikan ini.

Contoh dunia nyata: Products termasuk dalam banyak categories, dan categories berisi banyak products.

Many-to-Many Schema

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT
);
 
CREATE TABLE product_categories (
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, category_id)
);
 
CREATE INDEX idx_product_categories_category_id ON product_categories(category_id);

Junction table memiliki composite primary key yang memastikan tidak ada duplicate relationships. Index kedua memungkinkan query yang efisien dari sisi category.

Contoh query:

Fetch product dengan categories

SELECT 
  p.id,
  p.name,
  p.price,
  c.id AS category_id,
  c.name AS category_name
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN categories c ON pc.category_id = c.id
WHERE p.id = 10;

Reverse query (categories dengan products):

Fetch category dengan products

SELECT 
  c.id,
  c.name,
  p.id AS product_id,
  p.name AS product_name,
  p.price
FROM categories c
LEFT JOIN product_categories pc ON c.id = pc.category_id
LEFT JOIN products p ON pc.product_id = p.id
WHERE c.id = 3
ORDER BY p.name;

#Polymorphic Relationships

Polymorphic relationships memungkinkan satu tabel untuk berhubungan dengan multiple tabel berbeda. Ini umum dalam sistem dengan perilaku bersama di seluruh tipe entity berbeda.

Contoh dunia nyata: Comments bisa dilampirkan ke posts, products, atau reviews. Alih-alih tabel terpisah untuk setiap tipe comment, gunakan single comments table dengan type indicator.

Polymorphic Relationship Schema

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2)
);
 
CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5)
);
 
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  commentable_type VARCHAR(50) NOT NULL,
  commentable_id INTEGER NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CHECK (commentable_type IN ('post', 'product', 'review'))
);
 
CREATE INDEX idx_comments_polymorphic ON comments(commentable_type, commentable_id);

Kolom commentable_type dan commentable_id membentuk polymorphic key. Index pada kedua kolom sangat penting untuk performa.

Contoh query (fetch comments untuk post spesifik):

Fetch post dengan comments

SELECT 
  p.id,
  p.title,
  p.content,
  c.id AS comment_id,
  c.content AS comment_text,
  c.created_at
FROM posts p
LEFT JOIN comments c ON p.id = c.commentable_id 
  AND c.commentable_type = 'post'
WHERE p.id = 7
ORDER BY c.created_at DESC;

Clause AND c.commentable_type = 'post' sangat penting. Tanpanya, Anda akan mendapatkan comments dari tipe entity lain.

Warning

Polymorphic relationships menukar fleksibilitas untuk kompleksitas. Setiap query memerlukan filtering berdasarkan type. Pertimbangkan apakah union dari tabel terpisah atau desain yang lebih ternormalisasi mungkin lebih jelas.

#Self-Referential Relationships

Tabel bisa mereferensi dirinya sendiri, berguna untuk data hierarki seperti struktur organisasi atau threaded comments.

Contoh dunia nyata: Employees memiliki managers (yang juga employees).

Self-Referential Schema

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  manager_id INTEGER REFERENCES employees(id) ON DELETE SET NULL,
  department VARCHAR(100),
  salary DECIMAL(10, 2)
);
 
CREATE INDEX idx_employees_manager_id ON employees(manager_id);

Contoh query (fetch employee dengan manager details):

Self-join untuk hierarchy

SELECT 
  e.id,
  e.name,
  e.email,
  e.department,
  m.id AS manager_id,
  m.name AS manager_name,
  m.email AS manager_email
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE e.id = 15;

Recursive query (fetch entire reporting chain):

Recursive CTE untuk hierarchy

WITH RECURSIVE reporting_chain AS (
  -- Base case: mulai dengan employee
  SELECT 
    id,
    name,
    manager_id,
    1 AS level
  FROM employees
  WHERE id = 15
  
  UNION ALL
  
  -- Recursive case: dapatkan manager's manager, dll.
  SELECT 
    e.id,
    e.name,
    e.manager_id,
    rc.level + 1
  FROM employees e
  INNER JOIN reporting_chain rc ON e.id = rc.manager_id
  WHERE rc.level < 10  -- Prevent infinite loops
)
SELECT * FROM reporting_chain
ORDER BY level;

Recursive CTE ini berjalan naik di organizational hierarchy, menunjukkan chain of command setiap employee.

#Tipe-Tipe Join Dijelaskan

#INNER JOIN

Mengembalikan hanya baris di mana join condition cocok di kedua tabel.

INNER JOIN example

SELECT 
  c.name,
  o.id AS order_id,
  o.total_amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.total_amount > 100;

Ini mengembalikan hanya customers yang memiliki orders lebih dari $100. Customers tanpa orders dikecualikan.

#LEFT JOIN (LEFT OUTER JOIN)

Mengembalikan semua baris dari tabel kiri, dengan baris yang cocok dari tabel kanan. Baris yang tidak cocok memiliki nilai NULL untuk kolom tabel kanan.

LEFT JOIN example

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;

Ini mengembalikan semua customers, bahkan yang tanpa orders (order_count akan 0).

#RIGHT JOIN (RIGHT OUTER JOIN)

Kebalikan dari LEFT JOIN. Mengembalikan semua baris dari tabel kanan.

RIGHT JOIN example

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

Dalam praktik, RIGHT JOIN jarang digunakan. Anda selalu bisa menulis ulangnya sebagai LEFT JOIN dengan menukar urutan tabel.

#FULL OUTER JOIN

Mengembalikan semua baris dari kedua tabel, dengan NULLs di mana matches tidak ada.

FULL OUTER JOIN example

SELECT 
  c.id,
  c.name,
  o.id AS order_id,
  o.total_amount
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

Berguna untuk menemukan unmatched records di salah satu tabel.

#CROSS JOIN

Mengembalikan Cartesian product—setiap baris dari tabel kiri dipasangkan dengan setiap baris dari tabel kanan.

CROSS JOIN example

SELECT 
  c.name,
  p.name
FROM customers c
CROSS JOIN products p;

Ini mengembalikan 1,000 baris jika Anda memiliki 10 customers dan 100 products. Gunakan dengan hemat—mudah untuk secara tidak sengaja membuat massive result sets.

#Membangun Complete E-Commerce Schema

Mari kita desain skema realistis yang menggabungkan multiple relationship types:

Complete e-commerce schema

-- Customers
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  phone VARCHAR(20),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Products
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL,
  stock_quantity INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Categories (many-to-many dengan products)
CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  slug VARCHAR(100) UNIQUE
);
 
CREATE TABLE product_categories (
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  category_id INTEGER NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
  PRIMARY KEY (product_id, category_id)
);
 
-- Orders (one-to-many dengan customers)
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  total_amount DECIMAL(10, 2),
  status VARCHAR(50) DEFAULT 'pending'
);
 
-- Order Items (one-to-many dengan orders, many-to-one dengan products)
CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  subtotal DECIMAL(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);
 
-- Reviews (one-to-many dengan products dan customers)
CREATE TABLE reviews (
  id SERIAL PRIMARY KEY,
  product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  rating INTEGER CHECK (rating >= 1 AND rating <= 5),
  title VARCHAR(255),
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(product_id, customer_id)
);
 
-- Indexes untuk performa
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_reviews_product_id ON reviews(product_id);
CREATE INDEX idx_reviews_customer_id ON reviews(customer_id);
CREATE INDEX idx_product_categories_category_id ON product_categories(category_id);

#Complex Query Patterns

#Aggregating Across Multiple Joins

Fetch customers dengan order count dan average order value mereka:

Aggregation dengan multiple joins

SELECT 
  c.id,
  c.name,
  c.email,
  COUNT(DISTINCT o.id) AS total_orders,
  COALESCE(AVG(o.total_amount), 0) AS avg_order_value,
  COALESCE(SUM(o.total_amount), 0) AS lifetime_value
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name, c.email
ORDER BY lifetime_value DESC;

DISTINCT dalam COUNT(DISTINCT o.id) mencegah double-counting jika customer muncul multiple times karena joins lain.

#Filtering After Aggregation

Temukan customers dengan lebih dari 5 orders:

HAVING clause untuk post-aggregation filtering

SELECT 
  c.id,
  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
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

Gunakan HAVING untuk conditions pada aggregated values, bukan WHERE (yang memfilter sebelum aggregation).

Dapatkan products dengan category list dan average rating mereka:

Subquery untuk aggregated related data

SELECT 
  p.id,
  p.name,
  p.price,
  STRING_AGG(c.name, ', ') AS categories,
  ROUND(AVG(r.rating)::NUMERIC, 2) AS avg_rating,
  COUNT(r.id) AS review_count
FROM products p
LEFT JOIN product_categories pc ON p.id = pc.product_id
LEFT JOIN categories c ON pc.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
GROUP BY p.id, p.name, p.price
ORDER BY avg_rating DESC NULLS LAST;

STRING_AGG menggabungkan category names menjadi single string. NULLS LAST menempatkan products tanpa reviews di akhir.

#Window Functions untuk Ranking

Rank products berdasarkan sales dalam setiap category:

Window functions untuk ranking

SELECT 
  c.name AS category,
  p.name AS product,
  SUM(oi.quantity) AS units_sold,
  ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY SUM(oi.quantity) DESC) AS rank_in_category
FROM categories c
INNER JOIN product_categories pc ON c.id = pc.category_id
INNER JOIN products p ON pc.product_id = p.id
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY c.id, c.name, p.id, p.name
ORDER BY c.name, rank_in_category;

ROW_NUMBER() OVER (PARTITION BY c.id ORDER BY ...) menetapkan rank dalam setiap category.

Common Mistakes dan Pitfalls

#Mistake 1: Lupa Indexes pada Foreign Keys

Bad: Tidak ada index pada foreign key

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total_amount DECIMAL(10, 2)
);
-- Missing index pada customer_id!

Tanpa index, queries yang memfilter berdasarkan customer_id memindai seluruh tabel. Selalu index foreign keys:

Good: Index pada foreign key

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

#Mistake 2: Menggunakan INNER JOIN Ketika Anda Butuh LEFT JOIN

Bad: Mengecualikan customers tanpa orders

SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

Ini mengembalikan hanya customers dengan orders. Jika Anda ingin semua customers (termasuk yang tanpa orders), gunakan LEFT JOIN.

#Mistake 3: Cartesian Product dari Multiple Joins

Bad: Unintended Cartesian product

SELECT 
  c.name,
  o.id,
  oi.id
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id;

Jika customer memiliki 10 orders dan setiap order memiliki 5 items, Anda mendapatkan 50 baris (10 × 5). Ini benar untuk query ini, tetapi mudah untuk secara tidak sengaja mengalikan baris. Selalu verifikasi ukuran result set Anda.

#Mistake 4: Aggregating Tanpa Grouping Correctly

Bad: Incorrect grouping dengan multiple joins

SELECT 
  c.id,
  c.name,
  COUNT(o.id) AS order_count,
  COUNT(r.id) AS review_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN reviews r ON c.id = r.customer_id
GROUP BY c.id, c.name;

Ini double-counts karena setiap order-review combination membuat baris. Fixnya adalah aggregate secara terpisah:

Good: Separate aggregations

SELECT 
  c.id,
  c.name,
  (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count,
  (SELECT COUNT(*) FROM reviews WHERE customer_id = c.id) AS review_count
FROM customers c;

Atau gunakan window functions:

Good: Window functions

SELECT DISTINCT
  c.id,
  c.name,
  COUNT(o.id) OVER (PARTITION BY c.id) AS order_count,
  COUNT(r.id) OVER (PARTITION BY c.id) AS review_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN reviews r ON c.id = r.customer_id;

#Mistake 5: Tidak Menggunakan DISTINCT dengan Many-to-Many Joins

Bad: Duplicate rows dari many-to-many

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id;

Jika customer memiliki 1 order dengan 5 items, Anda mendapatkan 5 baris untuk customer itu. Gunakan DISTINCT jika Anda ingin unique customers:

Good: DISTINCT untuk unique rows

SELECT DISTINCT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN order_items oi ON o.id = oi.order_id;

#Query Optimization Techniques

#Gunakan EXPLAIN untuk Memahami Query Plans

Analyze query execution

EXPLAIN ANALYZE
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;

Cari sequential scans pada tabel besar—mereka menunjukkan missing indexes. Nested loops baik untuk small result sets tetapi problematic pada scale.

#Denormalization untuk Read-Heavy Workloads

Jika Anda sering query customer lifetime value, pertimbangkan menyimpannya di customers table:

Denormalized column untuk performa

ALTER TABLE customers ADD COLUMN lifetime_value DECIMAL(10, 2) DEFAULT 0;
 
-- Update via trigger atau batch job
UPDATE customers c
SET lifetime_value = COALESCE(
  (SELECT SUM(total_amount) FROM orders WHERE customer_id = c.id),
  0
);

Trade-off: Anda harus menjaga nilai ini tetap sinkron. Gunakan triggers atau scheduled jobs.

#Materialized Views untuk Complex Aggregations

Materialized view untuk expensive queries

CREATE MATERIALIZED VIEW customer_metrics AS
SELECT 
  c.id,
  c.name,
  COUNT(DISTINCT o.id) AS total_orders,
  COALESCE(SUM(o.total_amount), 0) AS lifetime_value,
  COALESCE(AVG(r.rating), 0) AS avg_product_rating
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
LEFT JOIN reviews r ON c.id = r.customer_id
GROUP BY c.id, c.name;
 
CREATE INDEX idx_customer_metrics_lifetime_value ON customer_metrics(lifetime_value DESC);
 
-- Refresh secara berkala
REFRESH MATERIALIZED VIEW customer_metrics;

Materialized views menyimpan query results di disk. Mereka stale sampai di-refresh tetapi jauh lebih cepat daripada computing on-the-fly.

#Best Practices

#1. Selalu Index Foreign Keys

Foreign keys adalah join conditions. Tanpa indexes, joins menjadi full table scans.

#2. Gunakan Meaningful Relationship Names

Clear naming conventions

-- Good: Foreign key name menunjukkan relationship
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer_id 
  FOREIGN KEY (customer_id) REFERENCES customers(id);
 
-- Good: Junction table name deskriptif
CREATE TABLE product_categories (
  product_id INTEGER,
  category_id INTEGER
);

#3. Normalize ke Third Normal Form (3NF)

Eliminasi transitive dependencies. Jika column A tergantung pada B, dan B tergantung pada C, maka A tidak seharusnya di tabel yang sama dengan C.

#4. Gunakan Surrogate Keys (Auto-Incrementing IDs)

Surrogate keys untuk fleksibilitas

-- Good: Surrogate key
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  sku VARCHAR(50) UNIQUE,
  name VARCHAR(255)
);
 
-- Avoid: Natural key yang mungkin berubah
CREATE TABLE products (
  sku VARCHAR(50) PRIMARY KEY,
  name VARCHAR(255)
);

Surrogate keys stabil dan efisien untuk joins. Natural keys (seperti SKU) bisa berubah, breaking relationships.

#5. Dokumentasikan Schema Anda

Schema documentation dengan comments

COMMENT ON TABLE customers IS 'Core customer records';
COMMENT ON COLUMN customers.email IS 'Unique email address, digunakan untuk login';
COMMENT ON TABLE orders IS 'Customer orders dengan timestamps dan totals';
COMMENT ON COLUMN orders.customer_id IS 'Foreign key ke customers table';

Gunakan \d+ table_name di psql untuk melihat comments.

#6. Test Queries dengan Real Data Volumes

Query yang cepat dengan 1,000 baris mungkin lambat dengan 1 juta. Selalu test dengan production-like data.

#Kapan TIDAK Menggunakan Joins

#Scenario 1: Denormalized Data Warehouses

Dalam sistem OLAP (data warehouses), denormalization adalah standard. Joins expensive pada scale, jadi data pre-aggregated.

#Scenario 2: Document Databases

Jika Anda menggunakan MongoDB atau similar, embedding related data sering lebih baik daripada joins. Document databases tidak memiliki efficient joins.

#Scenario 3: Real-Time Analytics

Untuk high-frequency queries, pre-computed aggregations (materialized views, caches) mengalahkan joins setiap saat.

#Scenario 4: Microservices Architecture

Jika data hidup di separate services, joins terjadi di application code, bukan SQL. Pertimbangkan API calls atau event-driven synchronization.

#Kesimpulan

SQL joins powerful karena mereka menerapkan data integrity sambil menjaga storage efisien. Kuncinya adalah memahami relationships Anda—one-to-one, one-to-many, many-to-many, polymorphic, dan self-referential—dan memilih join type yang tepat untuk setiap query.

Mulai dengan normalized schema, index foreign keys Anda, dan gunakan EXPLAIN ANALYZE untuk menangkap performance issues lebih awal. Saat sistem Anda tumbuh, denormalization dan materialized views menjadi tools, bukan shortcuts.

E-commerce schema yang kami bangun mencakup sebagian besar pola dunia nyata. Gunakan sebagai template untuk desain Anda sendiri, dan Anda akan menulis efficient queries yang scale.

Langkah selanjutnya: Bangun schema untuk domain Anda, tulis queries terhadapnya, dan analisis execution plans. Pola yang Anda pelajari akan transfer ke database relasional apapun.

Menguasai SQL Joins dan Relationships di PostgreSQL

Related Posts