The First Step to Sanity: Eliminating Repeating Groups in MySQL with 1NF

Database normalization is a crucial concept for anyone working with data. It’s a systematic approach to minimizing data redundancy and improving data integrity. The first and most fundamental step in this process is achieving First Normal Form (1NF). In this article, we’ll break down what 1NF is, why it’s important, and how to apply it in MySQL to eliminate those pesky repeating groups.

What Is 1NF? 🤔

First Normal Form states that each cell in a table must contain a single, atomic value, and there should be no repeating groups of columns. An “atomic value” means a value that can’t be further subdivided. For example, a single VARCHAR column holding a full name "John Doe" isn't atomic if you ever need to query by first name or last name individually. A better approach would be to split it into two separate columns: first_name and last_name.

The more common violation of 1NF, however, is the concept of a “repeating group.” This occurs when a table has multiple columns that are used to store similar data. For instance, imagine a table for a customer’s phone numbers with columns like phone1, phone2, and phone3. This is a classic violation of 1NF. Why? Because the number of phones a customer has can change, requiring you to add new columns, which is a structural change to the database.

Why Bother with 1NF? 💡

Ignoring 1NF can lead to several problems:

• Data Redundancy: Storing multiple phone numbers in separate columns leads to wasted space and makes your data less efficient.
• Update Anomalies: If a customer’s phone number changes, you’d have to figure out which column it’s in to update it. If you have to change more than one, you risk missing a column or making an incorrect update.
• Deletion Anomalies: Deleting a record might inadvertently delete other, unrelated data if you’re not careful.
• Querying Hell: Writing queries to find a specific phone number becomes a nightmare, as you’d have to check every phone column.

The core principle is that each piece of information should be stored in one and only one place.

How to Achieve 1NF in MySQL 🔧

Let’s use a practical example. Imagine we have a table called customers that stores customer information and their phone numbers.

The “Bad” Table (Not in 1NF):

SQL

CREATE TABLE customers_bad (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(255),
  phone1 VARCHAR(20),
  phone2 VARCHAR(20),
  phone3 VARCHAR(20)
);

This table violates 1NF because the phone columns are a repeating group. To fix this, we need to create a new, separate table to handle the phone numbers. This new table will have a foreign key linking it back to the customers table. A foreign key is a column or a set of columns in one table that uniquely identifies a row of another table.

The “Good” Tables (In 1NF):

First, let’s create the customers table, which is now simplified and only holds the customer's core information.

SQL

CREATE TABLE customers_good (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(255)
);

Next, we create a new table, customer_phones, specifically for phone numbers. This table will have a foreign key (customer_id) that references the customer_id in the customers_good table.

SQL

CREATE TABLE customer_phones (
  phone_id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  phone_number VARCHAR(20),
  FOREIGN KEY (customer_id) REFERENCES customers_good(customer_id)
);

Now, let’s see how we’d insert and query data.

Inserting Data:

SQL

-- Insert a customer
INSERT INTO customers_good (customer_id, customer_name) VALUES (1, 'Jane Doe');

-- Insert phone numbers for Jane Doe
INSERT INTO customer_phones (customer_id, phone_number) VALUES (1, '555-1234');
INSERT INTO customer_phones (customer_id, phone_number) VALUES (1, '555-5678');

Querying Data:

To get a customer’s details along with all their phone numbers, we use a JOIN operation.

SQL

SELECT
    c.customer_name,
    cp.phone_number
FROM
    customers_good c
JOIN
    customer_phones cp ON c.customer_id = cp.customer_id
WHERE
    c.customer_id = 1;

This query will return two rows for Jane Doe, one for each phone number, which is exactly what we want.

Final Thoughts 🚀

Achieving First Normal Form is the first and most crucial step in designing a solid, reliable database. By eliminating repeating groups and ensuring each column holds a single, atomic value, you’re setting the foundation for a scalable and maintainable system. It might seem like more work at first, but the long-term benefits in data integrity and ease of use are immeasurable. So, next time you’re designing a table, ask yourself: “Do I have any repeating groups?” If the answer is yes, you know what to do!

Database normalization is a fundamental concept for anyone working with data. It’s a systematic approach to minimizing data redundancy and improving data integrity. The first and most fundamental step in this process is achieving First Normal Form (1NF). In this article, we’ll break down what 1NF is, why it’s important, and how to apply it in PostgreSQL to eliminate those pesky repeating groups.

