First Normal Form (1NF) in SQL

Introduction

First Normal Form, commonly written as 1NF, is the foundational level of relational database normalization. It establishes the basic shape expected of relational data: a table has defined attributes, each row represents one occurrence at a consistent grain, and an ordinary attribute stores one value from its domain rather than an embedded collection of separately managed facts. Repeating groups such as phone_1, phone_2, and phone_3, or a comma-separated list such as Java, SQL, Python, usually indicate that multiple facts have been forced into one record.

Applying 1NF does more than make a table look tidy. It allows each value to be filtered, validated, indexed, inserted, updated, and deleted through normal relational operations. It also creates the structure needed for keys and relationships. However, 1NF is only the beginning. A table can satisfy 1NF while still repeating descriptions or containing partial and transitive dependencies that are addressed by later normal forms.

What Is First Normal Form?

A practical definition of 1NF is that each attribute contains one value from its defined domain, repeating groups are absent, and related collections are represented as rows in an appropriate relation. Consider a student record that stores Java, SQL in a courses column. Although the database sees one character string, the application interprets it as two independent enrollments. Those enrollments cannot be constrained or managed separately while they remain hidden inside the string.

Student IDStudent nameCourses
101AliceJava, SQL
102BobPython
103CarolHTML, CSS, Java

A 1NF representation gives each enrollment its own row. Alice then has one row for Java and another for SQL. This makes each course value directly available to SQL. The resulting table may still need further decomposition because the student name depends only on the student identifier, not on the full student-course key, but it has crossed the first structural boundary.

Why First Normal Form Is Important

Relational operators work most naturally when facts are represented individually. Searching a list requires substring logic, delimiters, escaping rules, and assumptions about capitalization and whitespace. Searching a normalized enrollment relation uses a direct predicate such as WHERE course_name = 'Java'. Adding one course creates one row, removing it deletes one row, and a unique key can prevent the same student from enrolling twice.

1NF also removes fixed limits hidden in a schema. A customer with three phone columns cannot receive a fourth number without a structural change, while a customer-phone relation accepts another row. The benefit is logical flexibility, not a guarantee of faster execution. Indexes, statistics, data distribution, query plans, and workload still determine performance.

Fundamental Rules of 1NF

PrincipleMeaning in practice
Single-valued attributesEach cell represents one value from its attribute domain
No repeating groupsThe same kind of fact is not spread across numbered columns
No nested relationsChild records are not stored as miniature tables inside a cell
Consistent domainsEach attribute represents a defined kind of information
Identifiable rowsCandidate keys describe how records can be distinguished
No meaningful row orderResult order is requested explicitly with ORDER BY
No meaningful column positionAttribute names, not physical positions, carry business meaning

The first four principles describe the central 1NF structure. Keys, unordered rows, and unordered attributes are broader relational-model properties that support a reliable practical design. A declared SQL primary key is strongly recommended, but the formal relational idea is that at least one candidate key identifies a tuple.

Understanding Atomic Values

An atomic value is one value as understood by its domain. Atomic does not mean physically indivisible. A SQL DATE contains year, month, and day components, yet it is one value in the date domain. A decimal amount has digits and scale but remains one numeric value. What matters is whether the application treats the stored content as one value or as a collection of independently meaningful values.

An employee's skills illustrate the difference. If Java, SQL, Python represents three skills that must be searched, validated, assigned, or removed independently, the string is hiding three business facts. The appropriate structure gives each employee-skill association a row. By contrast, a free-form note may legitimately contain commas because the note is managed as one text value.

Repeating Groups

A repeating group stores multiple occurrences of the same kind of information in one record. Numbered columns are the clearest example: course_1, course_2, and course_3. The design assumes an arbitrary maximum, leaves unused columns null, and forces every course query to inspect several attributes. Adding a fourth slot changes the schema and every dependent query.

Repeating groups also appear as delimited strings, arrays used as relational associations, and embedded child objects. Converting a repeating group normally produces a child or junction table. A student may have many enrollment rows; an order may have many order-item rows; a patient may have many patient-allergy rows. The parent remains one row, and the repeated relationship becomes an open-ended set of rows.

Before and After 1NF

Suppose a legacy table stores one student per row and combines course names. It is easy to display but hard to maintain correctly.

CREATE TABLE students_bad (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    courses      VARCHAR(500)
);

INSERT INTO students_bad
    (student_id, student_name, courses)
VALUES
    (101, 'Alice', 'Java,SQL,Python'),
    (102, 'Bob', 'HTML,CSS'),
    (103, 'Carol', 'SQL');

