skip to content

What does it mean for a table to be in first normal form, and what exactly does the requirement that values be 'atomic' rule out?

level: juniorimportance: must knowfreq 70%

answer

  1. one value per cell, one row per fact
  2. no phone1/phone2/phone3
  3. no duplicate rows — declare a key
  4. atomic = database never parses it
  5. fix = child table + foreign key

basics

~20 s

A table is in first normal form when every column holds one single value of its declared type — no lists, no nested structures, no repeating groups such as phone1/phone2/phone3 — and every row is uniquely identifiable, normally by a primary key.

solid answer

~60 s

First normal form is the entry condition for the relational model. It requires that: - **Every cell holds a single value** of the column's domain, not a list, a set, or a nested record. `tags = 'sql,indexing,oltp'` breaks this because the application must split the string to use it. - **There are no repeating groups** — no `phone1`, `phone2`, `phone3` encoding a one-to-many relationship across columns. - **Every row is distinguishable**: no duplicate rows, which in practice means declaring a primary key. Row and column order carry no meaning. "Atomic" is defined relative to how the database is asked to use the value, not by any absolute notion of indivisibility. A date is one value even though it contains a year; a full address in one column is a violation the moment you need to filter or group by city. The test is: does anything have to decompose this value inside a query or in application code to do its job? The fix is always the same shape — move the repeating part into its own table, one row per value, joined by a foreign key.

code

sql · 27 lines
sql
-- violates 1NF: list in a column, repeating group, no key
CREATE TABLE customer_bad (
  name    VARCHAR(100),
  tags    VARCHAR(200),   -- 'vip,newsletter'
  phone1  VARCHAR(20),
  phone2  VARCHAR(20),
  phone3  VARCHAR(20)
);

-- 1NF
CREATE TABLE customer (
  id    BIGINT PRIMARY KEY,
  name  VARCHAR(100) NOT NULL
);

CREATE TABLE customer_tag (
  customer_id BIGINT NOT NULL REFERENCES customer(id),
  tag         VARCHAR(50) NOT NULL,
  PRIMARY KEY (customer_id, tag)
);

CREATE TABLE customer_phone (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customer(id),
  phone       VARCHAR(20) NOT NULL,
  kind        VARCHAR(10) NOT NULL
);

go deeper

for a junior

State the three requirements — single-valued cells, no repeating groups, uniquely identifiable rows — and give the comma-separated-list example.

for a middle

Explain that atomicity is relative to how the data is queried, and describe the child-table fix including an explicit position column when order matters.

for a senior

Discuss the concrete costs — lost foreign keys, unusable indexes, lost updates on read-modify-write of a list — and when a document column is a considered trade-off rather than an accident.

for a principal

Frame 1NF as the precondition that makes the rest of normalization and the engine's integrity machinery applicable at all, and describe how you decide per-attribute whether the database needs to see inside a value.

