What Is Normalization in SQL?

Introduction

Normalization is a relational database design technique that organizes facts into well-structured tables. Its purpose is to reduce unnecessary duplication, prevent modification anomalies, and make data relationships explicit through keys and constraints. Instead of placing every detail about employees, departments, projects, and locations in one large table, a normalized design separates each subject according to what its attributes describe.

Normalization is not simply the act of creating more tables, and it does not mean eliminating every repeated value. A department identifier must repeat in employee rows to represent a relationship. The concern is unnecessary repetition of the department's name, location, and manager in every employee row. Good normalization follows dependencies between facts, preserves valid relationships, and creates a schema in which each fact has a clear owner.

What Is Normalization in SQL?

Database normalization is the process of analyzing attributes and their dependencies, then decomposing broad tables into smaller related tables when that separation prevents redundancy and anomalies. Primary keys identify rows, foreign keys connect entities, and constraints enforce rules at the correct level.

Suppose an employee table stores employee data together with department name and location. Every employee in the same department repeats the same department facts. A normalized design stores employees and departments separately. Employees retain the department identifier needed for the relationship, while the department's descriptive facts are maintained once.

Before and After Normalization

EmployeeDepartmentLocation
AliceTechnologyChicago
CarolTechnologyChicago
BobHuman ResourcesDallas

The repeated department facts can be separated into a departments table and referenced by employees:

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL,
  department_location VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name VARCHAR(100) NOT NULL,
  department_id INTEGER NOT NULL,
  FOREIGN KEY (department_id)
    REFERENCES departments(department_id)
);

The department name and location now have one authoritative row. Employees still share the department identifier because that repetition represents the relationship rather than redundant descriptive data.

Why Normalization Is Important

Poorly structured tables make data harder to insert, update, delete, validate, and understand. If the Technology department moves from Chicago to New York and its location appears in thousands of employee rows, every copy must change correctly. A missed row creates contradictory information.

In the normalized design, one department row changes and every related employee remains associated through the foreign key. This improves consistency, makes schema responsibilities clearer, and supports independent management of departments and employees. Normalization primarily improves logical integrity and maintainability; it does not guarantee that every query becomes faster.

A School Analogy

Imagine a school file where each student row also contains the teacher's name, phone number, and office. If one teacher has one hundred students, the same contact details appear one hundred times. Changing the teacher's phone number requires one hundred coordinated edits, and deleting the last student could accidentally remove the only record of the teacher.

A normalized design stores teachers independently and places the teacher identifier in student assignments. The teacher can exist before students are assigned, contact details change once, and student deletion does not erase the teacher. The analogy illustrates that normalization separates facts according to the entity they describe.

Objectives of Normalization

The main objectives are to minimize unnecessary redundancy, prevent insertion, update, and deletion anomalies, preserve valid dependencies, enforce relationships, and simplify long-term maintenance. A normalized schema should make it clear where a fact belongs and which key determines it.

Normalization also improves adaptability. A department can gain new attributes without expanding every employee row, and applications can manage departments independently. This separation creates intentional boundaries that support validation, security, ownership, and reuse.

Understanding Data Redundancy

Redundancy means storing the same fact unnecessarily in multiple places. Repetition alone is not proof of a design problem. Foreign keys, status values, and transaction dates naturally repeat because each occurrence describes a different row. The important question is whether repeated attributes depend on a key that belongs to another entity.

If department_name depends on department_id, placing the name in every employee row duplicates a department fact. Storing department_id in each employee row is necessary because it represents which department the employee belongs to.

Database Anomalies

An anomaly is an undesirable consequence of a table structure during insertion, update, or deletion. Anomalies reveal that unrelated facts have been forced into one row type. They can make valid business events impossible, require repeated changes, or cause one operation to remove information about another subject.

Insertion Anomaly

An insertion anomaly occurs when one fact cannot be stored without supplying an unrelated fact. If department details exist only inside employee rows, a new Finance department cannot be recorded until it has an employee. A placeholder employee or nullable employee fields would mix two independent events.

INSERT INTO departments
  (department_id, department_name, department_location)
VALUES
  (30, 'Finance', 'Boston');

A separate department table allows the department to exist before recruitment begins.

Update Anomaly

An update anomaly occurs when the same fact appears in multiple rows and every copy must change together. Renaming Technology to Engineering across employee rows can leave inconsistent names if one update is missed. In a normalized schema, the department fact changes once.

