Stage 2: multi-user support

Supporting multiple users in the context of this application means that our app should know who has watched which movie.

At the moment our movies table looks like this:

title	release_timestamp	watched

The watched column is an indicator as to whether we've watched the movie or not. But we have no way of storing who has watched the movie.

When you encounter this problem, usually the solution is to separate the two related sets of data: movie information and watcher information. This improves atomicity.

Atomicity

The separation of data into smaller and smaller pieces until each piece of data cannot be separated further while still holding meaning on its own.

Let's implement a solution that many students would attempt next. It is not a perfect solution but it will show us more about SQL and also about the thought process you want to avoid when developing something like this.

Since we already have most of the code, implementing this imperfect solution won't take long!

Two tables

Our imperfect solution will make use of two tables: movies and watched.

The movies table will look like this:

title	release_timestamp

And the watched table will look like this:

movie_title	watcher_name

What we'll do is store our movies in the movies table, and when someone watches a movie we'll delete it from the movies table and add it to the watched table, together with the person's name.

Clearly this still has problems:

• Every time someone watches a movie, we lose movie data from the movies table; and
• If two people want to watch the same movie, it has to be added twice to the movies table.

However this solution is a great step toward our final solution. Let's implement it and learn some new SQL while we do it. First step: learning how to delete rows!