Back Button Tooling
MySQL Database Design
Let's get started!

MySQL Database Design

Developers often dive in without taking the necessary time to learn the fundamentals of the technologies they're using. Basic database design is a perfect example of this, unfortunately. Let's fix this. Step by step, I'll introduce you to the techniques and jargon that you've been meaning to learn for years now.

Intermediate
Difficulty
10 episodes
1:06:12 hrs
Start Series
MySQL Database Design
Let's get started!

  1. Section 1 Setup

    1. The Example MySQL Database

      Run Time 2:44
      Free

      Before we begin, let's import an example MySQL database to toy around with. We'll use the popular Sakila database, available on MySQL's main website.

    2.

  2. Section 2 Keys

    1. Primary and Foreign Keys

      Run Time 6:31

      If a primary key is a unique identifier for a record within a table, then a foreign key is a reference to a primary key on a foreign table.

    2. Foreign Key Constraints

      Run Time 13:09

      A constraint allows you to protect the integrity of your database tables. Imagine an order that is associated with a customer who no longer exists in your system. Luckily, we can guard ourselves against such orphans.

    3. Laravel and Foreign Key Constraints

      Run Time 6:06

      Now that you understand keys a bit more, let's switch over to a fresh Laravel app and review how we can represent these relationships and constraints through Laravel migrations.

    3.

  3. Section 3 Joins

    1. The Difference Between MySQL Join Types

      Run Time 5:35

      If the various MySQL join types confuse you, don't worry: you're not the only one. Inner Join, Outer Join, Left Join, Right Join...what the heck? But, as it turns out, once you understand the basic structure of a join, everything else should quickly fall into place. In this episode, we'll review everything you need to know.

    2. Group Results With Aggregate Functions

      Run Time 7:13

      An excellent way to learn MySQL is to ask yourself: "If this was my domain, what sorts of questions might I ask?" One such question might be, "How many movies has each user rented?" To calculate numbers like this, we'll leverage MySQL aggregate functions and the GROUP BY clause.

    3. Multiple Joins in One Query

      Run Time 7:12

      If necessary, you can join any number of tables as part of your SELECT statement. In this episode, we'll add a second LEFT JOIN clause to the query we wrote in the previous episode. This will also give us the opportunity to discuss subqueries.

    4. Filtering Aggregated Data

      Run Time 5:41

      Here's another question we might ask: "Which movies have been the most profitable?" Once we solve this riddle, we can then answer a second question based on that information: "Of those results, which movies have made at least $200?"

    4.

  4. Section 4 Relationships

    1. One-to-One

      Run Time 7:59
      New

      A "one-to-one" database relationship is incredibly simple to understand. If you can say that a single record from one table is related to a single record from another table, in both directions, then you have a one-to-one relationship. Here are a few examples to get you started: "A person has one social security number." "A subscriber has one profile." "A manager has one team."

    2. One-to-Many

      Run Time 4:02
      New

      A one-to-many relationship is the second easiest to understand. A user may have many posts. A post may have many comments. A team may have many members. In each of these examples, a single record is related to any number of related records from a related table.

    5.
Series still in development robot

*Series still in development. Check back often for updates.

Want us to email you occasionally with Laracasts news?