First Normal Form 1NF Explained with Examples

First Normal Form is the entry-level requirement every relational table must satisfy: every column must hold a single, atomic (indivisible) value — no lists, no repeating groups, no comma-separated values crammed into one field. It sounds like a simple rule, but violating it is one of the most common real-world database design mistakes, especially with multi-valued data like phone numbers or tags.

Key Definitions

• First Normal Form (1NF): A table satisfies 1NF if every column contains only atomic (indivisible) values, and there are no repeating groups of columns.
• Atomic value: A single, indivisible unit of data, as opposed to a list or combination of multiple values stored together.
• Repeating group: A set of columns that repeats within a single row to store multiple related values, such as phone1, phone2, phone3.

What You'll Learn

• Define First Normal Form and its atomicity requirement.
• Identify 1NF violations, including repeating groups and multi-valued columns.
• Convert a table violating 1NF into a properly structured 1NF table.
• Understand why atomic values are essential for reliable SQL querying.

Detailed Explanation

A table violates 1NF in two common ways. The first is storing multiple values in a single column, such as a doctors table with a phone_numbers column containing '9876543210, 9123456780' as one comma-separated string. This might look convenient, but it makes filtering, indexing, and validating individual phone numbers with SQL extremely awkward — you can't easily write WHERE phone_number = '9123456780' against a comma-separated blob.

The second violation is a repeating group: instead of comma-separating values, the table design uses multiple similarly-named columns, like phone1, phone2, phone3, to accommodate multiple values. This is equally problematic, since it hardcodes an arbitrary limit (what happens when a doctor has four phone numbers?) and makes querying across all of them require checking every column separately.

The fix, in both cases, is the same: move the multi-valued data into its own separate table, with a foreign key linking each individual value back to the original entity — exactly the pattern introduced in Lesson 8.4 for handling multi-valued attributes. This restructuring achieves 1NF by ensuring the doctors table itself holds only atomic values, while a new doctor_phones table holds one phone number per row, with as many rows per doctor as needed.

Visual Summary

Two side-by-side tables. Left, a violating table: [doctors: doctor_id, doctor_name, phone_numbers='9876543210, 9123456780'] with a red X. Right, the 1NF-compliant fix: [doctors: doctor_id, doctor_name] connected via an arrow to [doctor_phones: phone_id, doctor_id, phone_number] showing two separate rows for the same doctor, each with a green checkmark.

Quick Reference

Violation Type	Example	1NF Fix
Multi-valued column	phone_numbers = '9876543210, 9123456780'	Move to a separate doctor_phones table, one row per number
Repeating group	phone1, phone2, phone3 columns	Same fix: a separate related table with one row per value

SQL Example

-- VIOLATES 1NF: multiple phone numbers crammed into one column
-- doctor_id | doctor_name | phone_numbers
-- 101       | Dr. Verma   | '9876543210, 9123456780'

-- 1NF-COMPLIANT fix: split into two properly structured tables
CREATE TABLE doctors (
  doctor_id   INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name VARCHAR(100) NOT NULL
);

CREATE TABLE doctor_phones (
  phone_id     INT PRIMARY KEY AUTO_INCREMENT,
  doctor_id    INT NOT NULL,
  phone_number VARCHAR(20) NOT NULL,
  FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);

INSERT INTO doctor_phones (doctor_id, phone_number) VALUES
  (101, '9876543210'),
  (101, '9123456780');

In the violating version, Dr. Verma's two phone numbers are trapped inside a single text string, making it impossible to write a clean WHERE phone_number = '9123456780' query or add a UNIQUE constraint on individual numbers. The fixed version stores each phone number as its own atomic row in doctor_phones, linked back to Dr. Verma via doctor_id — now both numbers are independently searchable, indexable, and constrainable, satisfying 1NF's atomicity requirement.

Real-World Examples

• CRM systems store multiple customer email addresses in a dedicated related table rather than a single delimited column, enabling reliable search and validation.
• E-commerce platforms store multiple product tags in a separate product_tags table rather than a comma-separated tags column, allowing efficient tag-based filtering.
• HR systems store multiple employee certifications in a related table rather than cramming them into one column, since certification counts vary unpredictably per employee.

Common Mistakes to Avoid

• Storing multiple related values as a comma-separated string in a single column instead of a separate table.
• Using repeating group columns (phone1, phone2, phone3) instead of a properly related table, hardcoding an arbitrary value limit.
• Assuming 1NF is only about having a primary key, when its core requirement is actually about column-level atomicity.

Interview Questions

Q1. What does First Normal Form require?

First Normal Form requires that every column in a table hold only atomic, indivisible values, with no repeating groups of columns used to store multiple related values.

Q2. How would you fix a table that stores multiple phone numbers in a single comma-separated column?

Move the phone numbers into a separate related table, such as doctor_phones, with a foreign key linking each individual phone number row back to the original doctor, ensuring each value is stored atomically.

Q3. Why is storing multiple values in one column considered a 1NF violation?

Because it prevents SQL from reliably filtering, indexing, validating, or updating individual values within that combined string, undermining the core relational principle that each column should hold a single, well-defined value.

Practice MCQs

1. What is the core requirement of First Normal Form?

1. Every table must have a foreign key
2. Every column must hold only atomic values
3. Every table must have at least three columns
4. Every row must be unique

Answer: B. Every column must hold only atomic values

Explanation: 1NF's defining rule is that every column must contain a single, indivisible value, with no repeating groups or multi-valued columns.

2. Which of the following violates 1NF?

1. A doctors table with a single doctor_name column
2. A doctors table with a phone_numbers column storing '123, 456'
3. A doctor_phones table with one phone number per row
4. A doctors table with a salary column

Answer: B. A doctors table with a phone_numbers column storing '123, 456'

Explanation: Storing multiple comma-separated values in a single column violates the atomicity requirement of 1NF.

Quick Revision Points

• 1NF requires atomic column values and no repeating groups.
• Common violations: comma-separated multi-valued columns and repeating group columns like phone1, phone2, phone3.
• The fix for both violation types is the same: move multi-valued data into a separate related table.

Conclusion

• 1NF requires every column to hold a single, atomic value.
• Multi-valued columns and repeating groups both violate 1NF.
• The standard fix is moving multi-valued data into its own related table with a foreign key.

First Normal Form establishes the baseline requirement for any relational table: every column must hold a single, atomic value, with no multi-valued columns or repeating groups of similarly-named columns. Violations, such as comma-separated phone numbers or numbered phone1/phone2/phone3 columns, are fixed by moving the multi-valued data into its own dedicated related table, linked back via a foreign key — the same pattern for handling multi-valued attributes introduced earlier when converting ER diagrams into relational tables.