1

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.

enter image description here

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

enter image description here

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.

  • Thanks for the feedback. To answer your questions... a transcode will be associated to only one movie or episode. A user can upload as many movies and or episodes he or she desires, but only they can own it. You bring up a good point about the poster - a movie, show, and episode can have a poster. You bring up another good point about roles - it needs to be many-to-many. Lastly, the genres need to have a pivot table as-well, for some reason I only thought they would have multiple genres. I really appreciate the feedback... I'll refactor everything and post it. – Mike Apr 10 at 1:42
  • I've added an updated design. – Mike Apr 10 at 17:43
  • 1
    RE: titles_posters, I would separate that table out. Have a posters table and a linking table for each of the relationships, e,g, a movie_posters linking table and a show_posters linking table and so on. it may seem a little counter-intuitive but poster is an entity all on its own and movie_id is not an attribute of a poster (because it is not a one-to-one) but a relationship. – HandyD Apr 11 at 0:54
  • 1
    RE: producers - The link to transcodes may be redundant because a transcode is linked to a movie/show already. If there is no possibility a transcode can be linked to a different producer than a show or movie, then I would remove that relationship – HandyD Apr 11 at 0:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.