# Three stages of our project
We'll develop the project in three stages. This is so it's easier to develop, and also because we'll be learning about so many things while we work on this, that splitting it up is going to do wonders for our morale!
This is a normal and good way to develop software. First, you develop a working project with very few features; then you add more, and change the structure of your code and your database only if you need to.
# First stage: one table
The first stage of our project will let users add movies to a table and store whether they've watched the movie or not.
This first stage will not support multiple users.
We will have one table, movies, with this structure:
| title TEXT | release_timestamp REAL | watched INTEGER |
|---|
- The first column is the title (or title) of the movie.
- The second column is the date of release.
- The final column will have the value
1if the user has watched the movie, or0if they haven't.
By building this project we'll have our first taster of storing dates in a SQL database.
# Second stage: two tables
In the second stage we will divide the table into two. This improved atomicity will mean that we will support multiple users.
We will learn about one to many relationships in this stage.
The movies table will have this structure:
| title TEXT | release_timestamp REAL |
|---|
And the watched table will have this structure:
| watcher_name TEXT | title TEXT |
|---|
- The name of the user who has watched the movie.
- The title of the movie they have watched.
# Third stage: three tables
In the final stage of the application we will learn about many to many relationships, by creating three tables:
The movies table will have this structure:
| id INTEGER PRIMARY KEY | title TEXT | release_timestamp REAL |
|---|
The users table will have this structure:
| username TEXT PRIMARY KEY |
|---|
And the watched table will have this structure:
| user_username TEXT | movie_id INTEGER |
|---|
In the watched table we will also use foreign keys to relate the user_username column to users.username and the movie_id column to movies.id.
# Normalization
Structuring data so it is only defined in one place. Through normalization, we reduce duplication and unwanted dependencies across tables.
For example, by splitting movie information from user information, and both of those from "watched" information, we've increased the normalization of our database. There's many different levels of normalization.
The "higher" level of normalization, the more separated data is in its own tables[1].
It's important that normalized data has meaning on its own. You don't want to take it too far:
- Don’t divide a phone number into its digits (they’re useless on their own).
- Don’t divide a date of birth into day, month, and year.
We will then learn about JOINs and how to query multiple tables at the same time to retrieve related information.
Every now and then we'll also throw in a chapter on a new SQL query.
There's a lot to get through! Let's get started!