Database Fundamentals - 2/4

From Basic CRUD to Professional Database Operations

You understand tables, relationships, and basic SQL operations. You can INSERT users, SELECT posts, and UPDATE profiles. Your queries work, your data gets stored, and your APIs return the right information. But here’s what separates hobbyist database usage from professional-grade data management: the ability to handle complex queries, maintain data integrity, and optimize for real-world performance.

The performance reality check:

-- Your basic JOIN works fine with 1,000 users
SELECT u.username, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.is_active = true;
-- Query time: 5ms

-- Same query with 10 million users and 50 million posts
SELECT u.username, p.title
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE u.is_active = true;
-- Query time: 45 seconds (your users are gone)

-- The problem: Missing indexes, poor JOIN strategy, no query optimization

The data integrity nightmare:

-- Looks innocent enough
INSERT INTO orders (customer_id, product_id, quantity)
VALUES (123, 456, 5);

-- But what happens when:
-- - customer_id 123 doesn't exist?
-- - product_id 456 is out of stock?
-- - quantity is negative?
-- - Price changes while order is being processed?
-- - Database crashes during the transaction?

-- Without proper constraints, transactions, and normalization,
-- your data becomes an inconsistent mess

The uncomfortable truth: Basic SQL gets you started, but production applications require advanced techniques that prevent performance disasters, maintain data consistency under concurrent load, and handle complex business logic at the database level.

Professional database operations require mastery of:

  • Advanced JOIN patterns that efficiently combine data across multiple tables
  • Subqueries and CTEs that break complex problems into manageable parts
  • Database normalization that prevents data duplication and inconsistency
  • Indexes and optimization that maintain millisecond response times under load
  • Transactions and ACID properties that ensure data integrity during failures

This article transforms you from someone who writes basic queries into someone who architects data operations that scale, perform, and maintain integrity in production environments.

Advanced SQL: JOINs, Subqueries, and Aggregations

Mastering JOIN Operations

JOINs are the backbone of relational database queries, but most developers only scratch the surface:

-- INNER JOIN: Only records that match in both tables
SELECT
  u.username,
  u.email,
  p.title,
  p.created_at
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE p.published = true;

-- LEFT JOIN: All records from left table, matching records from right
SELECT
  u.username,
  u.email,
  COUNT(p.id) as post_count,
  COALESCE(AVG(p.view_count), 0) as avg_views
FROM users u
LEFT JOIN posts p ON u.id = p.user_id AND p.published = true
GROUP BY u.id, u.username, u.email
ORDER BY post_count DESC;

-- RIGHT JOIN: All records from right table, matching from left
SELECT
  c.name as category_name,
  COUNT(p.id) as post_count
FROM posts p
RIGHT JOIN categories c ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY post_count DESC;

-- FULL OUTER JOIN: All records from both tables
SELECT
  u.username,
  p.title,
  CASE
    WHEN u.id IS NULL THEN 'Orphaned Post'
    WHEN p.id IS NULL THEN 'User Without Posts'
    ELSE 'Valid Relationship'
  END as relationship_status
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

Complex multi-table JOINs for real business queries:

-- E-commerce analytics: Customer lifetime value
SELECT
  c.id,
  c.email,
  c.first_name || ' ' || c.last_name as full_name,
  c.created_at as customer_since,
  COUNT(DISTINCT o.id) as total_orders,
  SUM(o.total_amount) as lifetime_value,
  AVG(o.total_amount) as avg_order_value,
  MAX(o.order_date) as last_order_date,
  STRING_AGG(DISTINCT cat.name, ', ') as purchased_categories
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
  AND o.status = 'completed'
LEFT JOIN order_items oi ON o.id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.id
LEFT JOIN categories cat ON p.category_id = cat.id
WHERE c.created_at >= '2024-01-01'
GROUP BY c.id, c.email, c.first_name, c.last_name, c.created_at
HAVING SUM(o.total_amount) > 100
ORDER BY lifetime_value DESC NULLS LAST
LIMIT 100;

