DEV Community

Cover image for UPDATE … FROM for database normalisation
Johnß
Johnß

Posted on

UPDATE … FROM for database normalisation

Normalisation?

Normalisation is the process of reducing the “copies” of data in a database, for example, in a fixtures table having a foreign-key to a sport table rather than repeating the value Football in each of the fixtures.

fixtures

id sport home_team away_team
1 Football Liverpool Everton
2 Football Newcastle Chelsea
3 Football Sunderland Arsenal

Might become:

sports

id sport
1 Football

fixtures

id sport_id home_team away_team
1 1 Liverpool Everton
2 1 Newcastle Chelsea
3 1 Sunderland Arsenal

home_team and away_team should probably also be foreign-keys to a teams table.

Migration queries

Create the new sports table:

CREATE TABLE sports (
  id SERIAL PRIMARY KEY NOT NULL,
  Name VARCHAR(100) NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

Insert all of the sports:

INSERT INTO sports (name)
  SELECT DISTINCT sport FROM fixtures;
Enter fullscreen mode Exit fullscreen mode

Add the sport_id foreign-key but don’t validate as there won’t be any values yet:

ALTER TABLE fixtures 
  ADD COLUMN sport_id INT;
ALTER TABLE fixtures
  ADD CONSTRAINT fixtures_sport_id
    FOREIGN KEY (sport_id)
    REFERENCES sports (id) NOT VALID;
Enter fullscreen mode Exit fullscreen mode

Update all of the fixtures with the correct value for sport_id:

UPDATE fixtures SET sport_id = sports.id
FROM sports
WHERE fixtures.sport = sports.name;
Enter fullscreen mode Exit fullscreen mode

Validate the foreign-key constraint:

ALTER TABLE fixtures
  VALIDATE CONSTRAINT fixtures_sport_id;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)