title: Multiple many to many's for genre
body: <p>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:</p>
  
  <p>In this scenario I refer to the MOVIE, TV_SHOWS, and BOOKS TABLES as categories.</p>
  
  <p>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?  </p>
  
  <p>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?  </p>
  
  <p>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.</p>
  
  <p>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.  </p>
  
  <p>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.</p>