Trying (and failing) to create a DVD cataloging database table design

database-design

I've been trying (and failing) to create a DVD cataloging database. My initial design has a whole bunch of many to many relationships. Consequently, my attempts to normalize it has produced a whole bunch of Composite Entities.

It's a huge mess and I think I've almost certainly done something wrong somewhere.

Right now, it looks like this:

movies(id, tmdb_id, imdb_id, title, summary, release_date, runtime, poster_path, rating)
series(id, tmdb_id, imdb_id, title, summary, start_date, end_date, poster_path, rating, status)
seasons(id, tmdb_id, imdb_id, title, summary, season_number, poster_path, FK series_id)
episodes(id, tmdb_id, imdb_id, title, summary, airdate, still_path, episode_number, FK season_id)

genres(id, genre_name)    
people(id, tmdb_id, imdb_id, name)
jobs(id, job_name)

movie_genres(FK movie_id, FK genre_id)
series_genres(FK series_id, FK genre_id)
movie_crew(id, FK movie_id, FK person_id, FK job_id, character_name)
season_crew(id FK season_id, FK person_id, FK job_id, character_name)
episode_crew(id, FK episode_id, FK person_id, FK job_id, character_name)

discs(id, type, features, status)
case(id, name, status, location FK disc_id)

disc_movie(FK disc_id, FK movie_id)
disc_episode(FK disc_id, FK episode_id)

my very messy database

The problem is, I'm not sure how to fix it.

I know the first four entities have a lot if common attributes. I don't know if I can rip them out and create a new entity and then have the four inherit from the common entity (????) or…?

Assuming I'm on the right track with that, I don't really know how to model it either.

Best Answer

Per Martin Fowler, there are basically three ways to model inheritance in a relational database:

  • Single Table Inheritance: everything goes into one table and you use nullable columns that may or may not be present for the
  • Class Table Inheritance: base class goes in one table with one-to-one relationships with sub-classes
  • Concrete Table Inheritance: each concrete class is given a distinct table (what you have now)

Some good answers here going into more detail:

https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database https://stackoverflow.com/questions/1567935/how-to-do-inheritance-modeling-in-relational-databases

Which you choose depends on exactly what you want to do, and there is not always one right answer, although you could probably lean towards class table inheritance as a "default" that is probably correct, and then work from there.

I'd suggest using class table inheritance you generalise movies and episodes into a "work" (or whatever terminology you think best) and then have movie or series as a lightweight entitity used to classify these "works".

This is all just quick suggestions on how you might do it- remember, there is not necessarily One and Only One Right Way, it depends like Evan says on what exactly your use case is and what exactly you are trying to model.

One small thing to point out- don't use "case" as a table name. You CAN use it but you would have to escape it everywhere as it is a SQL reserved word and this is a pain in the neck and will make you wish you didn't. Use something else.

Related Question