Database normalization and schema design patterns

-- Star Schema (Data Warehouse)
CREATE TABLE fact_sales (
  sale_id BIGSERIAL PRIMARY KEY,
  date_key INT REFERENCES dim_date(date_key),
  product_key INT REFERENCES dim_product(product_key),
  customer_key INT REFERENCES dim_customer(customer_key),
  store_key INT REFERENCES dim_store(store_key),
  quantity INT,
  amount DECIMAL(10,2),
  cost DECIMAL(10,2),
  profit DECIMAL(10,2)
);

CREATE TABLE dim_date (
  date_key INT PRIMARY KEY,
  date DATE,
  year INT,
  quarter INT,
  month INT,
  day_of_week INT,
  is_weekend BOOLEAN
);

CREATE TABLE dim_product (
  product_key INT PRIMARY KEY,
  product_id INT,
  product_name VARCHAR(200),
  category VARCHAR(100),
  brand VARCHAR(100)
);

-- Polymorphic Associations
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  commentable_type VARCHAR(50),  -- 'Post', 'Photo', etc.
  commentable_id INT,
  content TEXT,
  user_id INT REFERENCES users(id)
);

CREATE INDEX idx_comments_polymorphic
  ON comments(commentable_type, commentable_id);

-- Better: Separate junction tables
CREATE TABLE post_comments (
  id SERIAL PRIMARY KEY,
  post_id INT REFERENCES posts(id),
  comment_id INT REFERENCES comments(id)
);

CREATE TABLE photo_comments (
  id SERIAL PRIMARY KEY,
  photo_id INT REFERENCES photos(id),
  comment_id INT REFERENCES comments(id)
);

-- Soft Deletes
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  content TEXT,
  deleted_at TIMESTAMP NULL
);

CREATE INDEX idx_posts_active ON posts(id) WHERE deleted_at IS NULL;

-- Soft delete
UPDATE posts SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1;

-- Query only active
SELECT * FROM posts WHERE deleted_at IS NULL;

-- Audit Trail / History Table
CREATE TABLE users_history (
  history_id SERIAL PRIMARY KEY,
  user_id INT,
  name VARCHAR(100),
  email VARCHAR(100),
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  changed_by INT,
  operation VARCHAR(10)  -- 'INSERT', 'UPDATE', 'DELETE'
);

CREATE OR REPLACE FUNCTION audit_users()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    INSERT INTO users_history (user_id, name, email, operation)
    VALUES (OLD.id, OLD.name, OLD.email, 'DELETE');
    RETURN OLD;
  ELSIF TG_OP = 'UPDATE' THEN
    INSERT INTO users_history (user_id, name, email, operation)
    VALUES (NEW.id, NEW.name, NEW.email, 'UPDATE');
    RETURN NEW;
  ELSIF TG_OP = 'INSERT' THEN
    INSERT INTO users_history (user_id, name, email, operation)
    VALUES (NEW.id, NEW.name, NEW.email, 'INSERT');
    RETURN NEW;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_users
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_users();