How to normalize data SQLServer in PostgreSQL

How do I normalize a denormalized Orders table into separate lookup tables?

“Normalizing data” restructures a denormalized table into smaller, related tables to remove redundancy and enforce data integrity.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why normalize an ecommerce database?

Normalization eliminates duplicated customer, product, and order details, shrinking storage, speeding updates, and preventing inconsistent data.

What steps achieve data normalization?

Typical workflow: 1) create lookup tables for repeating attributes, 2) populate them with distinct values, 3) replace duplicated columns with foreign-key columns, 4) add constraints to maintain referential integrity.

How do I identify repeated data?

Run COUNT(*) with GROUP BY on candidate columns. If counts >1, the column is a normalization candidate.

Which SQL commands perform the split?

CREATE TABLE defines lookup tables, INSERT INTO … SELECT DISTINCT fills them, UPDATE rewires the original table to reference the new keys, and ALTER TABLE adds foreign keys.

Example – breaking out Customers from Orders

In a denormalized Orders table holding customer_name and customer_email, we first create Customers, load unique rows, then link Orders.customer_id to Customers.id.

Step 1 – lookup table

CREATE TABLE Customers (id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE, created_at TIMESTAMP DEFAULT NOW());

Step 2 – populate distinct rows

INSERT INTO Customers (name, email)SELECT DISTINCT customer_name, customer_email FROM Orders;

Step 3 – add foreign-key column

ALTER TABLE Orders ADD COLUMN customer_id INT;

Step 4 – backfill foreign keys

UPDATE Orders oSET customer_id = c.idFROM Customers cWHERE o.customer_name = c.name AND o.customer_email = c.email;

Step 5 – enforce referential integrity

ALTER TABLE Orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES Customers(id);ALTER TABLE Orders DROP COLUMN customer_name, DROP COLUMN customer_email;

Best practices for smooth normalization

Work in a transaction, add indexes on foreign keys, validate counts before and after the move, and update application code in tandem.

What are common pitfalls?

Forgetting to backfill foreign keys leaves NULLs; skipping UNIQUE constraints can re-introduce duplicates.

Why How to normalize data SQLServer in PostgreSQL is important

How to normalize data SQLServer in PostgreSQL Example Usage


-- Normalize Products duplicated inside OrderItems
CREATE TABLE Products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    price NUMERIC(10,2) NOT NULL,
    stock INT DEFAULT 0
);

INSERT INTO Products (name, price)
SELECT DISTINCT product_name, product_price
FROM OrderItems;

ALTER TABLE OrderItems ADD COLUMN product_id INT;

UPDATE OrderItems oi
SET    product_id = p.id
FROM   Products p
WHERE  oi.product_name  = p.name
  AND  oi.product_price = p.price;

ALTER TABLE OrderItems
  ADD CONSTRAINT fk_orderitems_products
  FOREIGN KEY (product_id) REFERENCES Products(id);

ALTER TABLE OrderItems
  DROP COLUMN product_name,
  DROP COLUMN product_price;

How to normalize data SQLServer in PostgreSQL Syntax


-- Generic pattern for normalizing repeated data
-- 1. Create a lookup table
CREATE TABLE Customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. Populate with unique rows from the source
INSERT INTO Customers (name, email)
SELECT DISTINCT customer_name, customer_email
FROM Orders;

-- 3. Add a foreign-key column to the source
ALTER TABLE Orders ADD COLUMN customer_id INT;

-- 4. Backfill the foreign-key column
UPDATE Orders o
SET    customer_id = c.id
FROM   Customers c
WHERE  o.customer_name = c.name
  AND  o.customer_email = c.email;

-- 5. Enforce referential integrity and drop redundant columns
ALTER TABLE Orders
  ADD CONSTRAINT fk_orders_customers
  FOREIGN KEY (customer_id) REFERENCES Customers(id);
ALTER TABLE Orders
  DROP COLUMN customer_name,
  DROP COLUMN customer_email;

Common Mistakes

Updating source rows before inserting into the lookup table. Always insert DISTINCT values first, then update, or the UPDATE cannot resolve the new keys.
Dropping old columns before enforcing foreign keys. Keep original columns until the backfill succeeds and counts match to avoid data loss.