All right.
Welcome to the next section.
So we're continuing on the same similar path, talking about relationships, talking about joints,
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 to many is is common as 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.
I don't want to scare you away, but you'll see there's a little more setup involved.
And then once we'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 just by 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 if it's a collection, if it's an
anthology, there might be 20 authors, and 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 an Instagram photo, a
Tumblr, whatever you call it, a Tumblr post, 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 Vanlife, if you're familiar with
that hashtag, 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
tags.
I mean, there's a limit sometimes, but on Instagram you can have 30 hash tags.
I think it is on a 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 maybe college students or university students where students have multiple
classes, they pick their classes at the beginning of the semester.
One student can have usually must have multiple classes unless maybe it's your last semester, but you
have multiple classes that you go to that you're related to.
And then each class is going to have multiple students in it.
And that to many to many relationship.
And the one that we'll be working with in this section is 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 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, 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 could sign up and rate Archer as an 8.5 out of
ten.
So to accomplish this relationship and 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 up here.
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'll keep it very simple.
This will be Colt or this will be Charlie or whatever it is.
But the way that they're connected is through a third table.
Through a reviews table.
So this will make sense when I show you the schema in just a moment.
But think about it this way Series exists on their own.
They're just TV shows.
Reviewers exist on their own.
They have nothing to do with the series out of the box.
It's just someone's name.
But they then are associated through this 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 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 ID 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 registration date may be less active, all that kind of stuff.
But 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 is with an S on 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 mouses instead of mice.
But I'm going to leave it as 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 Halt and Catch Fire, Seinfeld and then the release here.
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.
So 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 reviews 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, there's a rating.
So this would be 5.0 for a mediocre show or 9.9 for a fantastic show.
And then the series ID.
So that's going to be a foreign key pointing to the series table.
So what series is this, Fargo?
Is this Seinfeld?
And then the same?
Well, not the exact same, but the same idea for reviewer ID.
It's going to be a foreign key pointing to the reviewer's table the ID feels.
So then a single review will have an ID, it will have a rating 9.0 a series ID like one, and then
a reviewer ID, which could also be one there pointing to different tables.
So this is a centralized repository or 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.
It's going to be a pretty ugly table to look at.
It's just going to be numbers.
It will have an ID, which is the number rating is the number series, ideas number and reviewer IDs
and number.
So looking at it on its own, it's actually going to be very confusing.
It's not going to make sense.
So we'll have to use joints to be able to understand things so that we could use a join to replace series
ID with the name of the series so that it says Fargo instead of one, or it will say Blue Steel instead
of reviewer ID three and so on.
So that's our basic idea here.
Here's a simpler here's an example with some simple data very, very simple 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 Animation released first in 2009.
Fargo is a drama with a little comedy released in 2014.
Archer has idea of one, Fargo has idea of two.
So they're independent.
But then our reviews table well somebody reviewed something with a rating of 8.9.
The person who did that has a reviewer idea of one so that is blue steel.
So Blue Steel reviewed something and gave it an 8.9.
Well, what did she review?
Whatever has series ID two.
So Fargo so Blue gave Fargo an 8.9.
Then we have a 9.5 down here and the reviewer ID is two.
So that's Wyatt Earp.
This field or this row here in reviewers.
Wyatt Earp also reviewed Fargo because it's ID too, as we can see, corresponds to Fargo.
So essentially what we're storing here, Fargo has been rated twice, once by blue as a 8.9 and once
by why it is a 9.5.
So as you can see, the data is not that pretty in this table.
And this is only with two reviews.
We'll 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 own.