Kashyap Kansara Kashyap Kansara - 1 month ago 7
MySQL Question

How should I organize song download links of movies in MySQL?

I am asking in general how should I organize data in database.

Say I have 5 movies and each movie has 5 songs and it's download link.

So should I
- Make table for each movie and add song name and it's download link in that table like:

+-------------------------------+
| Movie Name |
+-------------------------------+
| SongTitle varchar() |
| DownloadLink varchar() |
+-------------------------------+


or
- Make one table that contains movie name, song title and it's download link like:

+-------------------------------------+
| Song Database |
+-------------------------------------+
| MovieName varchar() |
| SongName varchar() |
| DownloadLink varchar() |
+-------------------------------------+


Which way will provide better management of data and take less time to load?

How to decide when to create a separate table and when to use the same table for all the data?

Answer

Movies and songs can exist independently:

movies (movie_id PK, movie_name)
songs (song_id PK, song_name, download_link)

How to associate them depends on your need. If movies can have multiple songs, but each song can belong to only one movie:

movie_songs (song_id PK/FK, movie_id FK)

But if songs can be used in multiple movies, then:

movie_songs (song_id PK/FK, movie_id PK/FK)