UPDATE departments
SET department_name = 'Engineering'
WHERE department_id = 10;

Deletion Anomaly

A deletion anomaly occurs when removing one row unintentionally removes the only record of another fact. If the last Human Resources employee leaves and department information exists only in employee rows, deleting that employee also erases evidence that the department exists. Independent department storage prevents this loss.

Comparing the Three Anomalies

AnomalyProblemExample
InsertionOne fact requires unrelated dataA department requires a placeholder employee
UpdateOne fact must change in many rowsA department rename creates inconsistent copies
DeletionRemoving one fact removes anotherDeleting the last employee erases a department

Core Terminology

Normalization uses several connected concepts. An entity is a distinguishable subject such as an employee or department. An attribute describes that subject. A relation is represented as a table, a tuple as a row, and a candidate key as a minimal attribute set that uniquely identifies a row. Dependencies express how values determine other values.

Understanding the business meaning behind these terms matters more than memorizing definitions. Normalization decisions come from business rules, not from column names alone.

Functional Dependency

A functional dependency exists when one attribute or set of attributes determines another. It is written conceptually as X -> Y, meaning each valid value of X corresponds to one value of Y. In a department table, department_id -> department_name if each identifier represents exactly one department name.

Dependencies describe semantics, not accidental sample data. If current records happen to show one city per employee name, that does not prove employee name determines city. The rule must hold for all valid states of the database.

Keys in Normalization

A primary key is the chosen candidate key used as the main row identifier. A foreign key references a candidate or primary key in another table and protects relationship integrity. A composite key uses multiple attributes together when no proper subset uniquely identifies the row.

For example, an enrollment table may use (student_id, course_id) as its key. The pair identifies one enrollment, while neither column alone is unique. Composite keys are especially important when analyzing second normal form.

Partial Dependency

A partial dependency occurs when a non-key attribute depends on only part of a composite candidate key. Suppose an order-item table has the key (order_id, product_id) but also stores order_date. The date depends only on order_id, not on the complete key, so it belongs with the order.

Partial dependency is relevant when a key contains multiple attributes. A table whose candidate keys are all single-column cannot have this specific kind of violation.

Transitive Dependency

A transitive dependency occurs when a non-key attribute determines another non-key attribute. In an employee table, employee identifier may determine department identifier, and department identifier determines department location. Therefore the employee determines the location indirectly. The location is a department fact and should generally move to the department table.

Defining Table Grain

Table grain states precisely what one row represents. A row may represent one employee, one department, one order, one order item, or one employee assignment during a time period. Normalization becomes difficult when a table mixes grains. An order-level date repeated in every item row and a product-level description repeated in every sale are signs that facts with different owners have been combined.

State the grain before choosing columns or keys. Then ask whether every non-key attribute describes exactly one row at that grain. If an attribute describes a broader entity, a narrower detail, or a separate relationship, it probably belongs elsewhere. Clear grain also improves query design because developers know whether joins multiply rows legitimately or accidentally.

Candidate Keys and Surrogate Keys

A surrogate key is an artificial identifier such as a generated integer. It can provide a compact, stable reference, but adding one does not remove the business candidate keys. If email must be unique for an account or a product code must be unique within a supplier, those rules still need constraints. Otherwise duplicate business facts can exist under different surrogate identifiers.

Normalization analysis must consider every candidate key because dependencies may be hidden when attention is limited to the chosen primary key. Surrogates help implementation and relationships, while candidate keys express business uniqueness. A sound design often uses both: a surrogate primary key for references and unique constraints for alternate natural identifiers.

Current Facts vs Historical Facts

Two attributes with similar names may represent different facts because they apply at different times. A product's current price belongs to the product, while the unit price paid on an order item belongs to the historical transaction. Replacing the transaction value with a reference to the current price would make old invoices change whenever the catalog price changes.

The same distinction applies to shipping addresses, tax rates, job assignments, and account ownership. Normalization does not require removing valid snapshots. Instead, the model should name their meaning clearly and attach them to the correct event or effective period. Temporal meaning is part of the dependency.

Normalization and Database Constraints

Separating tables is not enough if the database permits invalid relationships and duplicate entities. Primary keys identify rows, foreign keys prevent references to missing parents, unique constraints protect candidate keys, check constraints restrict valid values, and nullability expresses whether a fact is required. Together these rules make the normalized design executable rather than merely diagrammatic.