Subqueries and Common Table Expressions (CTEs)

Break complex problems into manageable pieces:

-- Subquery in WHERE clause: Find users with above-average post count
SELECT
  username,
  email,
  (SELECT COUNT(*) FROM posts WHERE user_id = users.id) as post_count
FROM users
WHERE (
  SELECT COUNT(*)
  FROM posts
  WHERE user_id = users.id
) > (
  SELECT AVG(post_count)
  FROM (
    SELECT COUNT(*) as post_count
    FROM posts
    GROUP BY user_id
  ) user_post_counts
);

-- Better approach using CTE: Same logic, more readable
WITH user_post_stats AS (
  SELECT
    user_id,
    COUNT(*) as post_count
  FROM posts
  GROUP BY user_id
),
avg_posts AS (
  SELECT AVG(post_count) as avg_post_count
  FROM user_post_stats
)
SELECT
  u.username,
  u.email,
  ups.post_count
FROM users u
JOIN user_post_stats ups ON u.id = ups.user_id
CROSS JOIN avg_posts ap
WHERE ups.post_count > ap.avg_post_count
ORDER BY ups.post_count DESC;

Complex business logic with recursive CTEs:

-- Organizational hierarchy: Find all employees under a manager
WITH RECURSIVE employee_hierarchy AS (
  -- Base case: Direct reports
  SELECT
    id,
    name,
    manager_id,
    position,
    1 as level
  FROM employees
  WHERE manager_id = 123  -- Specific manager ID

  UNION ALL

  -- Recursive case: Reports of reports
  SELECT
    e.id,
    e.name,
    e.manager_id,
    e.position,
    eh.level + 1
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
  WHERE eh.level < 5  -- Prevent infinite recursion
)
SELECT
  REPEAT('  ', level - 1) || name as indented_name,
  position,
  level
FROM employee_hierarchy
ORDER BY level, name;

Advanced Aggregation Patterns

Window functions for sophisticated analytics:

-- Sales performance analysis with rankings and comparisons
WITH monthly_sales AS (
  SELECT
    sales_rep_id,
    DATE_TRUNC('month', sale_date) as month,
    SUM(amount) as monthly_total,
    COUNT(*) as deals_closed
  FROM sales
  GROUP BY sales_rep_id, DATE_TRUNC('month', sale_date)
)
SELECT
  sr.name as sales_rep,
  ms.month,
  ms.monthly_total,
  ms.deals_closed,

  -- Ranking within each month
  RANK() OVER (
    PARTITION BY ms.month
    ORDER BY ms.monthly_total DESC
  ) as monthly_rank,

  -- Running total for the year
  SUM(ms.monthly_total) OVER (
    PARTITION BY ms.sales_rep_id
    ORDER BY ms.month
    ROWS UNBOUNDED PRECEDING
  ) as ytd_total,

  -- Comparison to previous month
  LAG(ms.monthly_total) OVER (
    PARTITION BY ms.sales_rep_id
    ORDER BY ms.month
  ) as previous_month,

  -- Percentage change from previous month
  ROUND(
    ((ms.monthly_total - LAG(ms.monthly_total) OVER (
      PARTITION BY ms.sales_rep_id ORDER BY ms.month
    )) / NULLIF(LAG(ms.monthly_total) OVER (
      PARTITION BY ms.sales_rep_id ORDER BY ms.month
    ), 0)) * 100,
    2
  ) as month_over_month_change
FROM monthly_sales ms
JOIN sales_reps sr ON ms.sales_rep_id = sr.id
ORDER BY ms.month DESC, ms.monthly_total DESC;

Pivot queries for reporting:

-- Transform rows to columns: Monthly sales by product category
SELECT
  product_category,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1 THEN amount END) as jan_sales,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2 THEN amount END) as feb_sales,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3 THEN amount END) as mar_sales,
  SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4 THEN amount END) as apr_sales,
  SUM(amount) as total_sales
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE EXTRACT(YEAR FROM order_date) = 2024
GROUP BY product_category
ORDER BY total_sales DESC;

