Database Tables via Normalization vs Experience/Opinion

Question

I am attempting to explain how the mock artist/soundtrack data (first image below) can be normalized from 1NF to 2NF to 3NF, step-by-step to get the result that I think is best for the database. It's almost as if normalization is getting in the way of what I want to do, but am I just not understanding something in the normalization process? I can definitely see how this mock data can be normalized to 1NF by making each row unique and removing duplicates, but at which stage, for example are we told to assign Composer ID as a foreign key for the tracks table or the movie table? Is that just something we do from experience? Is there no right or wrong?

In short, my question is, can anyone show or explain how the mock data here ...

Was turned into this using all the first 3 stages of normalization?

Answer 1

Well your 1NF would be to have a distinct record for each track name so essentially the mock data with the first record split into 2...

2NF is to take out the repeated keys which to my mind is what you've displayed as the 3 separate tables, and potentially that might be as far as you need to go.

You could add a further table to allow for a track to feature in more than one movie ie create a movie tracks table referencing the track id and movie id respectively (and removing movie id from the tracks table).

Similarly, you could go to the extreme of allowing for collaborative compositions by also having a track composers table but that is probably not sufficiently common to make the effort worthwhile

Normalization is something that definitely becomes easier with experience and can be taken as far as suits the purpose of the data as can be seen from the example.