SQL / DATA MODELLING AND NORMALISATION
First normal form and atomic values
Recognise columns that hide a list or a repeating group, judge whether a value is atomic for the queries you need, and split it into a keyed child table.
What you will learn
- Spot repeating groups: comma lists, phone1/phone2/phone3, JSON arrays you filter on
- Split a repeating group into a child table keyed by (parent_id, element)
- Decide atomicity from the queries you run, not from how the value looks
- Replace LIKE '%x%' scans over packed values with an indexable equality join
Understanding First normal form and atomic values
First normal form asks two things of a table: every intersection of a row and a column holds exactly one value from that column's domain, and every row is distinguishable from every other. The reason this matters is mechanical rather than aesthetic. Equality, comparison, JOIN, GROUP BY, FOREIGN KEY, CHECK and every index operate on whole column values, so anything you pack inside a value is invisible to them. Once it is invisible you are reduced to string surgery, and a predicate like skills LIKE '%SQL%' cannot use an index and cannot tell 'SQL' from 'NoSQL'.
Atomic is not an absolute property of a value; it is relative to what your queries have to address. 'Ada Lovelace' in one column is perfectly atomic if the application only ever prints it, and stops being atomic the day you must sort by surname, because the column now has structure the database cannot see. The same test applies to '£4.50' if you need to sum amounts, or to '2026-09-03T20:10' stored as text if you need the year. So the question is never "could this be chopped up" — anything can — but "does any query, constraint or join need to reach a part of it".
Violations come in two shapes. Several values crammed into one column ('SQL,Python') and a family of numbered columns (phone1, phone2, phone3) both encode "several of these per row", and both are fixed the same way: give the repeating element its own table, one row per value, keyed by the parent key plus the value, with NOT NULL on the value so absence means zero rows rather than a placeholder. Array and JSON columns are the modern version of the comma list; they are reasonable when the payload is opaque and always read whole, but the moment a WHERE clause reaches inside you have the old problem with nicer syntax, and no foreign key can ever point at an element.
-- sqlite3
CREATE TABLE consultant_raw (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
skills TEXT NOT NULL -- a repeating group in disguise
);
INSERT INTO consultant_raw VALUES
(1, 'Ada', 'SQL,Python'),
(2, 'Bo', 'NoSQL,Go'),
(3, 'Cai', 'Python');
-- Bo has never written SQL, but the substring says otherwise
SELECT 'like' AS how, name FROM consultant_raw
WHERE skills LIKE '%SQL%'
ORDER BY name;
CREATE TABLE consultant (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE consultant_skill (
consultant_id INTEGER NOT NULL REFERENCES consultant(id),
skill TEXT NOT NULL,
PRIMARY KEY (consultant_id, skill)
);
INSERT INTO consultant VALUES (1,'Ada'), (2,'Bo'), (3,'Cai');
INSERT INTO consultant_skill VALUES
(1,'SQL'), (1,'Python'), (2,'NoSQL'), (2,'Go'), (3,'Python');
SELECT 'join' AS how, c.name FROM consultant c
JOIN consultant_skill s ON s.consultant_id = c.id
WHERE s.skill = 'SQL'
ORDER BY c.name;First normal form is the rule that every cell holds exactly one value from its column's domain, because SQL's operators, constraints and indexes can only see whole column values.
Worked examples
Aggregation becomes possible
One row per skill turns a question about skills into a plain GROUP BY, which a packed string cannot support.
-- sqlite3
CREATE TABLE staff_skill (
staff_id INTEGER NOT NULL,
skill TEXT NOT NULL,
PRIMARY KEY (staff_id, skill)
);
INSERT INTO staff_skill VALUES
(1,'SQL'), (1,'Python'), (2,'NoSQL'), (2,'Go'), (3,'Python'), (3,'SQL');
SELECT skill, COUNT(*) AS people
FROM staff_skill
GROUP BY skill
ORDER BY people DESC, skill;Example explained
Line 1PRIMARY KEY (staff_id, skill) makes the pair the identity of a row, so the same skill cannot be recorded twice for one person.
Line 2GROUP BY skill works because 'SQL' is the entire value of the column, not a fragment inside one.
Line 3COUNT(*) is trustworthy here: each row is exactly one skill fact, so there is nothing to parse or de-duplicate.
Line 4With a single skills text column the same answer needs the string split first, and the split has to guess the delimiter and trim stray spaces.
Numbered columns are also a repeating group
phone1, phone2, phone3 violates 1NF just as a comma list does, and shows up as OR chains and schema changes.
-- sqlite3
CREATE TABLE customer_wide (
id INTEGER PRIMARY KEY,
phone1 TEXT,
phone2 TEXT,
phone3 TEXT
);
INSERT INTO customer_wide VALUES
(1, '555-0100', '555-0101', NULL),
(2, '555-0102', NULL, NULL);
-- every column has to be named by hand, in every query
SELECT 'wide' AS src, id FROM customer_wide
WHERE phone1 = '555-0101' OR phone2 = '555-0101' OR phone3 = '555-0101';
CREATE TABLE customer_phone (
customer_id INTEGER NOT NULL,
phone TEXT NOT NULL,
PRIMARY KEY (customer_id, phone)
);
INSERT INTO customer_phone VALUES
(1,'555-0100'), (1,'555-0101'), (1,'555-0103'), (2,'555-0102');
SELECT 'tall' AS src, customer_id, COUNT(*) AS phones
FROM customer_phone
GROUP BY customer_id
ORDER BY customer_id;Example explained
Line 1The OR chain repeats the same predicate once per column; forget phone3 and rows quietly vanish while the query still succeeds.
Line 2A third number fits in phone3, but a fourth needs ALTER TABLE ... ADD COLUMN, which is a schema change forced by data.
Line 3customer_phone accepts any number of rows per customer, which is why customer 1 shows 3 phones with no DDL at all.
Line 4phone TEXT NOT NULL is possible because "no phone" is zero rows, so the NULL padding of the wide table disappears.
Important notes
Splitting a list loses its order, because rows in a table have no inherent sequence; store an explicit position column if "first phone" or "step 2" carries meaning.
Do not split a value the application only ever handles whole. Breaking an address into house number and street adds joins and reassembly work with no query benefit.
Common mistakes
Searching a packed list with skills LIKE '%SQL%': it also matches NoSQL and MySQL, so the result set is silently wrong, and the leading wildcard makes any index on the column useless.
Treating a JSON or array column as a fix: you can query inside it, but no foreign key can reference an element, so a misspelled tag like 'Pyhton' still gets stored.
Keeping the old comma column beside the new child table "for reports": the two copies drift apart and no constraint can keep them in step.
Try it yourself
Change, predict, then run
In a browser SQLite editor create recipe(id, name, ingredients TEXT) with one row for 'flour,water,salt' and one for 'flour,salted butter', then run WHERE ingredients LIKE '%salt%' and watch both rows come back. Rebuild it as recipe plus recipe_ingredient(recipe_id, ingredient) and get the single correct row with a join on ingredient = 'salt'.
Open the SQL workspaceCheck your understanding
A full_name column stores 'Ada Lovelace'. Under what condition does it violate first normal form?
- It never violates 1NF, because a text column always holds a single value.
- It violates 1NF as soon as the application must filter, sort or constrain the surname on its own.
- It violates 1NF because a string is made of characters and is therefore divisible.
- It violates 1NF only if two different people end up with the same full name.
Show answer
Atomicity is judged against the smallest value your queries need to address, and SQL operators, constraints and indexes only see whole column values, so a surname you sort or filter on must be its own column. Option 0 is tempting because the engine stores the string happily and it looks like one value, but 1NF is about hidden structure the queries must reach into, not about the storage type. Option 2 would make no text column atomic, and option 3 is about row identity and keys, not about packing several values into one cell.