lundi 4 mai 2020

How to structure tables for television tracking?

I am working on an app that tracks which tv shows you are watching and which episodes from that show you have watched.

Currently I have can track which users are watching which shows, this is relatively simple and it is structured like so:

table structure

So I have a User table which has a primary key id that identifies that user and some other info about that user which is not relevant.

Following this I have another table TV_SHOWS which stores a user_id as a foreign key some information about the show and crucially a field called user_watching that gives a boolean value as to whether the user is watching that show.

How it works is this, when the user clicks a button to add the show a row is inserted into the TV_SHOWS table with the all the information and user_watching = true at some point the user may wish to stop watching so they can click another button that will simply update the user_watching = false.

Now I wish to expand on this idea so that users can track which seasons and episodes of a tv show they have watched, a tv show is comprised many seasons and each season is comprised of many episodes in most cases.

I would like some help with how best to structure this database schema.

I initially thought that I could just expand along the same lines I had previously for instance:

I simply add two id's for the season and episode, in that way I can tell if a user has watched a season by seeing if they have watched all the episodes in that season and if they have watched a show by checking if they have watched all the seasons in the show, but I don't think this seems like good practice.

I could try and do a one table for shows, another for seasons and a final for episodes but I am not sure where I would "track" is a user had watched one of those, would it have to be in that specific table or would I need another table to track it?

Aucun commentaire:

Enregistrer un commentaire