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?
What you are trying to model are two many-to-many Relations, so you will need to implement an Associative Table for each one.
The primary key in both tables is the composition of the FKs.