-- PostgreSQL native CROSSTAB (requires tablefunc extension)
SELECT * FROM crosstab(
  'SELECT product_category,
          EXTRACT(MONTH FROM order_date)::text as month,
          SUM(amount)
   FROM orders o
   JOIN order_items oi ON o.id = oi.order_id
   JOIN products p ON oi.product_id = p.id
   WHERE EXTRACT(YEAR FROM order_date) = 2024
   GROUP BY 1, 2
   ORDER BY 1, 2',
  'VALUES (''1''), (''2''), (''3''), (''4''), (''5''), (''6''),
          (''7''), (''8''), (''9''), (''10''), (''11''), (''12'')'
) AS ct(category text, jan numeric, feb numeric, mar numeric, apr numeric,
         may numeric, jun numeric, jul numeric, aug numeric,
         sep numeric, oct numeric, nov numeric, dec numeric);

Database Normalization: Organizing Data for Consistency

Understanding Normal Forms

Normalization eliminates data redundancy and prevents inconsistencies:

-- ❌ Unnormalized table: Redundant data, update anomalies
CREATE TABLE orders_denormalized (
  order_id SERIAL PRIMARY KEY,
  customer_name VARCHAR(100),
  customer_email VARCHAR(255),
  customer_address TEXT,
  product_name VARCHAR(255),
  product_price DECIMAL(10,2),
  product_category VARCHAR(100),
  quantity INTEGER,
  order_date TIMESTAMP
);

-- Problems with denormalized structure:
-- 1. If customer changes email, must update multiple rows
-- 2. If product price changes, historical orders become inconsistent
-- 3. Cannot add products without orders
-- 4. Wasted storage space from repeated data

First Normal Form (1NF): Atomic values, no repeating groups:

-- ❌ Violates 1NF: Multiple phone numbers in one field
CREATE TABLE customers_bad (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  phones VARCHAR(255)  -- "123-456-7890, 987-654-3210, 555-123-4567"
);

-- ✅ 1NF compliant: Separate table for phone numbers
CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(255)
);

CREATE TABLE customer_phones (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  phone_number VARCHAR(20),
  phone_type VARCHAR(20) -- 'mobile', 'home', 'work'
);

Second Normal Form (2NF): 1NF + no partial dependencies:

-- ❌ Violates 2NF: product_price depends only on product_id, not the full key
CREATE TABLE order_items_bad (
  order_id INTEGER,
  product_id INTEGER,
  product_name VARCHAR(255),  -- Depends only on product_id
  product_price DECIMAL(10,2), -- Depends only on product_id
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id)
);

-- ✅ 2NF compliant: Separate product information
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  price DECIMAL(10,2),
  category VARCHAR(100)
);

CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER,
  unit_price DECIMAL(10,2), -- Price at time of order
  PRIMARY KEY (order_id, product_id)
);

Third Normal Form (3NF): 2NF + no transitive dependencies:

-- ❌ Violates 3NF: category_description depends on category, not employee_id
CREATE TABLE employees_bad (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  department_budget DECIMAL(12,2) -- Transitive dependency
);

-- ✅ 3NF compliant: Separate department information
CREATE TABLE departments (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  budget DECIMAL(12,2),
  manager_id INTEGER
);

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department_id INTEGER REFERENCES departments(id),
  position VARCHAR(100),
  salary DECIMAL(10,2)
);

Strategic Denormalization

Sometimes you need to break normalization rules for performance:

-- Normalized structure (3NF compliant)
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  order_date TIMESTAMP,
  status VARCHAR(20)
);

CREATE TABLE order_items (
  order_id INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER,
  unit_price DECIMAL(10,2)
);

-- Problem: Calculating order total requires JOINs every time
SELECT
  o.id,
  SUM(oi.quantity * oi.unit_price) as total
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
GROUP BY o.id;

