Database Design - TV Show Database Design

[mayutaka8985]

Forgive me if this is the wrong area to post, I wasn't 100% sure.

 

I'm trying to design a website for a TV show database where people can sign up as a member and then track the shows they are watching and keep track of which episodes they've seen.

 

How it would work:

Each TV show has it's own page on which there is information displayed about the show and then a listing of the episodes in a table with two columns: one containing the episode number and the other showing the status as either watched or not watched.

I want to be able to keep track in a database which episodes for which show each user has watched/not watched, so each time they visit the site they can easily view which episode they are up to for which show.

I hope that makes sense.

 

The question is: how do I go about doing this (in regards to the episode listing and the episode's status)? No matter which idea I come up with for the database it leads to a seemingly ridiculous conclusion. Can someone please point me in the right direction of how I should go about this?

 

Originally I thought something like:

 

episodeListing table

user_id || tv_id || ep_num || status

45434  || 234  ||    3      || watched

as columns in a database for the episode listing, but doing this would mean that there would be a separate row for each episode of each tv show for each user, meaning if 100 users watched a tv show with, for example, 16 episodes then it would be 1,600 entries just for the one tv show, which forgive me if I'm wrong, sounds a bit excessive and I'm sure it's a bad way to go about it.

 

Ideas, please?

[KevinM1]

You need four tables:

 

1. A user table, which contains user information.

2. A TV series table, which contains series info.

3. A TV episode table, which contains individual episode info and a series ID as a foreign key, linking an episode to a series.

4. A table to combine users and episodes.  This can be a simple pivot table containing only a user ID and episode ID, as users will only be linked to episodes they watched (there's no need for a special 'watched' column - the user has either seen it, and is linked to it, or they haven't and no link exists).  Series info can be gathered by going through the episode table.

 

But yes, you'll have a lot of rows.  That's what happens when you normalize your data.  It's a tradeoff between the number of rows stored vs. logical errors and convolutions required to successfully insert and display data.  This kind of data is exactly why relational databases exist.

[mayutaka8985]

Thank you very much! I understand now.