All right.
Welcome to the next section.
So we're continuing on the same similar path talking about relationships talking about joint more complex
data multiple tables and connecting those tables drawing lines between them.
However we're now moving on to our next relationship which is the many to many relationship.
So we saw how to do a one to many which is a pretty standard relationship and it's pretty common.
Many too many is is communist Well maybe not as common but still very very common.
So we're going to see how to do it.
And the reason that I saved it for last is that it's a little bit trickier.
It's not hard at all to scare you away but you'll see there's a little more setup involved.
And then once you've discussed on a high level how many to many relationships typically work we're going
to implement one using some relatively complex data that I spent way too long creating.
And then we'll do some problems together.
So this section will actually serve more as a code along.
So I would encourage you if you don't typically I encourage you to follow along.
We're going to use some relatively complex data like I said and we'll just go through it do a couple
of exercises together and I'm going to use it as a way to illustrate how joints work on a many to many
relationship.
So let's start in this video I just made talking about the many to many relationship.
So we kind of discussed it briefly in the last section but here are a couple of examples.
So books and authors harkening back to that last section where we saw books a book can have multiple
authors.
Right.
There are lots of books that are written by co-authors or teams of you know if it's a collection if
it's an anthology there might be 20 authors in each of those authors can then have multiple books.
So it's a many to many relationship books can have many authors authors can have many books or another
common example is blog post or some sort of content whether it's a Instagram photo a tumbler or whatever
you call it the tumblr posts or just a traditional blog some sort of content and then tags tags.
By that I mean things like sunset picture or there's some really like Van life if you're familiar with
that hash tag or I do a lot of photography so I see a lot of tags that are things like like Masterpiece
landscape photography and then you know insta good if you use Instagram there's all these different
tags that are out there.
And each post can have multiple tasks.
I mean there's a limit sometimes but you know on Instagram you can have 30 hashtags I think it is my
photo.
And then each of those tags can then have as many posts or photos as necessary.
So that's another example.
And the final example I have here is students in classes.
So this is we're talking about like maybe college students or university students where students have
multiple classes.
They picked the classes at the beginning of the semester.
One student can have.
Usually you must have multiple classes unless maybe it's your last semester but you have multiple classes
that you go to that you risk related to.
And then each class is going to have multiple students in it and that's a many to many relationship.
And the one that will be working within this section it's actually not listed here.
So a little different.
And to start off I want you to imagine that we're building a TV show reviewing application like yes
I know we don't need any more of those but imagine something like an Internet Movie Database.
Very very simple version though but we're working with TV shows exclusively.
And the key idea is that we can have someone who signs up as a reviewer.
We can have TV shows that you know let's say some of the ones I'm using.
Fargo the TV show Archer General Hospital.
Any series and a reviewer can then rate any of those series so I can sign up and rate Archer as a 8.5
out of 10.
So to accomplish this relationship a many to many relationship where we're basically associating users
or reviewers we'll call them with TV shows.
We need to actually use three tables.
We use what's known as a join table or a union table.
So we have our series data appear.
So these would be the TV shows and then down here we have our viewers data.
So these would be just names in our case we keep it very simple.
So be it or this will be Chartley or whatever it is.
But the way that they're connected is through a third table through reviews table so this will make
sense and I show you the schema in just a moment but think about it this way a series exist on their
own.
They're just TV shows reviewers exist on their own.
They have nothing to do with a serious out of the box.
It's just someone's name.
But they then are associated through reviews table.
So this reviews table will have information on the TV show that's being reviewed.
And then the reviewer who is doing it as well as a third piece of information which is the rating.
What's than the number the numeric representation of that review.
So without further ado let's take a look at our schema.
We have three tables reviewers over here with an ideal primary key first name and last name.
And of course in a real application we would have more data here right.
We would have email we'd be storing our password in a safe protected way.
We would have things like the registration date may be less active.
All that kind of stuff but to keep it simple here so we can focus on the relationship focus on the joints.
I'm only doing first name and last name then we also have series.
And you know this is something I actually debated the plural of series in English modern traditional
spoken English is just a series.
However a lot of people would argue that we should name this table series's within E.S. in the end just
to make it clear that that's a table name.
I'm not exactly a fan of that.
It's really determined by the project or the team you're working with or by you if you're working on
your own thing.
I've worked on projects where we had to abide by that where the plural was always with an S afterward.
Even if that meant mousies instead of mice.
But I'm going to leave it a series just because I think it's simplest.
So series has a primary key id a title so that would be Fargo which is a TV show as well.
Really good TV show actually not just a movie.
So Fargo or halting catch fire of Seinfeld and then the release year.
So 2014 or 2002 or whatever it is.
And then a genre which we're going to keep very simple.
We're only going to have animation comedy and drama.
Just a couple of examples of genres but again these on their own are just separate entities where they're
joined is through this third revues table which is what's known as a join or union table.
It's connecting these two.
And the way it's doing that.
Well first of all it has an ID nothing special there.
Every review has an ID a rating.
So this would be you know 5.0 for a mediocre show or a nine point nine for a fantastic show.
And then the series ID.
So that's going to be a foreign key pointing to this series table.
So what series is this Fargo.
Is this Seinfeld.
And then the same exact same but the same idea for reviewer ID it's going to be a foreign key pointing
to the reviewers table the ID feels.
So then a single review will have an ID it will have a rating 9.00 a series Id like one and then a reviewer
ID which could also be one they're pointing to different tables.
So this is a centralized repository or a centralized place for our information to be stored.
It's connecting these two tables through this table.
So hopefully you can see how this is going to work.
Basically reviews on its own is going to be a pretty ugly table to look at.
It's just going to be numbers it will have an ID which is number rating as a number series ideas number
and reviewer ideas and number.
So looking at it on its own is is actually going to be very confusing.
It's not going to make sense.
So we'll have to use Joines to be able to understand things so that we can use a join to replay series
ID with the name of the series that it says Fargo instead of one or it will say blue steel instead of
reviewer ID 3 and so on.
So that's our basic idea here.
Here's a simple Or here's an example with some simple data.
Very very simple.
You know two reviewers.
So we've got blue steel and Wyatt Earp idea of one and two then we've got two series Archer and Fargo.
If you're not familiar with these shows don't worry.
That's not necessarily relevant.
Archer is animation released first in 2009.
Fargo is a drama with a little comedy released in 2014.
Archer has idea of one Fargo whose idea of two so they're independent within our reviews table.
Well somebody reviewed something with a rating of eight point nine.
The person who did that has a review or idea of one.
So that is still so blue still reviewed something and gave it an eight point nine.
Well what did she review.
Whatever has serious ID too.
So Fargo.
So Blue gave Fargo an eight point nine.
Then we have a 9.5 down here and the reviewer ID is too.
So that's why it or this field or this row here in reviewers.
Wyatt Earp also reviewed Fargo because ID to as we can see corresponds to Fargo.
So essentially what we're storing here Fargo has been rated twice once by blue 8.9 and watched by Wyatt
as a nine point five.
So as you can see the data is not that pretty in this table.
And this is only with two reviews.
Will be working with somewhere around 100 reviews which is a lot of data to look at on its own.
And so we'll really be focusing on making that data understandable readable which is really the point
of this entire exercise.
So we're pretty much done here in this first video.
We didn't actually do anything except discuss our schema but in the next video we'll actually go about
implementing the tables and inserting some data on our.