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)
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.