-- Denormalized solution: Add calculated field for performance
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10,2);

-- Maintain consistency with triggers or application logic
CREATE OR REPLACE FUNCTION update_order_total()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE orders
  SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
  )
  WHERE id = COALESCE(NEW.order_id, OLD.order_id);

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_order_total_trigger
  AFTER INSERT OR UPDATE OR DELETE ON order_items
  FOR EACH ROW EXECUTE FUNCTION update_order_total();

Indexes and Query Optimization: Speed That Scales

Understanding Index Types

Indexes are your database’s search engine:

-- B-tree indexes (default): Great for equality and range queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_created_at ON posts(created_at);
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- Hash indexes: Perfect for equality lookups (PostgreSQL)
CREATE INDEX idx_sessions_token ON user_sessions USING HASH(session_token);

-- Partial indexes: Only index rows that meet certain conditions
CREATE INDEX idx_active_users_email
ON users(email)
WHERE is_active = true;

-- Expression indexes: Index computed values
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

-- Full-text search indexes
CREATE INDEX idx_posts_fulltext
ON posts USING GIN(to_tsvector('english', title || ' ' || content));

Composite indexes and column order matter:

-- Multi-column index: Order is critical
CREATE INDEX idx_user_posts ON posts(user_id, created_at, published);

-- This index efficiently supports these queries:
SELECT * FROM posts WHERE user_id = 123;
SELECT * FROM posts WHERE user_id = 123 AND created_at > '2024-01-01';
SELECT * FROM posts WHERE user_id = 123 AND created_at > '2024-01-01' AND published = true;

-- But NOT this query (created_at is not leftmost):
SELECT * FROM posts WHERE created_at > '2024-01-01';

-- You'd need a separate index for that:
CREATE INDEX idx_posts_created_at ON posts(created_at);

Query Performance Analysis

Use EXPLAIN to understand query execution:

-- Basic explain
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Detailed analysis with actual execution times
EXPLAIN (ANALYZE, BUFFERS)
SELECT
  u.username,
  COUNT(p.id) as post_count,
  AVG(p.view_count) as avg_views
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id, u.username
HAVING COUNT(p.id) > 10
ORDER BY avg_views DESC
LIMIT 20;

-- Understanding the output:
-- Seq Scan = Table scan (bad for large tables)
-- Index Scan = Using an index (good)
-- Nested Loop = Join method (can be slow)
-- Hash Join = Better for large datasets
-- Sort = Expensive operation
-- Limit = Early termination (good)

Common performance anti-patterns and fixes:

-- ❌ Function in WHERE clause prevents index usage
SELECT * FROM users
WHERE UPPER(username) = 'ALICE';

-- ✅ Use functional index or change query
CREATE INDEX idx_users_upper_username ON users(UPPER(username));
-- OR
SELECT * FROM users
WHERE username = 'alice';

-- ❌ Leading wildcards prevent index usage
SELECT * FROM products
WHERE name LIKE '%phone%';

-- ✅ Use full-text search or restructure query
SELECT * FROM products
WHERE to_tsvector('english', name) @@ to_tsquery('phone');

-- ❌ OR conditions can prevent index usage
SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing';

-- ✅ Use IN clause or UNION
SELECT * FROM orders
WHERE status IN ('pending', 'processing');

Index maintenance and monitoring:

-- Check index usage statistics
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find unused indexes (candidates for removal)
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Identify missing indexes using pg_stat_statements
SELECT
  query,
  calls,
  total_time,
  mean_time,
  rows
FROM pg_stat_statements
WHERE query LIKE '%Seq Scan%'
ORDER BY total_time DESC
LIMIT 10;

Transactions and ACID Properties: Data Integrity Under Pressure

Understanding ACID Properties

ACID ensures your data stays consistent even when things go wrong:

Atomicity: All or nothing

-- Bank transfer: Both operations must succeed or both must fail
BEGIN;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 'alice_account';

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 'bob_account';

