Need help with a table relationships and over-all design

Question

My design is a Netflix clone which has movies and shows just like Netflix. There is also a table called transcodes which will have information on a movie or episode video transcode.

My problem is, I don't know how the I should connect the transcodes table to the movies and episodes table. I thought about having a movie_id and episode_id foreign key in transcodes but then I was struggling with coming up with the query. I think adding a transcode_id to the movies and episodes tables could work, but I feel there is a more efficient way of handling it.

What would be the best way to connect transcodes to the movies and episodes tables?

Maybe my design is bad? If anyone sees any possible improvements, I would appreciate feedback on that as-well.

- EDIT -

I've updated my design to reflect the feedback. The only question I have is, some tables have both movie_id and show_id which means one of those will always be NULL, is that bad design? Or is that acceptable? Also, take a look at the titles_posters table. Something seems a little off about how everything is associated, seems like there will be quite a bit of nulls as-well, but maybe that is fine?

Answer 1

The question to ask yourself is simple: Can a single Transcode record be associated with more than one Movie or Episode?

If Yes, you need a linking table that links Movies and Transcodes and one that links Episodes and Transcodes to create a Many-To-Many relationship.

If No, you simply add transcode_id and a foreign key to the Movies and Episodes tables.

You need to apply that same logic to some of your other tables/relationships. For example, can a Movie really only be associated with a single user as per the current design? What is that relationship - the user who uploaded it, a user who watched it, a user who recommended it?

A few others:

• Shouldn't poster_id on Episodes actually be on Seasons? A season may have a different poster, but surely not an individual episode?
• Can users have multiple roles? If so, you need a linking table.
• Can Movies or Shows have multiple Genres (Action & Thriller, Drama & Comedy for example)? Again, if so, linking table.

Check out this Wikipedia page which covers off some basics on database/ERD design. You need to identify the various entities within your data model (a Movie, for example, is an entity, as is a user) then define the relationship between each entity (Entity A can have only one Entity B associated, and Entity C can have many associated Entity D records etc) and that will help you determine and design your ERD.