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:
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