Okay.
So we talked about the three kind of common relationship classifications.
And as I mentioned, we're focusing on one to many first, and we're doing that for a couple of reasons.
One is that compared to many to many, it's a little bit easier to get through shorter.
But also it's more common and it's more essential, in my opinion, to most of the development that
that I've done and that I see other people doing not to discount many to many, but we're going to get
there.
We're starting with one too many.
So we're going to start with a classic example, customers and orders.
Or this could be users and orders or users in carts, whatever the transaction is, but we're going
to call it customers and orders.
So two tables and this is a one to many relationship, as we talked about earlier.
So let's say we have a customer, President Bill Clinton, that's our customer.
And he can place as many orders as he'd like.
He can have many orders, but each of those orders is associated with exactly one user, Bill Clinton.
President Bill Clinton.
Excuse me.
There is no way to have two people associated with one order.
So it's a one to many customers have many orders.
Orders have one customer associated with them.
So let's talk about for our case what data we want to store.
And it's going to be very simple.
I don't want to clutter it with a whole bunch of other pieces of information.
We're just going to focus on the basics.
So a first and last name for customers and an email.
Now, in a real web app, we'd want a lot more information.
Probably we want to create a registration date when a customer signs up, maybe a last active date that
we're storing our date time password, somehow we need to store that.
There's I don't know, there's so many other things phone number if we're trying to do two factor authentication,
lots of things.
So first and last name and email.
So three things per customer.
But then we also for orders, we need to store the date that an order was made.
So it doesn't matter for our example if it's date or date time, but we'll keep it simple with date.
So 2017, slash 11, slash 12 or something.
And then the price of the order, the total amount, let's say 99.99 or 20.50.
So that's going to be a decimal.
So we're going to have first and last name an email, which should probably be our chars, the date
of the purchase, which will be a date time and the price of the order which will be a decimal.
That's the easy part.
The next question is how do we store that?
How do we represent it?
And we could do it this way, which I want to make clear.
I'm not saying this is a good way of doing it, but you could technically get away with doing it in
one giant table.
But there's problems.
But let's first just walk through how it works.
So we have first name and last name and email for customers and then order, date and amount.
So we have Boy George whose email is George at gmail.com.
He was a very early user of Gmail.
And then we have the order date 2016 210 and that order was $99.99.
So this is capturing information for one order.
But then George or Boy George placed another order and we have duplicated information because we're
storing it's Boy George, which I know is not his first name, but just let's go with it.
And then George at Gmail again and then a different date.
This one was later in 2017 and then a different price or a different amount.
So this stores the information that we talked about.
We needed first last email and then data, the purchase and price of the order.
And so that's working.
Same thing with George Michael who has GM at gmail.com, bought something in 2014 that was $800.67.
And then George Michael, same email about something in 2015 that was $12.50.
So already you should be noticing we have some a lot of duplication, right.
Even though we are storing the information that we're supposed to store, there's a lot of duplicated
data and then we run into these two.
David Bowie and Blue Steal My Cat.
There's problems because they haven't placed orders yet.
So we have their customer information.
We have David Bowie, David at gmail.com, also early adopter of Gmail and we have blue, steel, blue
at gmail.com and they haven't placed orders, but they still have order, date and amount as columns
and they just have no value.
It's null.
So this technically represents that fact that boy George and George Michael have two orders each and
David Bowie and Blue still have no orders.
So that's really not a great way of doing it.
Yes, it works.
But we're not only duplicating a lot of things, right?
Boy, George, George, Michael.
And if somebody shopped a lot, we would have tons of duplicated information, which is just a waste.
But then also we run into problems where we have users who maybe haven't ordered anything yet and they
just signed up for the site.
There's no reason to have anything to do with orders until they actually place an order.
So it's much better to keep our data separated because if we ever need to work with just customers on
their own, which is happens, there are times on a site where you don't care about orders, you just
care about customers.
Let's say when a customer is signing in, all that you need is to take their email and check it and
take a password and check it against the password field.
That has nothing to do with orders.
So why would we need to go through a giant customer orders table?
And that's just a really silly example.
But what I'm trying to show is that it's not just about the duplication.
It's also that sometimes it's better to keep your data separate almost every time unless you're always
accessing your data together.
Like if the only time we're ever talking about customers is when we're talking about orders, then maybe
you could put them together.
But then we still have this problem where we have people who haven't placed orders or we have duplicated
data.
So this is a bad idea.
Not a good idea, aka bad idea.
So what do we do?
Well, here is the simplest approach.
This is how we express a one to many relationship.
And it looks like this.
So we have two tables, customers and orders, and customers have an ID field and then a first name,
last name and email and then orders have an ID field and then the date, the amount.
And then this is the important this is the crux of everything.
They have a field called it doesn't matter necessarily what it's called, but ours is called customer
ID and it's a reference to the customers table.
So whatever customer ID is in a given order is going to correspond to an actual customer who placed
it.
So let's dive into it a bit more.
Here's an example.
So I took the exact same data we had for orders and for customers from back here, and I split it up
into separate tables.
And you can see let's just talk about customers first.
We have a customers table with an ID, a first name, last name and an email.
So we have Boy George.
George at Gmail ID one, George Michael at Gmail ID two, David Bowie, David at gmail.com, ID three
and then blue steal blue at gmail.com, ID of four.
These are all unique, corresponding to exactly one user or one customer.
And then we have our orders table.
So there's an ID there, order ID.
And then the date that an order was placed.
And then the amount.
And then the most important part.
Well, arguably the most important part, definitely the crux of the relationship is customer ID.
So in this case, it's one which is pointing to this customer with idea of one meaning.
Boy George.
So Boy George placed an order in 2016 for $99.99.
Here's another customer ID one telling us that this entire row here, this order is related to Boy George
again.
So he placed an order for 3550 on 2017.
And then we have George Michael.
Customer ID two matches this ID here, and he bought something for $800.67 in 2014 and something in
2015 for $12.50, which is the exact same data we had represented here.
And then notice that we have users like David Bowie or customers excuse me, David Bowie and Blue Steel
who haven't bought anything.
And there's no extra data, there's no NOLs.
We have the minimum information that we need.
We're not storing anything that we don't need.
So we don't have extra orders that are created because they haven't created orders.
We have separate tables.
So this is the classic way of structuring a one to many relationship.
And along with that, there are two terms we need to point out, which we have briefly discussed.
And the first one is primary key.
So remember this keyword we can type when we're defining a schema, we can say and what we've been saying
a lot is ID space, INT space, auto increment and primary key.
And the effect of that, what a primary key means is that some particular column in our case, this
customer ID is always unique.
So we only have one customer with ID of one.
And only one customer, the idea of two and three and four.
And that's really important because if we're referencing it somewhere else, like over here, if there
are two different customers that have ID of one, then this is useless because which one does it refer
to?
So it has to be unique.
And then that's where the auto increment comes in, right?
Where it will make it unique.
Every time we insert a new customer, it will automatically increment this to five and to six and to
seven.
So there is no possible way we could have duplicate customer IDs.
And then also here is order ID, which is also a primary key.
It is the way, that unique way of referencing an order.
Now, this is a bit extreme, but it's possible that we would have two duplicate rows here where basically
an order was placed for the same amount at the same day by the same person.
It's possible, and we would need to refer to them separately, though we don't want our database just
to combine them.
Maybe a user purposefully bought the same thing twice.
They bought it once and then they realized, oh shoot, I really should have bought two.
So then they buy it again.
Same amount, same day, same customer ID.
Well, the only thing that keeps those orders unique is our order ID.
So again, these two are primary keys.
They are the primary way of referring to rows in this table.
It's what makes them or it's not the only thing that makes them unique, but it is guaranteed to be
unique.
And then that brings us to foreign keys.
And foreign keys are references to another table within a given table.
So in this case, in our orders table, customer ID is a foreign key because it referring to this ID
of the customers.
So primary key primary key foreign key customers does not have a foreign key in it.
It's not referring to any external table, but orders has a foreign key and that terminology is important.
We'll come back to it a lot, primary key and foreign key.
And we'll also show you how you actually say in your schema definition that something is a foreign key.
And the reason you would do that is to enforce that whatever customer ID is in here is an existing customer
ID over here.
Because we might.
What if we inserted something an order in an amount with customer ID of 20 and we don't have a customer
with ID of 20?
We may not.
I mean, most likely we don't want that to happen, right?
We would want to bounce that back and say, hey, this is a problem.
There's not a valid customer with that ID and that's what foreign key, when we use that in our schema
definition, it will enforce that.
Otherwise, if we don't say that it's a foreign key explicitly, we could have customer IDs all over
the place that don't actually exist.
So this is the schema that we've defined well, this isn't the actual schema, but this is the basics
of what we want our schema to look like.
Now we're going to implement it in the next video.