-- If either UPDATE fails, ROLLBACK undoes both
-- If both succeed, COMMIT makes changes permanent
COMMIT;

Consistency: Data integrity rules are maintained

-- Constraints ensure consistency
CREATE TABLE accounts (
  id VARCHAR(50) PRIMARY KEY,
  balance DECIMAL(15,2) CHECK (balance >= 0), -- No negative balances
  account_type VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Foreign key constraints maintain referential integrity
CREATE TABLE transactions (
  id SERIAL PRIMARY KEY,
  from_account VARCHAR(50) REFERENCES accounts(id),
  to_account VARCHAR(50) REFERENCES accounts(id),
  amount DECIMAL(15,2) CHECK (amount > 0),
  transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Isolation: Concurrent transactions don’t interfere

-- Different isolation levels handle concurrent access differently

-- READ COMMITTED (default): Sees committed changes from other transactions
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM accounts WHERE id = 'alice';
-- Another transaction might change Alice's balance here
UPDATE accounts SET balance = balance - 100 WHERE id = 'alice';
COMMIT;

-- REPEATABLE READ: Consistent snapshot throughout transaction
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 'alice'; -- Returns 1000
-- Another transaction changes Alice's balance to 500
SELECT balance FROM accounts WHERE id = 'alice'; -- Still returns 1000
COMMIT;

-- SERIALIZABLE: Strongest isolation, prevents all anomalies
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Transaction will fail if conflicts with concurrent transactions
SELECT SUM(balance) FROM accounts WHERE account_type = 'checking';
UPDATE accounts SET balance = balance * 1.02 WHERE account_type = 'checking';
COMMIT;

Durability: Committed changes survive system failures

-- Database writes to persistent storage before confirming COMMIT
-- Configure durability vs performance trade-offs:

-- Maximum durability (slower)
SET synchronous_commit = on;
SET wal_sync_method = fdatasync;

-- Balanced durability and performance
SET synchronous_commit = local;
SET checkpoint_segments = 32;

Advanced Transaction Patterns

Savepoints for partial rollbacks:

BEGIN;

INSERT INTO orders (customer_id, order_date)
VALUES (123, CURRENT_TIMESTAMP);

SAVEPOINT after_order;

-- Try to add items
INSERT INTO order_items (order_id, product_id, quantity)
SELECT currval('orders_id_seq'), product_id, quantity
FROM cart_items
WHERE customer_id = 123;

-- Check if we have enough stock
IF (SELECT COUNT(*) FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    WHERE oi.order_id = currval('orders_id_seq')
    AND p.stock_quantity < oi.quantity) > 0 THEN

  -- Rollback just the items, keep the order
  ROLLBACK TO SAVEPOINT after_order;

  -- Add only available items
  INSERT INTO order_items (order_id, product_id, quantity)
  SELECT
    currval('orders_id_seq'),
    ci.product_id,
    LEAST(ci.quantity, p.stock_quantity)
  FROM cart_items ci
  JOIN products p ON ci.product_id = p.id
  WHERE ci.customer_id = 123
    AND p.stock_quantity > 0;
END IF;

COMMIT;

Handling deadlocks gracefully:

// Application-level deadlock handling
const transferMoney = async (fromAccount, toAccount, amount) => {
  const maxRetries = 3;
  let retries = 0;

  while (retries < maxRetries) {
    try {
      await db.transaction(async (trx) => {
        // Always lock accounts in same order to prevent deadlocks
        const [account1, account2] = [fromAccount, toAccount].sort();

        await trx("accounts").where("id", account1).forUpdate();

        await trx("accounts").where("id", account2).forUpdate();

        // Perform transfer
        await trx("accounts")
          .where("id", fromAccount)
          .decrement("balance", amount);

        await trx("accounts")
          .where("id", toAccount)
          .increment("balance", amount);
      });

      return { success: true };
    } catch (error) {
      if (error.code === "40P01") {
        // Deadlock detected
        retries++;
        await new Promise((resolve) =>
          setTimeout(resolve, Math.random() * 100)
        );
      } else {
        throw error;
      }
    }
  }

  throw new Error("Transfer failed after maximum retries");
};

Database Constraints: Enforcing Business Rules

Primary and foreign key constraints:

-- Composite primary keys
CREATE TABLE user_roles (
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  assigned_by INTEGER REFERENCES users(id),
  PRIMARY KEY (user_id, role_id)
);

-- Foreign key actions
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE, -- Delete posts when user deleted
  category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL -- Set to NULL if category deleted
);

Check constraints for business logic:

-- Data validation constraints
CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) CHECK (price > 0),
  stock_quantity INTEGER CHECK (stock_quantity >= 0),
  rating DECIMAL(3,2) CHECK (rating >= 0 AND rating <= 5),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  -- Multi-column constraints
  CONSTRAINT valid_product CHECK (
    (stock_quantity > 0 AND price > 0) OR
    (stock_quantity = 0 AND price >= 0)
  )
);

