First Normal Form (1NF) is the starting point of database normalization, and it puts strict constraints on your table structure. A table is in 1NF if:
- All data is atomic. Every value in a table cell should be indivisible. Say goodbye to "lists in a single cell"! That's a big no-no in a database.
- Each row is unique. That means your table should have a Primary Key or some unique identifier.
- No repeating groups of data. Values for the same entity shouldn't be stored in the same column.
To put it simply, imagine your database table is your room, and atomic values are separate items: a lamp, a desk, a book. If your room is a mess (like, everything is just piled up), you can't find the lamp or quickly check if you have a spare desk. Normalization helps you "put everything on its shelf."
Example of a 1NF Violation
Let's say we have a students table where we store info about the courses they attend:
| student_id | name | courses |
|---|---|---|
| 1 | Maria | "Mathematics, Physics" |
| 2 | Rob | "Biology, Chemistry" |
What's wrong with this structure? Courses (the courses column) are written as a comma-separated list in a single cell. That means if you want to find all students who study physics, your query turns into a nightmare: you'll have to do some gnarly text transformations. And if one student wants to drop physics from their list—boom, new headache. These values aren't atomic, which breaks the main 1NF rule.
How to Bring a Table to 1NF?
To fix this, we'll split the data into separate rows, so each table value is atomic:
| student_id | name | course |
|---|---|---|
| 1 | Maria | Mathematics |
| 1 | Maria | Physics |
| 2 | Rob | Biology |
| 2 | Rob | Chemistry |
Now we're good. We transformed our table so every cell value is indivisible. That's what 1NF is all about.
Detailed Example of a 1NF Violation and Fix
Let's say we have an online store orders table:
| order_id | customer_name | items |
|---|---|---|
| 1001 | Otto Lin | "Laptop, Mouse, Keyboard" |
| 1002 | Anna Song | "Smartphone, Case" |
Obviously, items here contains several values separated by commas, which breaks 1NF.
To bring the data to 1NF, we'll store one row for each item in the order:
| order_id | customer_name | item |
|---|---|---|
| 1001 | Otto Lin | Laptop |
| 1001 | Otto Lin | Mouse |
| 1001 | Otto Lin | Keyboard |
| 1002 | Anna Song | Smartphone |
| 1002 | Anna Song | Case |
Now the table structure matches 1NF principles. Each order and item is a separate row, and the values are atomic.
Adding a Primary Key
After transforming the table, it's important to add a unique identifier (primary key) for each row to guarantee uniqueness. In the example above, you could use a combo of order_id and item as a composite primary key. But in real life, people usually add a separate id field.
| id | order_id | customer_name | item |
|---|---|---|---|
| 1 | 1001 | Otto Lin | Laptop |
| 2 | 1001 | Otto Lin | Mouse |
| 3 | 1001 | Otto Lin | Keyboard |
| 4 | 1002 | Anna Song | Smartphone |
| 5 | 1002 | Anna Song | Case |
Practical Task
You have a students table with subjects they're studying, all packed into a single cell:
| student_id | name | subjects |
|---|---|---|
| 1 | Polly | "Mathematics, Chemistry" |
| 2 | Peter | "Physics, Computer Science" |
Transform the table so it matches 1NF.
After the transformation, the table should look like this:
| student_id | name | subject |
|---|---|---|
| 1 | Polly | Mathematics |
| 1 | Polly | Chemistry |
| 2 | Peter | Physics |
| 2 | Peter | Computer Science |
Most Common Mistakes When Working with 1NF
When you're working with a database, 1NF violations usually show up in these situations:
- Storing lists or arrays right in the table. This is the most common mistake.
- No unique identifier for rows (primary key). This makes your table vulnerable to duplicate data.
- Using multiple columns to store the same kind of info. For example, "course_1", "course_2", "course_3"—instead of the right structure.
Keep these things in mind, and your database will be 1NF-compliant.
Practical Use of 1NF
In real-world projects, 1NF is a big deal. For example:
- In CRM apps, client data and their actions should be atomic. That makes analysis and searching way easier.
- In online stores, 1NF is used to efficiently store info about orders, products, and customers.
- In banking systems, data about clients, their accounts, and transactions must be atomic to avoid mixing up different operations.
Sticking to 1NF principles helps you design databases that can handle heavy loads and are still easy to use. All good, but now you get some data duplication. That's why we move on to Second Normal Form (2NF), where you'll see how to handle partial dependencies in tables.
Why is it important to follow First Normal Form (1NF)? Imagine you're storing data in a table where a single cell can have several values—like a list of products a customer ordered. In that form, it's tough to work with the data: trying to find everyone who ordered a "Keyboard" is a pain. And if you need to change or delete part of the info, it's easy to mess up. When data is stored atomically—meaning each field has just one value—working with it is way more reliable and clear. Plus, these tables are easier to scale, update, and refactor when you need to.
GO TO FULL VERSION