Denormalización Intencional

-- Fuente de verdad normalizada
CREATE TABLE orders (
  order_id    bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (customer_id),
  status      text NOT NULL
);
 
CREATE TABLE customers (
  customer_id  bigint PRIMARY KEY,
  email        text NOT NULL
);
 
-- Copia de lectura desnormalizada en orders (documentar el invariante)
ALTER TABLE orders ADD COLUMN customer_email text;
 
CREATE OR REPLACE FUNCTION sync_order_customer_email()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  SELECT email INTO NEW.customer_email FROM customers WHERE customer_id = NEW.customer_id;
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER orders_customer_email_trg
  BEFORE INSERT OR UPDATE OF customer_id ON orders
  FOR EACH ROW EXECUTE FUNCTION sync_order_customer_email();

BEGIN;
 
CREATE TABLE products (
  product_id  bigint PRIMARY KEY,
  sku         text NOT NULL,
  name        text NOT NULL,
  price       numeric(12, 2) NOT NULL
);
 
CREATE TABLE orders (
  order_id  bigint PRIMARY KEY,
  placed_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE order_items (
  order_id      bigint NOT NULL REFERENCES orders (order_id),
  product_id    bigint NOT NULL REFERENCES products (product_id),
  quantity      integer NOT NULL CHECK (quantity > 0),
  -- Denorm de instantánea: el precio del catálogo puede cambiar más tarde
  unit_price    numeric(12, 2) NOT NULL,
  product_name  text NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
 
-- Rellenar columnas de denorm en la inserción mediante trigger
CREATE OR REPLACE FUNCTION order_items_snapshot_product()
RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  p products%ROWTYPE;
BEGIN
  SELECT * INTO p FROM products WHERE product_id = NEW.product_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'product % not found', NEW.product_id;
  END IF;
  NEW.unit_price := COALESCE(NEW.unit_price, p.price);
  NEW.product_name := COALESCE(NEW.product_name, p.name);
  RETURN NEW;
END;
$$;
 
CREATE TRIGGER order_items_snapshot_trg
  BEFORE INSERT ON order_items
  FOR EACH ROW EXECUTE FUNCTION order_items_snapshot_product();
 
INSERT INTO products (product_id, sku, name, price)
VALUES (1, 'A1', 'Alpha', 19.99);
 
INSERT INTO orders (order_id) VALUES (100);
INSERT INTO order_items (order_id, product_id, quantity, unit_price, product_name)
VALUES (100, 1, 2, 19.99, 'Alpha');
 
-- El cambio de nombre del catálogo no reescribe los elementos de línea históricos
UPDATE products SET name = 'Alpha Pro' WHERE product_id = 1;
 
SELECT product_name, unit_price FROM order_items WHERE order_id = 100;
 
COMMIT;

-- Detección de deriva: el email desnormalizado debe coincidir con el cliente, a menos que las reglas de instantánea digan lo contrario
SELECT o.order_id, o.customer_email, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.customer_email IS DISTINCT FROM c.email;