Schema for music lists + playlists web app

Question

Assuming that all playlists are subsets of a user's main library of music, how should a main library as well as playlists be managed in the database? It seems like a playlists table would grow extremely quickly for even a moderate amount of users. Would this be a decent use case for a nosql database having a list of playlists in each User collection, as opposed to a giant playlists table incorporating all users in the same place?

Answer 1

You haven't given a lot of details so I'm answering as best I can. I think a relational database solution is perfect for this problem and though you might end up with millions of records in the playlists and playlists_songs tables any modern RDBMS should be able to handle that with no problems.

You may or may not need/want a table for albums, I've included it here for the sake of completeness...

albums
    id          unsigned int(P)
    artist_id   unsigned int(F artists.id)
    name        varchar(50)
    ...

+----+-----------+-----------------------------------+-----+
| id | artist_id | name                              | ... |
+----+-----------+-----------------------------------+-----+
|  1 |         1 | The Last in Line                  | ... |
|  2 |         3 | American IV: The Man Comes Around | ... |
|  3 |         2 | Animal House Soundtrack           | ... |
|  4 |         4 | None or Unknown                   | ... |
| .. | ......... | ................................. | ... |
+----+-----------+-----------------------------------+-----+

Like albums, you may or may not want a table for artists but I've included it in case you want to show that kind of data.

artists
    id              unsigned int(P)
    name            varchar(50)
    ...

+----+-------------+
| id | name        |
+----+-------------+
|  1 | Dio         |
|  2 | Various     |
|  3 | Johnny Cash |
|  4 | Unknown     |
|  5 | Sam Cooke   |
| .. | ........... |
+----+-------------+

I view playlists as very basic: a user can have an unlimited number of them and they have a name. In my example data we see that bob has two playlists "Mix" and "Speeches" while mary has only one "Oldies".

playlists
    id          unsigned int(P)
    user_id     unsigned int(F users.id)
    name        varchar(50)

+----+---------+----------+
| id | user_id | name     |
+----+---------+----------+
|  1 |       1 | Mix      |
|  2 |       1 | Speeches |
|  3 |       2 | Oldies   |
| .. | ....... | ........ |
+----+---------+----------+

We have to keep track of what songs are on each playlist. In my example data you can see that "Egypt (The Chains Are On)" and "Hurt" are on the "Mix" playlist while the "Town Hall speech" is on the "Speeches" playlist and "Egypt (The Chains Are On)", "Hurt" and "Twistin' the Night Away" are all on the "Oldies" playlist.

playlists_songs
    id              unsigned int(P)
    playlist_id     unsigned int(F playlists.id)
    song_id         unsigned int(F songs.id)

+----+-------------+---------+
| id | playlist_id | song_id |
+----+-------------+---------+
|  1 |           1 |       1 |
|  2 |           1 |       2 |
|  3 |           2 |       4 |
|  4 |           3 |       1 |
|  5 |           3 |       2 |
|  6 |           3 |       3 |
| .. | ........... | ....... |
+----+-------------+---------+

Even though millions of users might all have the song "Hurt" in their collection, we only need to store information about each song once. So in the songs table we store information about each song including where the actual audio file is located. My example for file locations are just off the top of my head, how you would actually organize the files in the filesystem could easily be very different.

songs
    id              unsigned int(P)
    album_id        unsigned int(F albums.id) // Default NULL
    artist_id       unsigned int(F artists.id)
    name            varchar(50)
    filename        varchar(255)
    ...

+----+----------+-----------+---------------------------+---------------------------+-----+
| id | album_id | artist_id | name                      | filename                  | ... |
+----+----------+-----------+---------------------------+---------------------------+-----+
|  1 |        1 |         1 | Egypt (The Chains Are On) | /media/audio/1/1/9.mp3    | ... |
|  2 |        2 |         3 | Hurt                      | /media/audio/3/2/2.mp3    | ... |
|  3 |        3 |         5 | Twistin' the Night Away   | /media/audio/5/2/3.mp3    | ... |
|  4 |     NULL |         4 | Town Hall speech          | /media/audio/4/4/<id>.mp3 | ... |
| .. | ........ | ......... | ......................... | ......................... | ... |
+----+----------+-----------+---------------------------+---------------------------+-----+

And of course your users table.

users
    id              unsigned int(P)
    username        varchar(32)
    password        varbinary(255)
    ...

+----+----------+----------+-----+
| id | username | password | ... |
+----+----------+----------+-----+
|  1 | bob      | ******** | ... |
|  2 | mary     | ******** | ... |
| .. | ........ | ........ | ... |
+----+----------+----------+-----+

Answer 2

I think having a conceptual design like below will helps. The key here is to store media files out of application's database and make a link between them by media's path. Some RDBMS's provide APIs to access file system, like Oracle BFILE or SqlServer FILESTREAM .

Using relational or No-Sql solution is related to application business.
Any of them come with its own pros and cons, a comparison could be found here.