We’ll touch on one last topic and call it a day with MySQL. The last topic is normalization. What does it mean to normalize a database? Let’s say that you have a table that stores your customers and one of the fields there is customer_type, such as direct or distributor, those two words are going to be entered thousands of times unnecessarily. Why not have a separate table for them and just specify which number they represent? For example, if id of 1 is set to distributor, then if we look at a customer’s customer_type and see that the value is set to 1, then we’ll automatically know that the customer is a distributor.

Why do we even care?

It saves space, helps with eliminating data redundancy, and it makes it easier to manage. The space/redundancy is easy to visualize. What takes more room? Ten-thousand distributor entries, or ten-thousand 1 entries?

We also standardize what can be added? What are our options in other words? If we let users add whatever they want, they will make sure that they add every variation of distributor that you can think of:

• Distributor
• distributor
• dist
• dis
• Dist
• Distrib

Do I need to keep going? And the spelling mistakes: don’t forget about the spelling mistakes.

Disadavantages of Normalization

There are a few issues. Data is spread over multiple tables so it’s not as easy to see it. For example, you may see in the users table:

• first_name = Dino
• last_name = Cajic
• email = dino@example.com
• customer_type_id = 1

And in the customer_types table you may see:

• id = 1
• type = distributor

That means that you’ll have to visit the customer_types table each time to see what 1 means for customer_type_id.

Pivot Tables

If it’s not complicated enough, there are also pivot tables. Pivot tables will take the id from two tables and show them together. In the example above, we had a customer_type_id in the users table. That means that we’re assuming that each of our users is a customer and that might not be the case. What if some were employees and not customers?

What would the structure look like then?

For users,

• id
• first_name = Dino
• last_name = Cajic
• email = dino@example.com

For customer_types,

• id
• type

For customers,

• user_id
• customer_type_id

This is probably a poor example, but you get the gist. When you visit your customers table, you would see:

• user_id = 1
• customer_type_id = 1

What does that mean? Well, the user_id = 1 points to the users record where the id = 1. In this case, it’s for Dino | Cajic | dino@example.com. The customer_type_id points to the customer_types record where the id = 1, which is set to distributor. We know all of the information about that customer: Dino | Cajic | dino@example.com | distributor.

Creating Our Tables

I’ll be using phpMyAdmin to create these three tables: just quick and dirty. If you need some assistance in creating tables, here’s my previous article on it.

The users and customers tables will be empty. The customer_types table will be pre-populated with two records: 1 = distributor and 2 = direct.

Let’s look at the code to insert a user/customer into the database.

<?php

$mysqli = new mysqli(
    "localhost",
    "admin",
    "E9bIyRDwzidT",
    "dino_test_db"
);

if ( $mysqli->connect_error ) {
    die( $mysqli->connect_error );
}

$sql = "INSERT INTO users (first_name, last_name, email) 
                VALUES (?, ?, ?)";

$statement = $mysqli->prepare( $sql );

$first_name = "Dino";
$last_name  = "Cajic";
$email      = "dino@example.com";

$statement->bind_param("sss", $first_name, $last_name, $email);
$statement->execute();

$statement->close();
$mysqli->close();

We’ve already looked at this code. We prepare the insert statement and we insert it into the database. We now have a new record added inside of the users table. What we need for customers is the user_id and the customer_type_id.

The steps are:

• Insert the user into the users table.
• Retrieve the id that was just added from the users table.
• Retrieve the specific customer type id from the customer_types table.
• Add those two id’s into the customers table.

We just did part one. Next is getting the insert_id. That’s a lot simpler than you think. The $statement object has an insert_id property that we can retrieve right after insertion. We’ll add that piece to our code above.

<?php

// ...

$statement->bind_param("sss", $first_name, $last_name, $email);
$statement->execute();

$user_id = $statement->insert_id;

$statement->close();
$mysqli->close();

Great. Next we need to decide what type of customer this user is. For the sake of simplicity, I’ll say that they’re a distributor. Let’s grab the id from the customer_types table where the type is a distributor.

<?php
// ...

// Insert User
// ...

$user_id = $statement->insert_id;

$statement->close();

// Get customer_type_id
$sql = "SELECT id FROM customer_types WHERE type = 'distributor'";

