Instructor: Hi Guys, and Welcome Back
In this video, we look at the three stages of our project
through which we will build the complete application,
the first stage, we're gonna have one database table.
And that's because
users are gonna be able to add movies to a table,
as well as store, whether they've watched the movie or not
all in the same table.
So at this point, we're not gonna support multiple users,
it's just gonna be one user
with one table of movies,
and whether they've watched it.
So this is more or less what it's gonna look like.
We have the title of the movie as the first column,
we have the release timestamp,
or when the movie was released,
we're gonna talk about timestamps a bit more later on.
And we also have whether it's been watched or not
a one means that it's been watched,
a zero means it's not been watched.
So as you can see, this is pretty simple.
But there's no way for us to support multiple users
because there's no information in this table
to tell us who's watched what.
So at the moment, the app will be single user
in this first stage,
every movie added will only belong to one user,
and if the watched column has a one in It
that means that the one user that has this application
has seen this movie.
Then after we've done this,
and we've completed this and we know it works,
we're gonna move over to the second stage,
which is supporting multiple users.
In order to do this,
we're gonna need to separate who watched what,
from the movies themselves.
And so we will have a table.
This is the one on the right,
that stores the title of the movie,
and when it was released,
and then the table on the right
stores who watched the movie
and the title of the movie they watched.
So here we've got, for example,
that Rolf Smith watched two movies
and Ann watched one movie.
Remember that building this project incrementally, like this
is a normal way to build software,
we only add the features we need when we need them.
Finally, for the third stage,
we're going to reduce duplication further
by storing the movie information
and the user information in their own tables.
And who watched what in a separate table.
You can see in the previous tables,
that the movies name was there twice,
we're gonna get rid of that duplication.
So the watched table,
the one that tells us who watched what
is only going to reference the other table.
So we're gonna have something like this.
We've got the movies on the left.
And each movie has an ID column,
which is the primary key.
We're gonna talk about all of this as we go along.
Then, we've got the user data in the middle table,
at the moment, there's only user name in there,
but we could add more data if we wanted.
And finally, on the right,
we've got the mapping of who watched what,
so the user and the movie ID.
So with that information,
we could go into the user table
and find more data about the user.
Or we could go into the movie table
with the ID and find more information about the movie data.
We're going to learn more about how this is related
and how relationships work in a later video.
So why a table with only username?
Well, like I said,
this is just an example
we could add a lot more information into that table
and usually you would
like email date of birth and location et cetera,
other data related to the user.
So what we're gonna learn through this process
is how to better structure the data on the tables.
So that data only exists with meaning in one place.
This is called normalisation
and allows us to deduplicate data
and keep it all in one place.
So the other tables can access that single place,
we're gonna learn how to use JOINs
to query data from multiple tables.
And it's important to notice that normalised data
has to have meaning on its own.
And so you can take it too far.
And don't divide a phone number
into its digits, for example,
because the digits don't mean anything on their own.
Don't divide a date
into the day, month and year
because that doesn't mean anything on its own either.
So, and normalisation allows us to extract data
from one table into two tables
if it still has meaning on its own.
We've got some references
in the resources section of this lecture
with a step by step normalisation of a database
which has an interesting read.
And indeed, the resource does say
when it's taken too far,
but nonetheless, it's interesting.
So let's get to it.
We're gonna be building this project throughout the section.
So let's get started.
I'll see you in the next video.