How to have Foreign Key Relation with a field that may contain Multiple Types of it?

Question

Hello

I am currently working on a DVD Store App. There are two tables in which I am having an issue. First is the Genres and the other is DVDCatalog. I need that DVD can be listed in one or more Genres. So, to do that I have to store like the IDs of the Genres (4,5,6). This way I can't make a relationship with the GenresID in the Genres Table. Do you have any solution for this, so that I can keep the relationship between them?

Please Help!!!

Answer 1

Yes. Don't store the IDs of the Genres like (4,5,6) use first normal form and then you can get the referential integrity that you need.

You need a relationship table (say DVD_Genres) with 2 columns

DVD_Id and Genre_Id (These form the composite primary key)

Then if DVD Id 1 was in genres 4,5 and 6 you would have 3 rows in this table to represent this fact.

DVD_Id     Genre_Id
1            4
1            5
1            6

This also will make searching for DVDs matching a particular genre easier and more efficient.