How to insert multiple foreign keys into a table?-CodePudding

I am new to SQL and beginning by trying to clean datasets. This is my original table (movies_details)

ID  movie     release_year       genre              actors
---------------------------------------------------------------------
1   Movie_A   2010               Romantic, Comedy   Actor_A, Actor_B   
2   Movie_B   2011               Documentary        Null
3   Movie_C   2010               Comedy, Horror     Actor_A, Actor_C
4   Movie_D   2020               Documentary        Actor_B

I believe I first need to create a new table satisfying 1NF i.e creating atomic values. So I create 3 new tables

movie table:

movie_ID    movie     release_year
----------------------------------
m_1         Movie_A    2010
m_2         Movie_B    2011
m_3         Movie_C    2010 
m_4         Movie_D    2020

genre table:

genre_ID    genre    
---------------------
g_1         Romantic
g_2         Comedy
g_3         Documentary
g_4         Horror

actor table:

actor_ID    actors
--------------------
a_1         Actor_A
a_2         Actor_B
a_3         Actor_C

I understand that for each table we need to have a foreign key linked to all the tables in the database. My question is once I assign a foreign key to each of the new tables, there will again be a problem of not satisfying 1NF. For example, If I add foreign keys to the genre table it will be as:

genre_ID    genre         movie_ID     actor_ID
-----------------------------------------------------
g_1         Romantic      m_1          a_1, a_2
g_2         Comedy        m_1, m_3     a_1, a_2, a_3  
g_3         Documentary   m_2          Null, a_2
g_4         Horror        m_3          a_1, a_3

If I add foreign keys to every table it becomes more complicated. How do I fix this genre table because it seems the 1NF problem does not solve this way?

CodePudding user response：

What you are trying to model are two many-to-many Relations, so you will need to implement an Associative Table for each one.

MovieGenre with FKs to Movie and Genre
MovieActor with FKs to Movie and Actor

The primary key in both tables is the composition of the FKs.

CodePudding user response：

Till here with your design, you need to add two other tables to your design. cause of N : M relation between your movie_table table and genre_table table, you need to break down this relation into the Table_XYZ table that keep the relationship between these two tables, and its structure will be like below:

Table_XYZ
movie_ID    genre_ID
m_1         g_2
m_1         g_1
m_2         g_3

Also, there is N:M relation between the movie_table table and the actor_table table, you need to add Table_MNO and it would be like:

Table_MNO
movie_ID    actor_ID
m_1         a_2
m_1         a_1
m_2         a_3
m_2         a_1
m_2         a_5