Constraints also help documentation and, in some systems, query optimization. They should reflect genuine business rules and account for lifecycle behavior such as restricted, cascading, or soft deletion. Application validation remains useful for clear feedback, but it should not be the only protection against concurrent invalid writes.

What Are Normal Forms?

Normal forms are progressively stronger design criteria. First normal form addresses row and value structure. Second normal form removes partial dependencies. Third normal form removes inappropriate transitive dependencies. BCNF strengthens determinant requirements, while fourth and fifth normal forms address multivalued and join dependencies.

Higher is not automatically better for every operational design. The goal is a correct, understandable schema that handles the actual business rules. Most transactional designs focus heavily on 1NF through 3NF or BCNF, then evaluate rarer higher-form issues when they appear.

First Normal Form

A table in first normal form has a consistent relational structure in which each row can be identified and each column position holds one value from its defined domain. Repeating groups such as phone_1, phone_2, and phone_3 or a comma-separated product list make querying, validation, and referential integrity difficult.

A multivalued employee-phone relationship should use a separate table with one phone per row. This supports any number of phones without altering the employee schema.

CREATE TABLE employee_phones (
  employee_id INTEGER NOT NULL,
  phone_number VARCHAR(30) NOT NULL,
  phone_type VARCHAR(20),
  PRIMARY KEY (employee_id, phone_number),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Second Normal Form

A relation is in second normal form when it is in 1NF and every non-prime attribute depends on the whole of every candidate key rather than only part of a composite key. The rule prevents one table from mixing facts at different key grains.

In an order-item relation keyed by order and product, quantity and sale price describe the pair. Customer identifier and order date depend only on the order, while product name depends only on the product. Separating orders, products, and order items gives each fact the correct owner.

Third Normal Form

Third normal form builds on 2NF by preventing non-key attributes from depending improperly on other non-key attributes. If an employee row contains department identifier, department name, and department location, the descriptive department fields depend on department identifier rather than directly on the employee key.

Moving those fields to departments removes the transitive dependency. The employee retains only the foreign key required to represent membership.

Boyce-Codd Normal Form

BCNF requires every determinant of a nontrivial functional dependency to be a candidate key. It is stronger than 3NF and addresses certain overlapping-candidate-key situations that 3NF permits. BCNF analysis depends on complete business rules; applying it mechanically without knowing all valid dependencies can produce the wrong design.

Fourth Normal Form

Fourth normal form addresses independent multivalued facts stored together. If an instructor can have multiple skills and multiple office locations independently, combining both sets in one table creates every skill-location combination. Separate instructor skills and instructor locations represent the independent relationships without artificial multiplication.

Fifth Normal Form

Fifth normal form addresses join dependencies where a relation can be decomposed into smaller relations and reconstructed without spurious rows, but the need is not explained by simpler functional or multivalued dependencies. It appears less often in routine application design and requires careful proof of business constraints.

Normal Forms at a Glance

Normal formMain concernTypical design question
1NFRepeating groups and value structureDoes each row-column position hold one domain value?
2NFPartial dependencyDoes each non-key fact depend on the whole key?
3NFTransitive dependencyDoes a non-key fact belong to another entity?
BCNFDeterminantsIs every determinant a candidate key?
4NFIndependent multivalued factsAre unrelated sets being multiplied?
5NFJoin dependenciesCan decomposition avoid spurious combinations?

A Step-by-Step Normalization Example

Consider one sales table containing order identifier, order date, customer details, product details, quantity, and unit price. Its natural grain is unclear because order data repeats for every product and product data repeats across orders. Begin by identifying candidate keys and dependencies. The pair (order_id, product_id) may identify a line if each product appears once per order.

Move order-level facts into orders, customer facts into customers, and product facts into products. Keep quantity and transaction-specific unit price in order items because they depend on the complete order-product relationship. Use foreign keys to reconnect the decomposed entities.

Normalized Sales Implementation

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name VARCHAR(120) NOT NULL,
  email VARCHAR(200) UNIQUE
);

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name VARCHAR(150) NOT NULL,
  current_price DECIMAL(10, 2) NOT NULL
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  order_date DATE NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
  order_id INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

The order-item price intentionally records the transaction price rather than depending on the product's current price. Similar names do not imply the same fact or time meaning.

Database Decomposition

