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
);
Insert all of the sports:
INSERT INTO sports (name)
SELECT DISTINCT sport FROM fixtures;
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;
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;
Validate the foreign-key constraint:
ALTER TABLE fixtures
VALIDATE CONSTRAINT fixtures_sport_id;
Top comments (0)