First normal form (1NF)
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)First normal form (1NF) is the first rule of normalisation: every cell holds exactly one value. A table that keeps lists in a cell, or the same kind of value in several columns, cannot be searched, counted or checked properly.
Atomic values: one value per cell
First normal form (1NF) means every cell holds one atomic value (a value you never need to split), there are no repeating groups of columns, and every row has a primary key.
Searching with = works only when each cell holds one value. This table keeps a skills list in one cell, and the queries below show how every search on it goes wrong.
Example 1 · A skills list in one cell
CREATE TABLE candidates_v1 ( roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL, skills VARCHAR(100) NOT NULL -- a list in one cell);INSERT INTO candidates_v1 VALUES ('CS01', 'Aditya', 'C, SQL, Excel'), ('CS02', 'Bhavana', 'Python, SQL'), ('CS03', 'Chirag', 'Excel');
-- Who knows C?SELECT name FROM candidates_v1 WHERE skills LIKE '%C%' ORDER BY name;
-- Who knows SQL? FIND_IN_SET searches comma lists...SELECT name, FIND_IN_SET('SQL', skills) AS positionFROM candidates_v1ORDER BY name;+--------+ | name | +--------+ | Aditya | | Chirag | +--------+ +---------+----------+ | name | position | +---------+----------+ | Aditya | 0 | | Bhavana | 0 | | Chirag | 0 | +---------+----------+
What this code does
LIKE '%C%'also returns Chirag, because "Excel" contains a "c" (MySQL's default comparison ignores case). Text search cannot tell one value from part of another.FIND_IN_SETlooks for an exact item in a comma list, but the items are stored with a space after each comma (' SQL'), so it finds nobody. Every fix of this kind adds a new trap; the real fix is one value per cell.
Why does it matter? SQL works on whole values. WHERE skill = 'C' compares one value with one value. When a cell holds 'C, SQL, Excel', SQL sees one long string, and every question about a single skill becomes text searching, which goes wrong in quiet ways.
"Atomic" depends on how the data is used. A full name 'Asha Rao' is fine as one value if the app never sorts or searches by surname. An address in one column is fine for printing a label, but not if the app must list customers by city; then city and pincode need their own columns.
=? If you need LIKE '%X%' or string functions, the cell probably holds more than one value.Repeating groups and comma-separated lists
A repeating group is the same kind of fact stored several times for one row: a list in one cell ('C, SQL, Excel') or numbered columns (allergy1, allergy2, allergy3). Both break 1NF.
A search over numbered columns needs one condition per column. This patients table has allergy1, allergy2 and allergy3; the queries find who is allergic to penicillin.
Example 2 · Repeating columns: allergy1, allergy2, allergy3
CREATE TABLE patients_v1 ( patient_id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, allergy1 VARCHAR(20) NULL, allergy2 VARCHAR(20) NULL, allergy3 VARCHAR(20) NULL);INSERT INTO patients_v1 VALUES (1, 'Lalitha', 'Penicillin', 'Peanuts', NULL), (2, 'Mohan', NULL, NULL, NULL), (3, 'Nasreen', 'Dust', 'Penicillin', 'Sulfa');
-- Who is allergic to penicillin? Every column must be checkedSELECT nameFROM patients_v1WHERE allergy1 = 'Penicillin' OR allergy2 = 'Penicillin' OR allergy3 = 'Penicillin'ORDER BY name;
-- How many allergies does each patient have?SELECT name, (allergy1 IS NOT NULL) + (allergy2 IS NOT NULL) + (allergy3 IS NOT NULL) AS allergiesFROM patients_v1ORDER BY patient_id;+---------+ | name | +---------+ | Lalitha | | Nasreen | +---------+ +---------+-----------+ | name | allergies | +---------+-----------+ | Lalitha | 2 | | Mohan | 0 | | Nasreen | 3 | +---------+-----------+
What this code does
- The penicillin search needs three conditions, and any query written before a fourth column is added will silently miss the new column.
- Counting needs a trick: in MySQL a true comparison is 1 and false is 0, so the three tests are added up. Mohan's row wastes three
NULLcells, and a patient with four allergies cannot be stored at all.
Numbered columns look tidier, but they have the same problems. A search must check every column with OR. A fourth value needs ALTER TABLE. Most rows waste the unused columns as NULL. And nothing stops the same value appearing in two of the columns.
Find the bug
Spot the 1NF breaker
Click the line that breaks first normal form.
Which line stores several values in one cell?
Round 1 of 2SET and JSON column types that hold several values. They are useful for data you only store and display. For values you search, join, count or protect with a foreign key, a child table is the reliable design.Fixing a table that breaks 1NF
A child table holds the many values of one parent row, one row per value, with a foreign key back to the parent. Moving a repeating group into a child table is the standard fix for a 1NF problem.
To fix a list, give every value its own row in a child table. The picture shows the move; the diagram and script below build the 1NF tables for skills and fill them from the old comma list.
Example 3 · ER diagram: candidates and skills in 1NF
+--------------+ +------------------+ +--------------+| candidates | | candidate_skills | | skills ||--------------| |------------------| |--------------|| PK roll_no | ||------o{ | PK,FK roll_no | o{------|| | PK skill_id || name | | PK,FK skill_id | | name (UQ) || branch | +------------------+ +--------------++--------------+One candidate has zero or many skills; one skill is held by zero or many candidates (M:N). Each row of candidate_skills is one candidate-skill pair: one value per cell. skills.name is UNIQUE, so "SQL" is stored once and spelled the same everywhere.
What this code does
- Skills are shared by many candidates, so they get their own table and a join table, as in the many-to-many lesson.
- The composite key
(roll_no, skill_id)stops a candidate listing the same skill twice, which the comma list could not prevent.
Example 4 · The 1NF tables, filled from the old list
CREATE TABLE candidates_v1 ( roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL, skills VARCHAR(100) NOT NULL -- a list in one cell);INSERT INTO candidates_v1 VALUES ('CS01', 'Aditya', 'C, SQL, Excel'), ('CS02', 'Bhavana', 'Python, SQL'), ('CS03', 'Chirag', 'Excel');
CREATE TABLE candidates ( roll_no VARCHAR(10) PRIMARY KEY, name VARCHAR(30) NOT NULL);CREATE TABLE skills ( skill_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL UNIQUE);CREATE TABLE candidate_skills ( roll_no VARCHAR(10) NOT NULL, skill_id INT NOT NULL, PRIMARY KEY (roll_no, skill_id), FOREIGN KEY (roll_no) REFERENCES candidates (roll_no), FOREIGN KEY (skill_id) REFERENCES skills (skill_id));
-- One-time move: JSON_TABLE (MySQL 8.0.4+) turns each list into rowsCREATE TABLE skill_rows ASSELECT c.roll_no, j.skillFROM candidates_v1 c, JSON_TABLE(CONCAT('["', REPLACE(c.skills, ', ', '","'), '"]'), '$[*]' COLUMNS (skill VARCHAR(20) PATH '$')) AS j;
INSERT INTO candidates SELECT roll_no, name FROM candidates_v1;INSERT INTO skills (name) SELECT DISTINCT skill FROM skill_rows ORDER BY skill;INSERT INTO candidate_skillsSELECT r.roll_no, s.skill_idFROM skill_rows rJOIN skills s ON s.name = r.skill;
-- Who knows C? An exact match nowSELECT c.nameFROM candidates cJOIN candidate_skills cs ON cs.roll_no = c.roll_noJOIN skills s ON s.skill_id = cs.skill_idWHERE s.name = 'C';
-- Skills per candidate and candidates per skillSELECT c.name, COUNT(*) AS skillsFROM candidates cJOIN candidate_skills cs ON cs.roll_no = c.roll_noGROUP BY c.roll_no, c.nameORDER BY c.name;
SELECT s.name AS skill, COUNT(*) AS candidatesFROM skills sJOIN candidate_skills cs ON cs.skill_id = s.skill_idGROUP BY s.skill_id, s.nameORDER BY candidates DESC, skill;+--------+ | name | +--------+ | Aditya | +--------+ +---------+--------+ | name | skills | +---------+--------+ | Aditya | 3 | | Bhavana | 2 | | Chirag | 1 | +---------+--------+ +--------+------------+ | skill | candidates | +--------+------------+ | Excel | 2 | | SQL | 2 | | C | 1 | | Python | 1 | +--------+------------+
What this code does
- The
CONCATandREPLACEturnC, SQL, Excelinto the JSON array["C","SQL","Excel"], andJSON_TABLEgives one row per item. This runs once, to move old data; new data goes straight into the 1NF tables. - Now "who knows C?" is a plain
=and returns only Aditya, with no Excel mistake. Counting in either direction is an ordinaryGROUP BY.
The child's primary key is usually the parent key plus the value, such as (roll_no, skill_id), so a value cannot repeat for one parent.
If the values are only labels that belong to one parent (a patient's allergies, a contact's phone numbers), the child table can store the value itself. If the values are things in their own right that many parents share (skills, tags), give them their own table and link the two with a join table: a many-to-many relationship. Existing data must be moved too. MySQL 8.0.4 and later has JSON_TABLE, which turns a list into rows; Example 4 uses it once to fill the new tables.
A worked example
Now the whole routine on one sheet. A railway booking sheet stores all passengers of a PNR in one cell, as 'Ramesh/45/M, Suma/42/F'. The steps below, then the script, bring it to 1NF.
- Find the repeating group: the passengers of one booking.
- Find values hidden inside values: name, age and gender inside each passenger.
- Keep the single-valued facts (PNR, train, date) in the parent table,
bookings. - Move each passenger to a child row in
passengers, with one column for each part. - Choose the child's key: the PNR plus a passenger number,
(pnr, seq_no). - Check: every cell holds one value, and a plain
=orGROUP BYanswers each question.
Example 5 · ER diagram: a booking and its passengers
+------------------+ +----------------+| bookings | | passengers ||------------------| |----------------|| PK pnr | ||------|{ | PK,FK pnr || train_no | | PK seq_no || journey_date | | name || booked_by | | age |+------------------+ | gender | +----------------+One booking has one or many passengers; each passenger row belongs to exactly one booking. A passenger is identified by the PNR plus their number on the ticket (1, 2, 3 ...). Name, age and gender each get their own column: no "Ramesh/45/M" strings.
What this code does
- Passengers belong only to their booking, so a simple child table is enough; no join table is needed.
seq_nois the passenger number printed on the ticket, which makes(pnr, seq_no)a natural composite key. This is a weak entity: a passenger row has no identity without its PNR.
Example 6 · Worked example: a booking sheet brought to 1NF
-- Before: a list of passengers, each "name/age/gender", in one cellCREATE TABLE bookings_v1 ( pnr CHAR(10) PRIMARY KEY, train_no CHAR(5) NOT NULL, journey_date DATE NOT NULL, passengers VARCHAR(200) NOT NULL);INSERT INTO bookings_v1 VALUES ('4521378901', '12627', '2026-11-14', 'Ramesh/45/M, Suma/42/F'), ('4521378902', '12951', '2026-11-15', 'Imran/67/M'), ('4521378903', '12627', '2026-11-14', 'Geeta/63/F, Arun/19/M, Kiran/17/M');
-- After: 1NFCREATE TABLE bookings ( pnr CHAR(10) PRIMARY KEY, train_no CHAR(5) NOT NULL, journey_date DATE NOT NULL);CREATE TABLE passengers ( pnr CHAR(10) NOT NULL, seq_no TINYINT NOT NULL, name VARCHAR(30) NOT NULL, age TINYINT NOT NULL, gender CHAR(1) NOT NULL, PRIMARY KEY (pnr, seq_no), FOREIGN KEY (pnr) REFERENCES bookings (pnr));INSERT INTO bookings SELECT pnr, train_no, journey_date FROM bookings_v1;INSERT INTO passengers VALUES ('4521378901', 1, 'Ramesh', 45, 'M'), ('4521378901', 2, 'Suma', 42, 'F'), ('4521378902', 1, 'Imran', 67, 'M'), ('4521378903', 1, 'Geeta', 63, 'F'), ('4521378903', 2, 'Arun', 19, 'M'), ('4521378903', 3, 'Kiran', 17, 'M');
-- Questions the old sheet could not answer with plain SQLSELECT b.pnr, COUNT(*) AS passengers, SUM(p.age >= 60) AS seniorsFROM bookings bJOIN passengers p ON p.pnr = b.pnrGROUP BY b.pnrORDER BY b.pnr;
SELECT p.name, p.age, b.train_noFROM passengers pJOIN bookings b ON b.pnr = p.pnrWHERE p.gender = 'F'ORDER BY p.age DESC;+------------+------------+---------+ | pnr | passengers | seniors | +------------+------------+---------+ | 4521378901 | 2 | 0 | | 4521378902 | 1 | 1 | | 4521378903 | 3 | 1 | +------------+------------+---------+ +-------+-----+----------+ | name | age | train_no | +-------+-----+----------+ | Geeta | 63 | 12627 | | Suma | 42 | 12627 | +-------+-----+----------+
What this code does
- The parent keeps one value per cell (PNR, train, date). Each passenger became one row, with name, age and gender split into typed columns, so
ageis a real number that can be compared. SUM(p.age >= 60)counts seniors (a true test counts as 1), andWHERE p.gender = 'F'filters women passengers. On the old sheet both needed string cutting on every row.
Next step
Check your understanding
11 quick questions