Decomposition splits a relation into smaller relations. A good decomposition reduces anomalies while preserving the ability to reconstruct valid information. It should be evaluated for losslessness, dependency preservation, key placement, and the business operations the schema must support.

Lossless Decomposition

A decomposition is lossless when joining the resulting tables on the intended relationship reconstructs exactly the valid original information, without losing rows or generating spurious combinations. Shared keys and constraints are central to this property. Separating employees and departments by department identifier is lossless when each employee references one valid department.

Lossy Decomposition

A lossy decomposition cannot reconstruct the original relation accurately. Joining the pieces may create false combinations or omit distinctions. This often occurs when tables are split without retaining a key that connects the original facts. More tables do not guarantee a better design; relationships must preserve meaning.

Dependency Preservation

A decomposition preserves dependencies when important rules can be enforced within the resulting tables without repeatedly joining them. Lossless reconstruction and dependency preservation are separate goals, and some advanced decompositions require a tradeoff. Practical designs document any rule that must be enforced through additional constraints, application logic, or controlled processes.

Normalization vs Denormalization

Normalization separates facts to protect integrity. Denormalization deliberately introduces duplication or precomputed data for a measured workload need. Examples include stored summaries, duplicated display labels, materialized views, or dimension models. Denormalization is not the same as leaving an accidental unnormalized table.

Every duplicate needs an owner, refresh rule, failure strategy, and reconciliation method. Begin from a correct normalized model, identify a proven bottleneck, and denormalize only when the operational benefit exceeds consistency and maintenance cost.

Does Normalization Improve Performance?

Normalization can reduce storage and repeated updates, and narrower focused tables may improve some operations. It can also require joins to assemble related data. Whether performance improves depends on query patterns, indexes, data volume, caching, concurrency, and database capabilities.

The primary purpose is integrity and maintainability. Performance should be measured separately. Well-designed joins are routine in relational systems, and avoiding them by duplicating every fact often transfers cost to writes, validation, and incident recovery.

Normalization in OLTP Systems

Online transaction processing systems perform frequent inserts, updates, and short reads under concurrency. Normalized schemas fit this workload because each fact changes in one place and constraints protect relationships. Orders, payments, inventory, accounts, and reservations benefit from clear transaction boundaries and authoritative entities.

Normalization in Analytical Systems

Analytical systems emphasize broad scans, grouping, historical snapshots, and simplified reporting. Dimensional models often use denormalized dimensions and fact tables to support these patterns. Source systems can remain normalized while data pipelines create analytical representations. Different workloads justify different physical models without changing the underlying business meaning.

Employee Management Example

Separate employees, departments, job roles, office locations, and employee-role assignments. Department name belongs to departments, while an employee's department identifier represents membership. Time-varying job history may require dated assignment rows rather than overwriting one role column.

E-Commerce Example

Customers, products, orders, order items, addresses, payments, and shipments are distinct entities. Product current price belongs to products, while the sale price belongs to an order item because it records a historical transaction. Shipping address snapshots may intentionally preserve what was used at purchase time even if the customer's profile later changes.

Banking Example

Customers, accounts, account ownership, transactions, and branches should have explicit keys and relationships. Joint accounts require an ownership association rather than extra customer columns. Transaction history should record immutable financial events, while account balances may be derived or maintained under strict reconciliation rules.

School Management Example

Students, teachers, courses, sections, enrollments, and classrooms represent different subjects. An enrollment connects a student to a section and stores relationship facts such as enrollment date or grade. Teacher contact data does not belong in every student or section row.

Software Testing Management Example

Projects, test cases, test runs, environments, results, and defects have different lifecycles. A test case can execute in many runs, and one run contains many results. Keeping result status on the run-case association preserves history instead of overwriting the test case definition.

Course Platform Example

Users, courses, lessons, instructors, enrollments, and progress should be separated. A course can have multiple instructors and a user can enroll in multiple courses, so association tables model the many-to-many relationships. Progress belongs to the user-lesson or enrollment context, not to the lesson definition.

Inventory Example

Products, warehouses, stock balances, suppliers, purchase orders, and stock movements describe different facts. Quantity by warehouse belongs to a product-warehouse relation. Movement rows record changes over time and support auditability, while a current balance may be maintained under controlled transaction rules.

Hospital Example

Patients, clinicians, appointments, encounters, diagnoses, prescriptions, and facilities require separate ownership and careful history. Repeating clinician details in every appointment creates update anomalies. Clinical records often need immutable historical context and security rules beyond basic normalization.

