1

OK here is the scenario. I have tables like MOVIES, BOOKS, TV_SHOWS. All of these tables can have many genres and any genre can have many multiple files from any table. So the problem I'm faced with is this:

In this scenario I refer to the MOVIE, TV_SHOWS, and BOOKS TABLES as categories.

Should I make MOVIES GENRE, BOOKS GENRE, and TV_SHOWS GENRE tables, one for each table? Or is there some way to link all the tables to the single GENRE table in a many to many style relationship without sacrificing the uniqeness of the GenreID IN THE GENRE table?

Now I have gone through the other questions that touch on this and they all have a resounding NO. Apparently there is no database that can accommodate this style of many to (many)many relationship. But I am trying to find out if there is any alternative to this or should I just make multiple GENRE tables?

The only thing I can think that might work is to have a foreign GenreID column in each category table. And then have a many to many relationship with the GENRE table for each one of the category tables. All of the category tables will be linked using a join table called CATEGORY_GENRE that has composite primary key made from the foreign key GenreID columns from all the category tables and the GenreID primary key from the GENRE table. The only problem with this is that I don't think one side of a many to many relationship can be linked with a foreign key, both sides of the relationship have to be linked by their primary's.

This whole thing would be easier if the category tables were on the many side of a one to many relationship but unfortunately sometimes a movie or book has more than one genre like fantasy, Science fiction, etc.

Unfortunately I think the only way to do this is to make multiple GENRE tables but I just want some opinions before I go ahead and do it.

Christopher
  • 53
  • 8

1 Answers1

2

You should consider using a polymorphic association, specifically a join table between your various "media" (books, tv_shows and movies) and your genres table. This media_genres join table needs to describe both the genre_id, the media_id, and additionally, the media_type (e.g. "book", "tv_show", "movie").

Here are some alternative approaches: https://stackoverflow.com/a/441111/3390061

Community
  • 1
  • 1
nathanallen
  • 424
  • 3
  • 10
  • This is an awesome link nathanallen. I appreciate this a lot because I didn't find this post with the keywords I used. I think I will try the approach with one genre table that has a column for each category table which all allow nulls. Then at least one column will have the foreign key required. – Christopher May 04 '16 at 15:46