Database!

Topics

Normalization
Queries and Indexes
Advanced Topics
Anti-Patterns

Normalization

n-normal Forms

1-NF First Normal Form
2NF Second Normal Form
3NF Third Normal Form
BCNF Boyce-Codd Normal Form (3.5NF?)
EKNF Elementary Key Normal Form
4NF Fourth Normal Form
ETNF Essential Tuple Normal Form
5NF Fifth Normal Form
DKNF Domain Key Normal Form
6NF Sixth Normal Form

First Normal Form

Single Value
Unique Types
Unique Columns

Second Normal Form

At 1NF
Single Column Primary Key

Third Normal Form

At 2NF
No Transitive Dependency

Queries and Indexes

Relational Algebra

Projection, Selection, Rename
Joins

SQL 101

SELECT, WHERE
GROUP BY, HAVING
INNER JOIN, LEFT/RIGHT JOIN

Indexes

Types of Indexes
When to use Indexes
When not to use Indexes

Advanced Topics

Advanced SQL

Subqueries
Class Table Extensions
Lateral Joins
Window Functions
Materialized Views

Class Table Extensions

WITH regional_sales AS (
        SELECT region, SUM(amount) AS total_sales
        FROM orders
        GROUP BY region
     ), top_regions AS (
        SELECT region
        FROM regional_sales
        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
     )
SELECT region,
       product,
       SUM(quantity) AS product_units,
       SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;

Lateral Joins

SELECT user_id, first_order_time, next_order_time, id FROM
  (SELECT 
        user_id, 
        min(created_at) AS first_order_time 
        FROM orders GROUP BY user_id) o1
  LEFT JOIN LATERAL
  (SELECT id, created_at AS next_order_time
   FROM orders
   WHERE user_id = o1.user_id AND created_at > o1.first_order_time
   ORDER BY created_at ASC LIMIT 1)
   o2 ON true;

Window Functions

SELECT sum(salary) OVER w, avg(salary) OVER w
  FROM empsalary
  WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Materialized Views

CREATE TABLE invoice (
    invoice_no    integer        PRIMARY KEY,
    seller_no     integer,       -- ID of salesperson
    invoice_date  date,          -- date of sale
    invoice_amt   numeric(13,2)  -- amount of sale
);

CREATE MATERIALIZED VIEW sales_summary AS
  SELECT
      seller_no,
      invoice_date,
      sum(invoice_amt)::numeric(13,2) as sales_amt
    FROM invoice
    WHERE invoice_date < CURRENT_DATE
    GROUP BY
      seller_no,
      invoice_date
    ORDER BY
      seller_no,
      invoice_date;

CREATE UNIQUE INDEX sales_summary_seller
  ON sales_summary (seller_no, invoice_date);

Design Patterns

Use Cases

Tree/Nested Structure
Polymorphic Association
Entity/Attribute Value
Metaprogramming
Full-text Search
Extensions

Tree Nested Structure

Adjacency Tree
Path
MPTT

Polymorphic Association

Content Table Pattern
Single Table Inheritance
Individual Tree

EAV

Template Pattern
Use Cases
Pitfalls

Metaprogramming

Information Schema
Postgres Commands
Explain/Analyze

Metaprogramming

select 
    (select cls.relname from pg_class cls where cls.oid = cnstr.conrelid) as tbl, 
    (select array_agg(attname) from pg_attribute 
       where 
            attrelid = cnstr.conrelid and ARRAY[attnum] <@ cnstr.conkey) as fk_col, 
    (select cls.relname from pg_class cls where cls.oid = cnstr.confrelid) as target 
    from pg_constraint cnstr 
    where 
        cnstr.confrelid = (select oid from pg_class where relname = 'auth_user');

select table_name 
from information_schema.columns 
where 
    column_name ilike '%name%' and 
    table_name not ilike 'pg_%';

Explain/Analyze

Extensions

Full Text Search
PostGIS
Others (CIText, tablefunc)

Full-text Search

SELECT title
FROM pgweb
WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;

Pivot Table

SELECT *
FROM crosstab(
  'SELECT
    a date,
    b.desc AS os,
    (random() * 10000 + 1)::int AS value
     FROM generate_series(
        (now() - ''100 days''::interval)::date, 
        now()::date, ''1 DAY''::interval) a,
        (SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux'']) AS DESC) b 
     ORDER BY 1,2
  ','SELECT unnest(ARRAY[''OSX'', ''Windows'', ''Linux''])'
) 
AS ct(date date, OSX int, Windows int, Linux int);