Many to Many with Variable Foreign Key

user_16943499 0
Viewed 106

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

entity-relationship diagram database
1 Answers

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.

user_164590350
Related