Search code examples
databaseentity-relationshipdiagram

Many to Many with Variable Foreign Key

I am drawing an ERD diagram for the following scenario:

A user can have one or many subscriptions. A tv show can be on one or many subscriptions. A movie can also be on one or many subscriptions. The confusion comes from the foreign keys. How would I map it out? What would be the foreign key name etc? A subscription can be linked to a movie, OR a tv show but cannot have both. Is it better to just create a bridge table for each entity type e.g. have a TVSubscription table for TVShows, and a MovieSubscrption table for movies?

I'm confused on how a subscription have be linked to multiple entities.

diagram

Solution

  • A foreign key must be primary key on another table. So basically, your subscription table must have more than one foreign key. The correct structure to your subscription table should be something like:

    id (PK)
user_id (FK referencing USER PK)
movie_id (FK referencing MOVIE PK)
tv_show_id (FK referencing TV SHOW PK)

    This structure should handle your problem.