Relational Schema Design and Normalization Questions

INSERT INTO hist_customer_status (customer_id, status, effective_from, effective_to, is_current)
VALUES (:cust_id, :new_status, CURRENT_TIMESTAMP, '9999-12-31', TRUE);

-- mark previous as not current
UPDATE hist_customer_status
SET is_current = FALSE, effective_to = CURRENT_TIMESTAMP
WHERE customer_id = :cust_id AND hist_id <> (SELECT MAX(hist_id) FROM hist_customer_status WHERE customer_id = :cust_id AND is_current = TRUE);

MERGE INTO dim_customer d
USING (
  SELECT h.customer_id, h.status AS current_status, max(h.effective_from) AS last_updated
  FROM hist_customer_status h
  WHERE h.is_current = TRUE
  GROUP BY h.customer_id, h.status
) src
ON (d.customer_id = src.customer_id)
WHEN MATCHED AND d.current_status <> src.current_status THEN
  UPDATE SET current_status = src.current_status, last_updated = src.last_updated
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, current_status, last_updated)
  VALUES (src.customer_id, NULL, src.current_status, src.last_updated);

SELECT f.*, h.status
FROM fact_orders f
JOIN hist_customer_status h
  ON f.customer_id = h.customer_id
  AND f.order_date BETWEEN h.effective_from AND h.effective_to;

SELECT c.customer_id, c.customer_name, s.lifetime_value, a.city, p.phone_number, o.order_date, o.total_amount
FROM customer_snapshot s
JOIN customers c ON s.customer_id = c.customer_id
LEFT JOIN customer_address a ON s.primary_address_id = a.address_id
LEFT JOIN customer_phone p ON s.primary_phone_id = p.phone_id
LEFT JOIN orders o ON s.last_order_id = o.order_id;

SELECT t.name, COUNT(at.article_id) AS article_count
FROM tags t
JOIN article_tags at ON t.id = at.tag_id
GROUP BY t.name
ORDER BY article_count DESC;

SELECT a.*
FROM articles a
JOIN article_tags at ON a.id = at.article_id
JOIN tags t ON t.id = at.tag_id
WHERE t.name = 'sports';

CREATE MATERIALIZED VIEW tag_counts AS
SELECT tag_id, COUNT(*) AS cnt FROM article_tags GROUP BY tag_id;