OK.
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 many it's a little bit easier to get through shorter but also it's more
common and it's more central in my opinion to most of the development that that I've done and that I
see other people doing.
Not to discount many too 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 and 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 too 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.
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 you know our registration data when a customer signs up maybe a last active date that
we're storing or date time password somehow we need to store that.
There's I don't know there's so many other things phone number.
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 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 ninety nine point nine nine or twenty point
five zero.
So that's going to be a decimal.
So we're going to have first and last name and email which should probably be Vardar as 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 e-mail for customers and then order date and amount.
So we have you know Boy George whose e-mail is George a gmail dot com.
He was a very early user of Gmail and then we have the order date 20:16 to 10.
And that order was ninety nine dollars and 99 cents.
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.
OK it's Boy George which I know was not his first name but just let's go with it.
And then George had gmail again and then a different date.
This one was later in 2017 and then a different price or different amount.
So this store is the information that we talked about when needed first last e-mail and then date of
the purchase price of the order.
And so that's working.
Same thing with George Michael who has GM at gmail dot com bought something in 2014 that was $800 and
67 cents.
And then George Michael Sam email Bazzani in 2050 and that was 12 dollars and 50 cents.
OK.
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 steel.
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 dot com.
Also early adopter of Gmail and we have blue steel blue at gmail dot 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 not.
So this technically represents that fact that Boy George and George Michael have two orders each.
And David Bowie and you 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.
George George George Michael and if somebody you know 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 you know there are times on the site where you don't care about orders or
you just care about customers.
Let's say when a customer is signing in.
All you need is to take their e-mail 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 duplications.
It's also that sometimes it's better to keep your data separate almost every time.
In Leicester always accessing your data together like if the only time we're ever talking about customers
is when we're talking about orders then OK 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's the simplest approach.
This is how we express 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 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 is 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 four orders and four customers from back here and I split it up into separate tables and you
can see it 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 board Ga-Ga G-mail ID one George Michael GMG mail id to David Bowie David at gmail dot com
ID 3 and then blue steel blue at gmail dot com idea for.
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 the idea of one meeting.
Boy George so Boy George placed an order on 2016 for ninety nine dollars.
Ninety nine cents.
Here's another customer I.D. one telling us that this entire row here this order is related to Boy George
again.
So he placed an order for 35 50 on 20:17 and then we have George Michael customer ID to match this idea
here.
And he bought something for $800 67 cents in 2014 and something in 2015 for twelve dollars and fifty
cents 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 Knolles.
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 order and 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 key word we can type when we're defining a schema we can say and what we've been saying
a lot is ID space and space autoincrement and primary key and the effect of that what a primary key
means is that some particular column in our case is customer ID is always unique.
So we only have one customer with the idea 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 5 and to 6 to 7.
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 the unique way of referencing an order.
Now this is a bit extreme but it's possible that we would have to duplicate rows here where basically
an order was placed with 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 the user purposely bought the same thing twice they bought it once and then they realize 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 is 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 is referring to this idea
of the customers.
So primary key primary key foreign key customers does not have a foreign key and it is 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 foreign key.
Will 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 dont have a customer with ID of 20 we mean.
I mean most likely we dont want that to happen.
Right.
We would want to bounce that back and say hey this is a problem there is not a valid customer with that
ID.
And thats what foreign key when we use that in our schema definition it will enforce that.
Otherwise if we dont say that its a foreign key explicitly we could have customer IDs you know all over
the place that don't actually exist.
OK.
So this is the schema that we've defined.
Well this is in the actual scheme of this is that the basics of what we want our schema to look like.
Now we're going to implement it in the next video.