The courses column is technically one string to the DBMS, but the business interprets parts of that string as separate enrollments. A direct 1NF conversion places each enrollment in its own row:

CREATE TABLE student_courses (
    student_id   INT          NOT NULL,
    student_name VARCHAR(100) NOT NULL,
    course_name  VARCHAR(100) NOT NULL,
    PRIMARY KEY (student_id, course_name)
);

INSERT INTO student_courses
    (student_id, student_name, course_name)
VALUES
    (101, 'Alice', 'Java'),
    (101, 'Alice', 'SQL'),
    (101, 'Alice', 'Python'),
    (102, 'Bob', 'HTML'),
    (102, 'Bob', 'CSS'),
    (103, 'Carol', 'SQL');

This table demonstrates 1NF, but it repeats each student's name. Later normalization would place students and courses in their own tables and retain only their keys in an enrollments table.

Primary Keys and Candidate Keys

A candidate key is a minimal set of attributes that uniquely identifies a row. One candidate key is selected as the primary key, while others may be enforced with unique constraints. Keys are important because converting a list into rows creates new records that need clear identity. In an employee-skills relation, the pair (employee_id, skill_id) can identify one assignment if an employee may hold each skill only once.

A primary key does not prove that a table satisfies 1NF. The students_bad table has a primary key but still hides multiple enrollments in one column. Conversely, formal discussions of 1NF focus on relation structure rather than whether SQL syntax explicitly declared a primary key. In production design, declaring the intended key is essential because SQL tables can otherwise permit duplicates.

Composite Keys in 1NF

A composite key contains more than one attribute. Relationship tables often use them naturally. If a student may enroll in a course once, the pair of identifiers is a candidate key, while grade describes that enrollment.

CREATE TABLE enrollments (
    student_id INT     NOT NULL,
    course_id  INT     NOT NULL,
    grade      CHAR(1),
    PRIMARY KEY (student_id, course_id)
);

Composite keys become especially important when studying Second Normal Form. If an attribute in this table depends only on student_id or only on course_id, it is partially dependent on the key and should normally move to the corresponding entity table.

NULL Values and 1NF

The presence of NULL in a SQL table does not automatically establish a 1NF violation. NULL represents missing, unknown, or inapplicable information according to the model. A nullable middle name can still be one attribute value. SQL's null marker does introduce three-valued logic, so queries must use IS NULL rather than = NULL.

Repeating-column designs often create large numbers of avoidable nulls because most records do not use every numbered slot. Replacing those slots with related rows removes the artificial emptiness. Optional scalar attributes may remain nullable when the business meaning is clear, while mandatory fields should use NOT NULL.

Data Types and Attribute Domains

Each attribute should represent a coherent domain. An employee identifier, name, salary, and joining date have different meanings and deserve appropriate SQL types. Defining a salary as DECIMAL(10,2) and a joining date as DATE lets the database reject many invalid representations and apply the correct operators.

Good types support 1NF but do not prove every normalization rule. A VARCHAR(500) can store a comma-separated list even though the application treats it as several values. Data type selection and logical grain must therefore be reviewed together.

Row Uniqueness

Relations are sets of tuples, but SQL tables can contain duplicate rows when no suitable key or unique constraint exists. An employee-skills table without a key may accept the pair (101, 'Java') twice. That duplication may cause inflated counts and ambiguous updates.

CREATE TABLE employee_skills (
    employee_id INT          NOT NULL,
    skill_name  VARCHAR(100) NOT NULL,
    PRIMARY KEY (employee_id, skill_name)
);

The key makes the intended business rule executable. If repeated assessments of the same skill are valid, then date, assessment identifier, or another attribute changes the grain and may become part of the key.

Row Order and Column Order

Neither row order nor column order carries inherent relational meaning. A database may return rows in a convenient physical order during one execution and a different order later. A query that needs a sequence must use ORDER BY. Likewise, selecting salary before employee name changes presentation, not the underlying facts.

These properties are broader than 1NF, but they prevent spreadsheet assumptions from leaking into relational design. Business meaning should come from attribute names, keys, values, and relationships rather than from where a value happens to appear on screen.

Functional Dependencies

A functional dependency states that one set of attributes determines another. If each student_id identifies one student name, then student_id -> student_name. In the 1NF demonstration table, the candidate key is (student_id, course_name), yet the student name depends only on the first part. That partial dependency is allowed by 1NF but becomes the central issue in 2NF.

This distinction explains why splitting a list is not the end of normalization. 1NF corrects the representation of repeating values. Higher normal forms examine which keys own each non-key fact and whether the table combines attributes from different entities.

