Jose: Hi guys, and welcome back.
In this video, we're going to look at stage three
of our project.
Stage two had a couple problems,
marking a movie as watched, removed it from
the movies table, so we had to re-add it.
And also, we couldn't distinguish between two users with
the same username.
And so these are a couple of problems.
We are going to be fixing them in stage three.
A good starting point for database design is to model after
the real-world entities that your programme deals in.
So our programme, at the moment,
has two main real-world entities, that's users and movies.
So we can create tables for each.
But what about the watching thing?
It's not really a thing, right?
It's an action, something that a user does to a movie.
And then when you get to something like this,
it's interesting to think about how the act of watching
is related to the entities.
So watching movies,
we've got that one user can watch many movies,
but also one movie can be watched by many users.
So this is known as a many-to-many relationship,
and it can be modelled with a third table
that links the other two.
We've seen this before, we're just gonna explain it
a bit better now.
So what's interesting here is that we're going to have
the movies table like that.
We've got the id property, the title
and the release timestamp.
We've got the user data table there that just has
the username at the moment, but,
like we discussed earlier on in the course,
we could have more information about users
here if we wanted.
And finally, notice that these two columns, username
and id at the top, are primary keys.
And finally, we're gonna have this watched table
that has two foreign keys.
The user username column is a foreign key onto
the username column of the users table.
And the movie id column at the bottom, is a foreign key
that maps to the id column of the movies table.
So this third table at the bottom is just
a mapping between users and movies.
It doesn't actually store any more information than that.
With this table, we can easily say
that one user watched multiple movies.
Rolf Smith, in this case, watched two movies.
And one movie was watched by multiple users.
In this case, movie two was watched by Rolf and Anne.
If we wanted to grab more information about movies,
we just go into the movies table and fetch
the related information to the movie id.
And similarly, if we wanted to grab more information
about users, we'd grab the user username column,
go into the users table and fetch the information there.
We're going to learn how to do that using joins in just
a moment.
That's everything for this video.
This is stage three.
We're gonna be implementing this over the next few videos.
Thank you for watching, and I'll see you in the next one.