Normalizing Flat Data in SQL: A Step-by-Step Guide

When importing data from flat files like CSVs into a relational database, denormalized data is common. A classic example is a table where repeated string values (such as measurement units, categories, or country names) appear alongside numeric data. To follow proper relational database design (Third Normal Form or 3NF), you should extract those repeated strings into a lookup table and link them using a foreign key.

In this guide, we'll walk through how to populate a foreign key column in your master table referencing the normalized lookup table, and how to reconstruct the original dataset using a simple JOIN.

The Scenario

You have imported raw data into a staging table named original, created a lookup table units with unique unit names, and created a numeric table master_values. Now, you need to accomplish two things:

  1. Populate a foreign key column (e.g., units_key) in master_values matching the corresponding unit from the units lookup table.
  2. Query the normalized tables with a JOIN to recreate the original dataset.

Step 1: Adding and Populating the Foreign Key

First, add the new foreign key column to master_values:

ALTER TABLE master_values 
ADD COLUMN units_key INT REFERENCES units(pkey);

To populate this column, you need to update master_values using data from both the raw table (original) and the lookup table (units). In PostgreSQL, you can use an UPDATE ... FROM statement:

UPDATE master_values m
SET units_key = u.pkey
FROM original o
JOIN units u ON o.unit = u.unit
WHERE m.pkey = o.originalpkey;

How it works:

  • FROM original o JOIN units u ON o.unit = u.unit matches each original row to the generated pkey in the lookup table by comparing unit names.
  • WHERE m.pkey = o.originalpkey guarantees that the update targets the exact record in master_values.

Step 2: Reconstructing the Original View Using a JOIN

Once your normalized schema is set up, you can reconstruct the unnormalized view at any time using an INNER JOIN:

SELECT 
    m.value, 
    u.unit
FROM master_values m
JOIN units u ON m.units_key = u.pkey
ORDER BY m.pkey;

This query pairs each numeric measurement with its corresponding text unit via the foreign key link, resulting in the original layout without the storage overhead and data integrity risks of duplicate strings.

A Cleaner, Proactive ETL Pattern

While fixing an existing table with an UPDATE statement works, performing multi-step updates on large datasets can be slow due to write overhead and WAL generation. In production, a cleaner ETL (Extract, Transform, Load) pattern builds the normalized tables in the proper order:

-- 1. Create the lookup table from raw data
CREATE TABLE units (
    pkey INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    unit TEXT UNIQUE NOT NULL
);

INSERT INTO units (unit)
SELECT DISTINCT unit FROM original;

-- 2. Create the master table directly with the populated foreign key
CREATE TABLE master_values (
    pkey INT PRIMARY KEY,
    value REAL NOT NULL,
    units_key INT NOT NULL REFERENCES units(pkey)
);

INSERT INTO master_values (pkey, value, units_key)
SELECT 
    o.originalpkey,
    o.value,
    u.pkey
FROM original o
JOIN units u ON o.unit = u.unit;

Summary

Normalizing flat CSV data in PostgreSQL typically involves creating your canonical lookup table, then joining the lookup table back to your raw data during the insertion or update process. By enforcing foreign key constraints, you eliminate redundancy, maintain consistency, and ensure query performance remains fast and scalable.