We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningFoundation · Database Fundamentals
Organizing database tables to reduce redundancy by splitting data into related tables connected by foreign keys. Follows normal forms (1NF, 2NF, 3NF).
In short
Normalization is the process of organizing a relational database into multiple tables so each fact is stored exactly once, splitting data into related tables connected by foreign keys to remove redundancy and prevent update anomalies. It follows a series of rules called normal forms, the most common being first, second, and third normal form (1NF, 2NF, 3NF).
In short
Normalization is the process of organizing a relational database into multiple tables so each fact is stored exactly once, splitting data into related tables connected by foreign keys to remove redundancy and prevent update anomalies. It follows a series of rules called normal forms, the most common being first, second, and third normal form (1NF, 2NF, 3NF).
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Normalization across a run of lessons, not one page. These 4 alone are about 83 minutes of step-by-step reading, every one with a quiz.
Renaming one category rewrote 200,000 rows in a flat table and 1 in a normalised one. A partial update left the flat table holding two answers to one question, and the join it costs measured 1.34 times.
Vertical partitioning splits a wide table into hot and cold columns. Shows a users table before and after, and compares it to horizontal partitioning.
Convert data from one format, structure, or representation to another, the glue between incompatible systems.
A precomputed aggregate answered in 0.024 ms what took 107.729 ms to compute, 4,489 times. Maintaining it cost 3.4 times on every insert, and doing the maintenance the obvious way lost 391 of 480 concurrent increments.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Database Normalizationpay once,
yours for life
When you store data in a table, it is easy to repeat the same fact in many rows. Imagine an orders table where every row also carries the customer's name, email, and shipping address. If one customer places 50 orders, their email is copied 50 times. When that customer changes their email, you have to update all 50 rows, and if you miss one, your data now disagrees with itself. Normalization is the discipline of designing tables so each piece of information lives in one place.
The way you do this is to split a wide table into smaller tables linked by foreign keys. The customer's name and email move into a customers table with one row per customer. The orders table keeps only a customer_id that points back to that single row. Now the email exists once. Change it there and every order automatically sees the new value, because the order never stored a copy in the first place.
Normalization was defined by Edgar Codd, the inventor of the relational model, in the early 1970s. He laid out the normal forms as a checklist: each form removes a specific class of redundancy. A table that satisfies third normal form has no repeating groups, every non-key column depends on the whole primary key, and no non-key column depends on another non-key column.
First normal form (1NF) says every column holds a single atomic value, not a list. A column called phone_numbers containing "555-1234, 555-5678" violates 1NF. You split those into separate rows in a phones table so each phone number gets its own row.
Second normal form (2NF) applies when a table has a composite primary key made of more than one column. It requires that every non-key column depend on the entire key, not just part of it. If a table keyed on (order_id, product_id) stores the product's name, the name depends only on product_id, so it belongs in a separate products table.
Third normal form (3NF) removes transitive dependencies, where a non-key column depends on another non-key column. If an employees table stores department_id and also department_name, the name depends on the department_id, not on the employee. You move department_name into a departments table. There are stricter forms beyond this (BCNF, 4NF, 5NF), but most production schemas aim for 3NF because it removes nearly all anomalies while staying practical to query.
Normalization shines in systems that write and update data constantly, which is most transactional applications: banking, inventory, order management, user accounts. Because each fact is stored once, updates are cheap and the database cannot drift into an inconsistent state. Storage is also smaller because you stop duplicating long strings across millions of rows.
The cost shows up at read time. To display an order with its customer name and product names, the database must join the orders, customers, and products tables back together. Joins are not free. A query that touches five normalized tables does more work than reading one wide table where everything is already side by side.
This is why high-traffic read paths often deliberately reverse the process. Denormalization copies some columns back into a table, or precomputes a joined result, to avoid joins on the hot path. The trade-off is the classic one: normalized schemas optimize for correctness and write efficiency, denormalized schemas optimize for read speed at the price of duplicated data you must keep in sync. Most teams normalize first to keep data correct, then denormalize narrowly where profiling proves a join is too slow.
Say you run an e-commerce store. A naive design puts everything in one orders table: order_id, customer_name, customer_email, customer_address, product_name, product_price, quantity. A customer with three orders has their name and address written three times. A product that appears in 10,000 orders has its price copied 10,000 times. Raise that price and you face 10,000 updates, with a real chance of leaving some rows stale.
Normalized to 3NF, this becomes four tables. customers holds id, name, email, address. products holds id, name, price. orders holds id, customer_id, created_at. order_items holds order_id, product_id, quantity. Now a customer's address lives in one row, a product's price lives in one row, and a price change is a single UPDATE.
This is exactly how the schema behind a tool like an online store or a banking ledger is laid out. Postgres and MySQL are both designed around this style. To show an order on screen you join the four tables, and the database engine, with proper indexes on the foreign keys, does that quickly for typical order sizes.
PostgreSQL
The reference relational database for normalized schemas; foreign keys, joins, and constraints enforce that each fact lives in one table.
MySQL
Powers countless normalized transactional apps; banking, e-commerce, and CMS schemas typically target 3NF before any denormalization.
Stripe
Stores customers, charges, and subscriptions in separate related tables so a customer's details are recorded once and referenced by every charge.
Shopify
Models products, variants, orders, and line items as separate tables joined by foreign keys, so a price change updates one row, not every historical order.
Normalization splits data into multiple related tables so each fact is stored once, which keeps writes cheap and prevents inconsistency. Denormalization deliberately duplicates or precomputes data back into fewer tables to avoid joins and speed up reads, at the cost of having to keep the copies in sync. Teams usually normalize first, then denormalize narrowly where a read path is proven too slow.
1NF means every column holds a single value, not a list. 2NF means every non-key column depends on the whole primary key, not just part of a composite key. 3NF means no non-key column depends on another non-key column. Most production schemas aim for 3NF because it removes almost all redundancy while staying easy to query.
For transactional systems that update data often, yes, because it keeps data correct and updates cheap. But it adds joins at read time. For read-heavy analytics, reporting dashboards, or hot API endpoints, controlled denormalization is common and intentional. The right answer depends on whether your workload is write-heavy or read-heavy.
Usually the opposite. Document stores like MongoDB and key-value stores like DynamoDB favor denormalized, embedded data so a single read returns everything an application screen needs without joins, since those databases either lack joins or make them expensive. The redundancy is accepted in exchange for fast, scalable reads.
An update anomaly happens when the same fact is stored in many rows and an update misses some of them, leaving the data contradicting itself. For example, if a customer's email is copied across 50 order rows and you only update 49, the database now holds two different emails for one person. Normalization prevents this by storing the email exactly once.
Vertical Partitioning
Vertical partitioning splits a wide table into hot and cold columns. Shows a users table before and after, and compares it to horizontal partitioning.
Data Transformation
Convert data from one format, structure, or representation to another, the glue between incompatible systems
Database Denormalization
A precomputed aggregate answered in 0.024 ms what took 107.729 ms to compute, 4,489 times. Maintaining it cost 3.4 times on every insert, and doing the maintenance the obvious way lost 391 of 480 concurrent increments
Denormalization
Intentionally adding redundant data to database tables to speed up read queries by avoiding expensive joins. Trades storage and write complexity for read performance.
SQL
Structured Query Language for managing relational databases. Tables, rows, columns, and powerful joins to query related data.
Foreign Key
A column in one table that references the primary key of another table, enforcing referential integrity between related tables.
Database
An organized collection of data that can be easily accessed, managed, and updated. The backbone of almost every application.
NoSQL
Databases that don't use traditional table-based relational models. Includes document stores, key-value, graph, and column-family databases.
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
This page explains the idea. The "Database Normalization" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Database Normalization lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Normalization in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
