-- Denormalization's update anomaly, DDIA 2e Ch. 3.
-- The resume/organization case: store the org name inline on every profile
-- (denormalized) vs reference the org by id (normalized). Rename an org and
-- watch (1) the blast radius, and (2) an interrupted denormalized update leave
-- one company with two names -- an inconsistency the normalized schema can't reach.
--
--   docker cp denormalization_anomaly.sql ddia-ch3:/tmp/
--   docker exec ddia-ch3 psql -U postgres -d study -f /tmp/denormalization_anomaly.sql

CREATE TABLE organizations (id int PRIMARY KEY, name text, logo_url text);
INSERT INTO organizations VALUES
  (1,'Facebook','/logos/fb.png'), (2,'Google','/logos/g.png'), (3,'Amazon','/logos/a.png');

-- Normalized: profiles reference the organization by id.
CREATE TABLE profiles_norm (id bigint PRIMARY KEY, person_name text, org_id int REFERENCES organizations(id));
INSERT INTO profiles_norm SELECT g, 'person '||g, (g%3)+1 FROM generate_series(1,2000000) g;

-- Denormalized: the org name (and logo) copied onto every profile.
CREATE TABLE profiles_denorm (id bigint PRIMARY KEY, person_name text, org_name text, org_logo_url text);
INSERT INTO profiles_denorm
SELECT g, 'person '||g, o.name, o.logo_url
FROM generate_series(1,2000000) g JOIN organizations o ON o.id=(g%3)+1;

\echo '===== Facebook renames to Meta. Blast radius of the rename: ====='
\echo '--- normalized: update the one organization row ---'
UPDATE organizations SET name='Meta' WHERE name='Facebook';
\echo '--- denormalized: update every profile that copied the name ---'
UPDATE profiles_denorm SET org_name='Meta' WHERE org_name='Facebook';

\echo '===== Now the anomaly: an interrupted denormalized update (only half commits) ====='
UPDATE profiles_denorm SET org_name='Facebook' WHERE org_name='Meta';           -- reset
UPDATE profiles_denorm SET org_name='Meta' WHERE org_name='Facebook' AND id % 2 = 0;  -- partial
\echo '--- denormalized: how many distinct names does this ONE company now have? ---'
SELECT org_name, count(*) FROM profiles_denorm
WHERE org_name IN ('Facebook','Meta') GROUP BY org_name ORDER BY org_name;

\echo '===== The normalized schema cannot reach that state ====='
\echo '--- normalized: the name lives in one row; every profile reads it via join ---'
SELECT o.name AS company_name, count(*) AS profiles
FROM profiles_norm p JOIN organizations o ON o.id = p.org_id
WHERE o.id = 1 GROUP BY o.name;