First Normal Form vs Second Normal Form

Question1NF2NF
Single-valued attributes required?YesYes
Repeating groups removed?YesYes
Partial dependencies removed?Not requiredRequired for non-prime attributes
Main focusRelational structure of valuesDependency on the complete candidate key

A student-course table containing student name, course name, and grade can satisfy 1NF while violating 2NF. A stronger design separates students, courses, and enrollments. The enrollment retains student_id, course_id, and grade because grade belongs to that association.

First Normal Form vs Third Normal Form

Third Normal Form builds on both 1NF and 2NF. It also addresses cases in which a non-key fact depends on another non-key fact rather than directly on a candidate key. For example, an employee row containing department identifier, department name, and department location can repeat facts determined by the department identifier.

A table in 3NF necessarily satisfies the lower normal forms. The reverse is not true. 1NF is the structural starting point that exposes values and relationships so their dependencies can be analyzed accurately.

Employee Skills Example

An employee record with a skills string combines a variable collection into one field. Converting the collection to employee-skill rows makes each assignment searchable and removable. A durable model goes further: employees stores employee facts, skills defines controlled skills, and employee_skills connects their identifiers.

The junction table can also carry facts about the relationship, such as proficiency level, verification date, or certifying manager. Those details were difficult to represent while all skills lived inside one string.

Customer Phone Numbers Example

Customers may have zero, one, or many phone numbers. Numbered phone columns impose a limit and make it difficult to record type, verification, or preference. A customer_phones table gives each number a row and can enforce uniqueness for the chosen business rule.

CREATE TABLE customer_phones (
    customer_id  INT         NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    phone_type   VARCHAR(20),
    PRIMARY KEY (customer_id, phone_number),
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

One number can now be validated, marked as preferred, or removed without parsing and rebuilding a list.

E-Commerce Orders Example

An order row containing Laptop, Mouse in a products field hides individual lines. It cannot naturally store a quantity, unit price, discount, tax treatment, or fulfillment status for each product. An orders table should represent the transaction header, while order items represent products purchased within it.

The order-item table has one row per product line and references both order and product. Historical unit price belongs on that line because it records what was charged at purchase time. This is valid repetition of a historical fact, not an uncontrolled list.

Student Enrollment Example

Students and courses form a many-to-many relationship. A student can enroll in many courses, and a course can contain many students. The enrollments relation resolves that relationship and can store grade, enrollment date, completion status, and attempt number. Its key must match the actual rule about repeated attempts.

A basic table with student name and one course per row demonstrates 1NF, but separating student and course entities prevents repeated descriptions and prepares the model for higher normal forms.

Software Testing Example

A test case often runs against multiple browsers. Storing Chrome, Firefox, Edge in one column makes browser-level execution and reporting difficult. A test-case-browser relation creates one supported combination per row. An execution table can then record results for a specific test case, browser, build, and environment.

This structure supports questions such as which Firefox tests failed in the current release without substring searches or duplicated test-case definitions.

Inventory Example

A product can be stocked in many warehouses, and every warehouse contains many products. A warehouses list on the product row cannot represent stock quantity, reserved quantity, reorder threshold, or last-counted time for each location. An inventory table keyed by product and warehouse represents the association directly.

Adding a warehouse creates warehouse and inventory rows rather than altering the products schema. This demonstrates how 1NF removes artificial limits and supports growth.

Healthcare Example

A patient may have multiple allergies, and the same allergy can apply to many patients. Storing allergy names in one text list prevents controlled coding and individual severity or reaction details. Separate patient, allergy, and patient-allergy tables expose each clinical relationship.

Real healthcare systems also require terminology standards, privacy controls, auditing, consent, and historical rules. 1NF provides a relational structure, but it does not replace those domain safeguards.

Course Content Example

A learning site should not store all topics in one course field. Courses, topics, lessons, and articles have distinct identities and metadata. A topic row can reference its course and carry title, sequence, publication status, and canonical slug. New topics then become data rather than schema changes.

If ordering is required for display, store an explicit sequence attribute and query with ORDER BY. The physical order of inserted topic rows is not a stable content-navigation rule.

How to Convert Unnormalized Data to 1NF

Begin by profiling the source rather than splitting every string blindly. Locate numbered attributes, arrays, delimited collections, and embedded objects. Determine which contents are independent business facts and which are legitimate single values. Confirm delimiters, escaping rules, blanks, duplicated items, inconsistent spellings, and invalid identifiers before migration.

  1. Define the grain of the current record and the intended target tables.
  2. Identify repeating groups and multivalued business attributes.
  3. Create parent, child, or junction tables with appropriate domains.
  4. Choose candidate keys and declare primary or unique constraints.
  5. Add foreign keys for relationships that must reference valid parents.
  6. Parse and clean legacy values into individual rows.
  7. Reconcile counts, distinct values, orphan records, and control totals.
  8. Test normal inserts, updates, deletes, and retrieval queries.
  9. Move consumers to the new structure and retire the legacy field.
  10. Continue dependency analysis for 2NF and 3NF.

Complete SQL Conversion Example

The employee-skills example can be converted into separate employee and association tables. A reference table for skills can be added when names need centralized governance.

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL
);

