Geek Answers Handbook

Scheme for music lists + playlists

Assuming all playlists are subsets of the user's main music library, how should the main library and the playlists in the database be managed? It seems that the table playlistswill grow extremely fast even for a moderate number of users. Would this be a decent use case for a database nosqlthat has a list of playlists in each collection User, as opposed to a giant playlist table that includes all users in the same place?

+4
source share
2 answers

You did not provide a lot of details, so I answer as best as possible. I think the relational database solution is perfect for this problem, and although you may run into millions of table entries playlistsand playlists_songsany modern RDBMS should be able to handle it without a problem.

You may or may not need / want a table for albums, I have included it here for 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 to have a table for artists, but I have included it if you want to show such data.

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

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

I consider playlistsit very simple: a user can have an unlimited number of them, and they have a name. In my examples, 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   |
| .. | ....... | ........ |
+----+---------+----------+

, . , " (The Chains Are On)" "Hurt" "Mix", " " "" " (The Chains Are On )", "Hurt" "Twistin the the Night Away" "Oldies".

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 |
| .. | ........... | ....... |
+----+-------------+---------+

, "Hurt" , . songs , . , , .

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 | ... |
| .. | ........ | ......... | ......................... | ......................... | ... |
+----+----------+-----------+---------------------------+---------------------------+-----+

, , users.

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

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

, , , . enter image description here . API , Oracle BFILE SqlServer FILESTREAM.

No-Sql -.
, .

+2

More articles:


All Articles