Common Normalization Mistakes

  • Splitting tables mechanically without identifying business dependencies.
  • Assuming every repeated value is harmful redundancy.
  • Using names or mutable codes as keys without analyzing stability.
  • Ignoring candidate keys after choosing a surrogate primary key.
  • Storing comma-separated lists or numbered repeating columns.
  • Mixing facts at different grains in one table.
  • Decomposing without proving lossless reconstruction.
  • Removing historical transaction values because similar current values exist elsewhere.
  • Denormalizing before measuring a real performance problem.
  • Relying on application code instead of available database constraints.

Best Practices

Begin with business rules, entities, identifiers, relationships, cardinalities, optionality, and attribute meaning. Write important functional dependencies explicitly and identify all candidate keys, not only the chosen primary key. Normalize to a level that prevents observed anomalies and fits the transactional model.

Use foreign keys, unique constraints, checks, and appropriate nullability to enforce the design. Review decomposition with realistic insert, update, delete, and reporting scenarios. Document intentional denormalization, historical snapshots, derived values, and rules enforced outside the database. Test performance with representative data before changing the logical model for speed.

Interview Questions and Answers

What is normalization?

It is the process of organizing relational data according to keys and dependencies to reduce unnecessary redundancy and prevent modification anomalies.

What are the three main anomalies?

Insertion anomalies prevent independent facts from being added, update anomalies require repeated coordinated changes, and deletion anomalies remove unrelated facts accidentally.

What is a functional dependency?

It is a rule in which one attribute set determines another for every valid database state.

Does normalization always improve query speed?

No. Its primary goals are integrity and maintainability. Runtime performance depends on workload, indexes, data size, plans, and infrastructure.

What is lossless decomposition?

It is a split that allows the original valid information to be reconstructed by joining the resulting relations without spurious or missing rows.

Practical Exercises

  1. List the dependencies in an employee-department table.
  2. Identify insertion, update, and deletion anomalies in a sales spreadsheet.
  3. Convert a comma-separated phone list into a 1NF relationship.
  4. Remove partial dependencies from an order-item relation.
  5. Remove transitive department facts from an employee relation.
  6. Prove whether a proposed decomposition is lossless.
  7. Model course enrollments and instructor assignments.
  8. Document one justified historical snapshot or denormalized value.
  9. Compare query plans before and after a physical optimization without changing logical integrity.

Quick Reference

ObservationDesign questionLikely action
Same descriptive fact repeatsDoes it depend on another entity key?Move it to that entity
One field stores a listIs this a multivalued relationship?Create one related row per value
Non-key fact depends on part of a keyAre grains mixed?Separate the partial dependency
Non-key fact determines anotherDoes another entity own it?Remove the transitive dependency
Split tables create false rowsIs decomposition lossless?Restore the required key relationship
Measured reads need faster reportingCan a controlled derived model help?Evaluate intentional denormalization

Complete Normalization Workflow

  1. Define the business subjects, events, and rules.
  2. List attributes and the meaning and time context of each fact.
  3. Identify candidate keys and relationship cardinalities.
  4. Write functional, multivalued, and important join dependencies.
  5. Remove repeating groups and establish a valid relational structure.
  6. Remove facts that depend on only part of a composite key.
  7. Remove non-key facts that belong to another entity.
  8. Evaluate BCNF and higher-form issues when the dependencies require it.
  9. Prove lossless decomposition and assess dependency preservation.
  10. Add primary, foreign, unique, check, and nullability constraints.
  11. Test realistic inserts, updates, deletes, and historical scenarios.
  12. Measure query performance with representative data and indexes.
  13. Introduce denormalization only for a proven need with ownership and reconciliation.
  14. Document the model and review it as business rules evolve.

Key Takeaway

Normalization organizes relational data so that each fact has a clear owner and depends on the appropriate key. By separating entities and relationships according to functional dependencies, a database avoids unnecessary duplication and reduces insertion, update, and deletion anomalies. Normal forms provide a structured vocabulary for evaluating increasingly complex dependency problems.

The strongest design is not the one with the most tables. It is the one that preserves meaning, supports valid operations, enforces integrity, and remains understandable as the system grows. Start with a normalized logical model, validate lossless relationships and constraints, then optimize physical access or introduce controlled denormalization only when a measured workload justifies the tradeoff.