What Is 1NF? 🤔

First Normal Form states that each cell in a table must contain a single, atomic value, and there should be no repeating groups of columns. An “atomic value” means a value that can’t be further subdivided. For example, a single VARCHAR column holding a full name "John Doe" isn't atomic if you ever need to query by first name or last name individually. A better approach would be to split it into two separate columns: first_name and last_name.

The more common violation of 1NF, however, is the concept of a “repeating group.” This occurs when a table has multiple columns that are used to store similar data. For instance, imagine a table for a customer’s phone numbers with columns like phone1, phone2, and phone3. This is a classic violation of 1NF. Why? Because the number of phones a customer has can change, requiring you to add new columns, which is a structural change to the database.

Why Bother with 1NF? 💡

Ignoring 1NF can lead to several problems:

• Data Redundancy: Storing multiple phone numbers in separate columns leads to wasted space and makes your data less efficient.
• Update Anomalies: If a customer’s phone number changes, you’d have to figure out which column it’s in to update it. If you have to change more than one, you risk missing a column or making an incorrect update.
• Deletion Anomalies: Deleting a record might inadvertently delete other, unrelated data if you’re not careful.
• Querying Hell: Writing queries to find a specific phone number becomes a nightmare, as you’d have to check every phone column.

The core principle is that each piece of information should be stored in one and only one place.

How to Achieve 1NF in PostgreSQL 🔧

Let’s use a practical example. Imagine we have a table called customers that stores customer information and their phone numbers.

The “Bad” Table (Not in 1NF):

SQL

CREATE TABLE customers_bad (
  customer_id SERIAL PRIMARY KEY,
  customer_name VARCHAR(255),
  phone1 VARCHAR(20),
  phone2 VARCHAR(20),
  phone3 VARCHAR(20)
);

This table violates 1NF because the phone columns are a repeating group. To fix this, we need to create a new, separate table to handle the phone numbers. This new table will have a foreign key linking it back to the customers table. A foreign key is a column or a set of columns in one table that uniquely identifies a row of another table.

The “Good” Tables (In 1NF):

First, let’s create the customers table, which is now simplified and only holds the customer's core information. In PostgreSQL, we'll use SERIAL for auto-incrementing integer primary keys.

SQL

CREATE TABLE customers_good (
  customer_id SERIAL PRIMARY KEY,
  customer_name VARCHAR(255)
);

Next, we create a new table, customer_phones, specifically for phone numbers. This table will have a foreign key (customer_id) that references the customer_id in the customers_good table.

SQL

CREATE TABLE customer_phones (
  phone_id SERIAL PRIMARY KEY,
  customer_id INT,
  phone_number VARCHAR(20),
  FOREIGN KEY (customer_id) REFERENCES customers_good(customer_id)
);

Now, let’s see how we’d insert and query data.

Inserting Data:

SQL

-- Insert a customer
INSERT INTO customers_good (customer_name) VALUES ('Jane Doe');

-- Get the last inserted customer_id
SELECT currval('customers_good_customer_id_seq');

-- Insert phone numbers for Jane Doe
INSERT INTO customer_phones (customer_id, phone_number) VALUES (1, '555-1234');
INSERT INTO customer_phones (customer_id, phone_number) VALUES (1, '555-5678');

Querying Data:

To get a customer’s details along with all their phone numbers, we use a JOIN operation.

SQL

SELECT
    c.customer_name,
    cp.phone_number
FROM
    customers_good c
JOIN
    customer_phones cp ON c.customer_id = cp.customer_id
WHERE
    c.customer_id = 1;

This query will return two rows for Jane Doe, one for each phone number, which is exactly what we want.

Final Thoughts 🚀

Achieving First Normal Form is the first and most crucial step in designing a solid, reliable database. By eliminating repeating groups and ensuring each column holds a single, atomic value, you’re setting the foundation for a scalable and maintainable system. It might seem like more work at first, but the long-term benefits in data integrity and ease of use are immeasurable. So, next time you’re designing a table, ask yourself: “Do I have any repeating groups?” If the answer is yes, you know what to do!

For more follow us on Web or Linked CourseCasts.Com