MENU


First Normal Form

First Normal Form (1NF) is the baseline every relational table must satisfy before any other normal form is even meaningful. A table is in 1NF when:

Every column holds a single, atomic (indivisible) value — not a list, not a comma-separated string, not a nested structure.
There are no repeating groups — no set of columns that repeats itself (col1_1, col1_2, col1_3, ...) to hold a variable number of values for one row.
Each row is uniquely identifiable, typically by a primary key.

Symptom: Non-Atomic Values

Storing multiple values crammed into one column — a comma-separated list of phone numbers, a JSON array of tags in a VARCHAR — violates 1NF even though MySQL will happily store and even index the string. The problem shows up the moment the application needs to search, join, count, or update one of the individual values: there is no clean way to say "find every customer with this one phone number" without string parsing.

Symptom: Repeating Groups

A table with columns like phone_1, phone_2, phone_3 is the classic repeating-group violation. It caps the number of phone numbers a row can have at however many columns were defined, wastes space for rows that use fewer, and forces every query that needs "any phone number" to check every column with OR.

The Fix

Move the multi-valued attribute into its own table, with a foreign key back to the original entity. This also resolves the repeating-group problem, since the new table can hold any number of rows per parent without any schema change.

Worked Example

Before — a customers table storing phone numbers as a comma-separated list, and a fixed number of repeating email columns:

customer_idnamephonesemail_1email_2
1Alice Tan555-0101,555-0199alice@example.comNULL
2Ben Ong555-0150ben@work.comben@home.com

Finding every customer with phone number 555-0199 requires a LIKE '%555-0199%' scan, which is slow, cannot use a normal index, and can false-match a substring of a different number. Adding a third email requires an ALTER TABLE.

After — the repeating/multi-valued data moves into child tables, each row atomic:

See the worked CREATE TABLE statements below. With this shape, finding every customer with a given phone number is an indexed equality lookup, and a customer can have any number of phone numbers or email addresses without a schema change.

1NF says nothing yet about redundancy between non-key columns — that is the concern of Second Normal Form and Third Normal Form, covered next. See also JSON for cases where a genuinely document-shaped value is intentionally kept as one column rather than split out. 

-- BEFORE: violates 1NF (comma-separated list + repeating email columns)
CREATE TABLE customers_unnormalized (
    customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    phones      VARCHAR(255) NOT NULL,   -- e.g. '555-0101,555-0199'
    email_1     VARCHAR(255) NULL,
    email_2     VARCHAR(255) NULL
) ENGINE=InnoDB;

-- Finding a customer by one phone number needs a fragile substring scan:
-- SELECT * FROM customers_unnormalized WHERE phones LIKE '%555-0199%';

-- AFTER: 1NF-compliant, atomic values, no repeating groups
CREATE TABLE customers (
    customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE customer_phones (
    phone_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    phone       VARCHAR(20)  NOT NULL,
    CONSTRAINT fk_customer_phones_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
        ON DELETE CASCADE,
    UNIQUE KEY uq_customer_phone (customer_id, phone)
) ENGINE=InnoDB;

CREATE TABLE customer_emails (
    email_id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    email       VARCHAR(255) NOT NULL,
    CONSTRAINT fk_customer_emails_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
        ON DELETE CASCADE,
    UNIQUE KEY uq_customer_email (customer_id, email)
) ENGINE=InnoDB;

-- Now an indexed, exact lookup:
SELECT c.name
FROM customers c
JOIN customer_phones p ON p.customer_id = c.customer_id
WHERE p.phone = '555-0199';

-- Adding a third, fourth, fifth email needs no ALTER TABLE:
INSERT INTO customer_emails (customer_id, email) VALUES (2, 'ben@thirdaddress.com');