CREATE TABLE employee_skills (
    employee_id INT          NOT NULL,
    skill_name  VARCHAR(100) NOT NULL,
    PRIMARY KEY (employee_id, skill_name),
    FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES
    (101, 'Alice'),
    (102, 'Bob'),
    (103, 'Carol');

INSERT INTO employee_skills VALUES
    (101, 'Java'),
    (101, 'SQL'),
    (102, 'Python'),
    (102, 'HTML'),
    (103, 'Java');

A normal join reconstructs the useful display shape without recreating the storage problem:

SELECT
    e.employee_id,
    e.employee_name,
    s.skill_name
FROM employees AS e
INNER JOIN employee_skills AS s
    ON s.employee_id = e.employee_id
ORDER BY e.employee_id, s.skill_name;

Production migrations need controlled parsing, duplicate handling, audit logs, validation, and a rollback plan. Data should not be considered migrated merely because the insert statements completed.

Query Simplicity and Indexing

A substring condition such as skills LIKE '%Java%' can match unintended text, depends on formatting, and is difficult to protect with ordinary relational constraints. In a relationship table, skill_name = 'Java' expresses the actual condition. An index on skill_name may support selective lookups, while the primary key supports employee-specific access.

Normalization does not guarantee the index will be beneficial. The optimizer considers table size, selectivity, statistics, joins, and requested columns. Use execution plans and workload measurements instead of assuming that any structural change automatically improves speed.

Data Integrity and Scalability

Representing one value per row makes validation granular. A key rejects duplicate employee-skill pairs, a foreign key rejects missing employees, and a skill reference table can limit assignments to approved skills. Individual values can also receive status, timestamps, and audit information.

The same design scales logically because the number of related values is no longer encoded in the number of columns. A customer receives a fourth phone number through another row. Physical scale still requires suitable indexes, partitioning, capacity planning, and query design, but growth no longer requires recurring schema expansion.

Storage Efficiency

1NF can remove empty numbered columns and avoid storing delimiters or repeated formatting. It may also create more rows, keys, and indexes, so reduced disk usage is not guaranteed. The principal benefit is correct relational representation and independent management of facts.

Storage decisions should consider data type widths, compression, index design, retention, and access patterns. Do not preserve a multivalued field merely because it appears compact; hidden parsing and integrity costs often outweigh small storage savings.

JSON, Arrays, and 1NF

Modern databases support JSON, arrays, XML, and other complex types. Whether such a value violates a formal interpretation of 1NF depends on the data model and the domain exposed by the DBMS. A JSON document may be treated as one typed value, yet an application may use it to hide a collection of relational facts that need independent constraints and joins.

Use related tables when elements require referential integrity, uniqueness, frequent relational filtering, individual updates, or relationships with other entities. JSON is appropriate when a document is genuinely managed as a document, its internal shape is variable, and relational constraints are not required for every member. The question is not whether JSON is always wrong, but whether the chosen representation matches how the business uses the data.

Addresses and Composite Values

An address stored as one string is not automatically a 1NF violation. If the application treats it as one formatted delivery label, it can be one value in an address domain. If the system filters by city, validates postal code, applies tax jurisdiction, or localizes formatting, separate address attributes or a dedicated address table may be more useful.

The same reasoning applies to names, coordinates, money, and dates. Atomicity is defined by the chosen domain and business operations, not by whether a human can imagine smaller components.

Dates in First Normal Form

A DATE value remains atomic even though SQL can extract its year, month, and day. Store it with a native date type so comparisons and calculations use date semantics. Breaking every date into three columns would usually make validation and range queries harder rather than more relational.

Multiple independent dates, such as several appointment occurrences, should not be packed into one string. Each appointment is a separate event row because it has its own time, status, participants, and lifecycle.

Common 1NF Mistakes

  • Storing comma-separated independent values in an ordinary text column.
  • Creating numbered columns such as item_1, item_2, and item_3.
  • Assuming that declaring a primary key automatically guarantees 1NF.
  • Assuming that 1NF eliminates every kind of redundancy.
  • Treating every structured scalar value, including dates, as non-atomic.
  • Claiming that any NULL value automatically violates 1NF.
  • Ignoring candidate keys and allowing duplicate relationship rows.
  • Relying on insertion order instead of an explicit ordering attribute and ORDER BY.
  • Splitting legacy strings without preserving duplicates, escaping, or historical meaning.
  • Stopping at 1NF without checking partial and transitive dependencies.

First Normal Form Best Practices

Model business meaning before writing table definitions. State what one row represents, identify candidate keys, define attribute domains, and distinguish scalar values from collections of separately managed facts. Use relationship tables for one-to-many and many-to-many associations, and enforce the intended uniqueness and references with database constraints.

During migration, retain the original source until reconciliation is complete. Compare counts and distinct associations, investigate invalid duplicates, and test boundary cases such as entities with no related records or unusually many related records. Review application code and reports so no consumer continues to parse the obsolete list.

Finally, treat 1NF as a foundation rather than a finish line. Analyze functional dependencies, move facts to the keys that own them, preserve valid historical snapshots, and measure physical performance after the logical model is correct.

Interview Questions and Answers

What is First Normal Form?

1NF is the foundational relational normal form in which attributes hold single values from defined domains and repeating groups are represented as separate rows or relations.

What is a repeating group?

It is multiple occurrences of the same type of information stored within one record, such as numbered phone columns or a comma-separated course list.

Does a primary key guarantee 1NF?

No. A keyed table can still hide several independent values in one column or contain repeating groups.

Can NULL exist in a table designed around 1NF?

Yes. NULL is not by itself proof of a 1NF violation, although its business meaning and SQL behavior must be handled carefully.

Is a JSON column always a 1NF violation?

No. Its suitability depends on the defined domain and how the contained information is managed. Relational tables are preferable when individual members require constraints, relationships, or frequent independent access.

Does 1NF remove partial dependencies?

No. Partial dependencies are addressed by 2NF after a table already satisfies 1NF.

Does 1NF guarantee faster queries?

No. It often makes predicates and constraints clearer, but performance depends on indexes, plans, data volume, distribution, concurrency, and infrastructure.

Practical Exercises

  1. Convert a student course list into individual enrollment rows.
  2. Replace three numbered phone columns with a customer-phone relation.
  3. Choose a candidate key for an employee-skills table.
  4. Explain why a date can be atomic while a list of appointment dates is not one scalar fact.
  5. Design order and order-item tables for products, quantities, and unit prices.
  6. Add foreign keys and uniqueness rules to a student enrollment schema.
  7. Identify the partial dependency in a table keyed by student and course that also stores student name.
  8. Evaluate whether a JSON preferences document or JSON skills array matches the business use.
  9. Write reconciliation queries for a migration from comma-separated skills.
  10. Review the resulting schema for 2NF and 3NF issues.

Quick Reference

ConceptMeaning
1NFFoundational relational normal form
Atomic attributeOne value from the attribute's defined domain
Repeating groupRepeated occurrences of one kind of fact within a record
Candidate keyMinimal attribute set that uniquely identifies a row
Composite keyA key containing multiple attributes
Junction tableA relation representing associations between entities
Functional dependencyA relationship in which one attribute set determines another
2NFThe next normal form, addressing partial dependencies

Step-by-Step 1NF Checklist

  1. Define the business grain of every table.
  2. Find lists, arrays, nested records, and numbered columns.
  3. Decide which contents are independently managed facts.
  4. Define a consistent domain and SQL type for each attribute.
  5. Identify candidate keys and choose a primary key.
  6. Move repeating facts into child or junction rows.
  7. Add unique constraints and foreign keys.
  8. Clean and migrate legacy data.
  9. Reconcile source and target facts.
  10. Test inserts, updates, deletes, joins, and ordering.
  11. Remove obsolete list fields and parsing logic.
  12. Continue dependency analysis for higher normal forms.

Key Takeaway

First Normal Form turns repeating collections into explicit relational facts. Each ordinary attribute represents one value from a defined domain, repeating groups become rows, and keys make those rows identifiable. The resulting structure is easier to query, validate, extend, and connect through normal SQL operations.

1NF should be applied with domain awareness. Dates and other structured scalar values can remain atomic, NULL does not automatically invalidate a design, and complex database types require judgment based on how their contents are used. After reaching 1NF, inspect functional dependencies and continue toward 2NF and 3NF where the business rules require further decomposition.