## Where 1NF sits Normalization is a series of conditions that progressively remove redundancy from a schema. 1NF is the first and most basic: it is not really about redundancy at all, it is about a table being a *relation* in the first place. The higher forms (2NF, 3NF, BCNF) assume you already have a well-formed relation and then talk about dependencies between attributes. If a table is not in 1NF, those questions cannot even be asked. ## The three requirements **1. Single-valued columns.** Each cell contains one value drawn from the column's domain. Violations look like: - `tags VARCHAR(200)` holding `'sql,indexing,oltp'` — a list crammed into a string. - `skills` holding a serialized array or a nested document that the application unpacks. - `full_name` holding `'Ada Lovelace'` in a system where you must sort by surname. **2. No repeating groups.** The other way people encode a one-to-many relationship is horizontally: `phone1`, `phone2`, `phone3`, or `item1_sku`, `item1_qty`, `item2_sku`, `item2_qty`. Structurally this is the same violation turned sideways — the column set encodes a collection. **3. Row uniqueness.** A relation is a *set* of tuples, so duplicates are meaningless. SQL tables permit duplicates, so you get this property only by declaring a primary key (or at minimum a unique constraint on the business identity). Without one, two identical rows are indistinguishable and you cannot address one of them. A fourth, often forgotten point: **order carries no information**. You may not rely on "the third row" or "the first column"; if position means something (priority, sequence), that meaning must become a column. ## What "atomic" really means This is the part candidates get wrong. There is no absolute test for indivisibility — every string can be split, every number has digits. `1996-04-12` is a date, and nobody calls it a violation even though it contains a month. A JPEG in a BLOB is one value even though it has internal structure. The useful, engineering definition: **a value is atomic with respect to a schema if the database is never asked to look inside it.** Atomicity is therefore a property of the pairing between the data and its use: - Storing a postcode inside `address_line` is fine if the system only ever prints the address on a label. - The same design is a 1NF violation the day someone needs "all customers in postcode area SW1", because now a query must decompose the value. The practical interview test is: *does any query, index, constraint or piece of application code have to parse this column to do its job?* If yes, it is not atomic for this schema. ## Why the violations hurt Once a column holds a list, the database stops being able to help you: - **No integrity.** You cannot point a foreign key at an element inside a string, so nothing stops `'sql,sqll,SQL '` from accumulating. - **No useful indexing.** A B-tree indexes the whole string. Finding rows containing one tag becomes a wildcard scan, and the wildcard also matches substrings — searching for `sql` matches `nosql`. - **Awkward everything.** Counting per tag, joining to a tag description, adding or removing one element — each becomes string surgery instead of a row insert or delete. - **Concurrency hazards.** Two sessions adding a different tag both read-modify-write the same string; one update is lost. Two rows in a child table would not have collided. - **Type erasure.** A comma-joined list of numbers is text; comparisons and range predicates no longer behave numerically. Repeating-group columns fail differently but just as badly: the arity is fixed and arbitrary (why three phones?), most cells are NULL, adding a fourth means DDL and a code change, and any search has to check every column. ## The canonical fix Both violations resolve into the same move: **extract the repeating part into its own table, one row per value, with a foreign key back to the parent.** `customer(id, name)` plus `customer_phone(customer_id, phone, kind)`. Each phone is now a first-class row that can be constrained, indexed, counted, joined and validated. If ordering matters, add an explicit `position` column, because row order in a table means nothing. ## Nuance worth voicing Modern engines offer array and document column types, and a well-scoped document column can be a defensible engineering choice even though it is formally not 1NF. The test is the same one as always: if the database has to reach inside the value to filter, join or enforce integrity, you have paid for a violation without the benefit. Say the trade-off out loud rather than pretending array columns do not exist — but be able to state that strictly they place the table outside 1NF.

  • Is storing a full postal address in a single column a 1NF violation?
    It depends entirely on how the system uses it. If the address is only ever printed or displayed as a block, the whole string is one atomic value and the design is fine. The moment a requirement appears to filter by city, group by country, or validate a postcode, the database has to reach inside the value, and it becomes a violation that should be split into separate columns.
  • Does the relational model require a primary key, and how does that relate to 1NF?
    A relation is a set of tuples, so duplicates cannot exist and every tuple is identifiable — that is where row uniqueness in 1NF comes from. SQL tables are bags rather than sets and do permit duplicate rows, so you must recover the property by declaring a primary key. Without one, two identical rows cannot be told apart and an UPDATE or DELETE cannot target just one of them.

1NF is the difference between a spreadsheet cell containing 'milk, eggs, bread' and three separate rows on a shopping list — only the second lets you tick one item off, count them, or sort them.

saying these in an interview costs you the question

  • Defining atomic as 'cannot be divided at all', which would make dates and strings violations
  • Believing 1NF is about eliminating redundancy — that is 2NF and 3NF; 1NF is about being a well-formed relation
  • Thinking phone1/phone2/phone3 is fine because each cell holds a single value
  • Claiming a table without a primary key can still be in 1NF as long as its columns are atomic
  • Assuming any use of a JSON or array column is automatically acceptable relational design

context

resolving links…