MySQL Database Design

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.

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.

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.

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.

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.

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."

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.

Many-to-many is one of the more confusing relationships to understand - at least initially. The most common example consists of posts and tags. Notice that any one post may have multiple tags, and any one tag may be associated with multiple posts. In situations such as this, a connection may be created between the two tables by constructing a linking table. Let's review the workflow.

Database indexing can be a fairly confusing topic - at least initially. Over the next few episodes, let's review a series of examples that illustrate precisely why, when, and how you should apply an index.

Now that you have a basic understanding of indexing, let's review another example. If you're not careful, even with an index, a slight tweak to your query has the potential to skyrocket the time it takes to execute.

Let's begin working through some basic exercises. Using our blogging system, imagine that your boss wants to give a bonus to the ten most popular writers. Let's write a SQL query to determine who these people are.