$results = $mysqli->query( $sql );
$row = $results->fetch_assoc();
$customer_type_id = $row['id'];

$mysqli->close();

Great. We now have both the user_id and the customer_type_id. We just need to insert those two fields into the customers table.

<?php
<?php

//...

// Get customer_type_id
$sql = "SELECT id FROM customer_types WHERE type = 'distributor'";

$results = $mysqli->query( $sql );
$row = $results->fetch_assoc();
$customer_type_id = $row['id'];

// Create Customer
$sql = "INSERT INTO customers (user_id, customer_type_id) 
               VALUES (?, ?)";

$statement = $mysqli->prepare( $sql );

$statement->bind_param("ii", $user_id, $customer_type_id);
$statement->execute();

$statement->close();
$mysqli->close();

And that’s it. We can check our tables and see if it worked.

The full code is below.

<?php

$mysqli = new mysqli(
    "localhost",
    "admin",
    "E9bIyRDwzidT",
    "dino_test_db"
);

if ( $mysqli->connect_error ) {
    die( $mysqli->connect_error );
}

// Insert User
$sql = "INSERT INTO users (first_name, last_name, email) 
                VALUES (?, ?, ?)";

$statement = $mysqli->prepare( $sql );

$first_name = "Dino";
$last_name  = "Cajic";
$email      = "dino@example.com";

$statement->bind_param("sss", $first_name, $last_name, $email);
$statement->execute();

$user_id = $statement->insert_id;

$statement->close();

// Get customer_type_id
$sql = "SELECT id FROM customer_types WHERE type = 'distributor'";

$results = $mysqli->query( $sql );
$row = $results->fetch_assoc();
$customer_type_id = $row['id'];

// Create Customer
$sql = "INSERT INTO customers (user_id, customer_type_id) 
               VALUES (?, ?)";

$statement = $mysqli->prepare( $sql );

$statement->bind_param("ii", $user_id, $customer_type_id);
$statement->execute();

$statement->close();
$mysqli->close();

Retrieving the Content

How do we retrieve this content? I know you know that JOIN’s were right around the corner. We’ll use a join. We have to join each table. Let’s take a look at the code first.

<?php

$mysqli = new mysqli(
    "localhost",
    "admin",
    "E9bIyRDwzidT",
    "dino_test_db"
);

if ( $mysqli->connect_error ) {
    die( $mysqli->connect_error );
}

$sql = "SELECT 
            users.first_name, 
            users.last_name, 
            users.email,
            customer_types.type
        FROM users 
        JOIN customers ON users.id = customers.user_id
        JOIN customer_types ON customer_types.id = customers.customer_type_id";

$results = $mysqli->query( $sql );

if ( $results->num_rows > 0 ) {
    while( $row = $results->fetch_assoc() ) {
        var_dump($row);
    }
} else {
    echo "No results";
}

$mysqli->close();

More specifically, let’s look at the SQL statement. We’re retrieving users.first_name, users.last_name, and users.email from the users table. We don’t have to specify users.xxx but to be explicit here, we are. If we didn’t care about the customer’s type, we could eliminate all of the joins. But, we need the customer_types.type. How do we get from users to the customer_types table? We have the customers table that allows us to walk that path.

• We first establish a connection between users and customers with the users id field. That links those two tables together.
• Now, there’s no direct link from users to customer_types, but there is a link from customers to customer_types. The link occurs on the customer_types id.
• Once both connections are established, there’s a direct line from users to customer_types and we can retrieve the type for that specific user.

/app/91 Normalization/TestSelect.php:27:
array (size=4)
  'first_name' => string 'Frank' (length=4)
  'last_name' => string 'Johnson' (length=5)
  'email' => string 'frank@example.com' (length=16)
  'type' => string 'distributor' (length=11)
/app/91 Normalization/TestSelect.php:27:
array (size=4)
  'first_name' => string 'Dino' (length=4)
  'last_name' => string 'Cajic' (length=5)
  'email' => string 'dino@example.com' (length=16)
  'type' => string 'distributor' (length=11)

That’s it for normalization. I highly encourage you to research joins and the differences between left, inner, full, and right joins. Until next time.

https://github.com/dinocajic/php-youtube-tutorials?source=post_page—–7e9a9809311e——————————–