-- Complex business rules
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  birth_date DATE NOT NULL,
  hire_date DATE DEFAULT CURRENT_DATE,
  salary DECIMAL(10,2) CHECK (salary > 0),
  department_id INTEGER REFERENCES departments(id),

  -- Employee must be at least 18 years old
  CONSTRAINT employee_age_check CHECK (
    AGE(hire_date, birth_date) >= INTERVAL '18 years'
  ),

  -- Hire date cannot be in the future
  CONSTRAINT hire_date_check CHECK (hire_date <= CURRENT_DATE)
);

Unique constraints and indexes:

-- Single column uniqueness
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  username VARCHAR(50) UNIQUE NOT NULL
);

-- Multi-column uniqueness
CREATE TABLE course_enrollments (
  id SERIAL PRIMARY KEY,
  student_id INTEGER REFERENCES students(id),
  course_id INTEGER REFERENCES courses(id),
  semester VARCHAR(20),
  year INTEGER,

  -- Student can only enroll once per course per semester
  UNIQUE (student_id, course_id, semester, year)
);

-- Conditional uniqueness using partial unique indexes
CREATE UNIQUE INDEX idx_active_user_sessions
ON user_sessions(user_id)
WHERE is_active = true;
-- Ensures each user has only one active session

Key Takeaways

Advanced SQL operations, proper normalization, strategic indexing, and transaction management separate professional database usage from basic CRUD operations. These techniques ensure your applications maintain data integrity, perform efficiently under load, and handle complex business requirements.

The advanced database mindset you need:

  • Think in sets, not loops: Use SQL’s power for bulk operations instead of row-by-row processing
  • Design for integrity first: Use constraints, normalization, and transactions to prevent data corruption
  • Optimize for your access patterns: Index the queries you run most frequently
  • Plan for concurrency: Design transactions that handle multiple users safely

What distinguishes professional database architecture:

  • Complex queries that efficiently join multiple tables and aggregate data for business insights
  • Proper normalization balanced with strategic denormalization for performance
  • Comprehensive indexing strategy that supports your application’s query patterns
  • Transaction design that maintains data consistency under concurrent load and system failures

What’s Next

We’ve mastered relational databases and SQL operations that handle complex business requirements. In the next article, we’ll explore the NoSQL universe: document databases like MongoDB, key-value stores like Redis, column-family databases like Cassandra, and graph databases like Neo4j. You’ll learn when to choose each type and how they solve problems that SQL databases handle poorly.

The relational foundation is solid—you understand tables, relationships, advanced queries, and data integrity. Next, we’ll explore the flexible, scalable world of NoSQL that powers modern web applications, real-time systems, and big data analytics.

You’re no longer just writing queries—you’re architecting data operations that maintain integrity, perform under pressure, and support sophisticated business logic. The database expertise deepens as we venture into the diverse NoSQL landscape.