Proper Design of Simple Music-like Database

Hi,
I was wondering if someone could let me know if this simple database design is correct?

Like iTunes, I’m implementing a sidebar and the ability to have user created playlists added to that sidebar. Records from a listbox to the right of that sidebar will be dragged onto the playlists.

I’m thinking during the DropObject event of the sidebar, I would update the record with the new playlist_ID based on what row the user dropped it on top of. Does this look ok or am I not foreseeing something?

CREATE TABLE "Song"( "id" INTEGER PRIMARY KEY NOT NULL, "name" TEXT ); CREATE TABLE "Playlist"( "id" INTEGER PRIMARY KEY NOT NULL, "name" TEXT ); CREATE TABLE "Song_has_Playlist"( "Song_id" INTEGER NOT NULL, "Playlist_id" INTEGER NOT NULL, PRIMARY KEY("Song_id","Playlist_id"), CONSTRAINT "fk_Song_has_Playlist_Song1" FOREIGN KEY("Song_id") REFERENCES "Song"("id"), CONSTRAINT "fk_Song_has_Playlist_Playlist1" FOREIGN KEY("Playlist_id") REFERENCES "Playlist"("id") ); CREATE INDEX "Song_has_Playlist.fk_Song_has_Playlist_Playlist1" ON "Song_has_Playlist"("Playlist_id"); CREATE INDEX "Song_has_Playlist.fk_Song_has_Playlist_Song1" ON "Song_has_Playlist"("Song_id");

And a trigger for when a user deletes a playlist…

CREATE TRIGGER "Song_has_Playlist_CascadeDelete"( BEFORE DELETE on "Playlist", FOR EACH ROW BEGIN, DELETE FROM "Song_has_Playlist" WHERE "playlist_id" = "old.id " END;

Many Thanks!

Not sure I’d called the table “Song_has_Playlist” but that’s just me :slight_smile:
I’d probably name it “PlayList_Songs” since playlists have songs but … we’re picking nits at this point

The design seems right for getting a playlist to be a list of songs

Looks really good to me actually, good use of foreign keys here :).

Usually you don’t need to create a trigger just for the purpose of deleting related records. In your create table statement just say:
REFERENCES “Playlist”(“id”) ON delete cascade. BTW, you might want to setup the “fk_Song_has_Playlist_Song1” constraint in a similar way. But then again, I don’t know what DB you are using…

Thanks guys. Can I ask if there is any disadvantage to using a trigger for deleting related records in this way?

No, what you did is just fine. It might be a tad slower if you delete billions of those Playlist records.

Thanks.

If I need an “All Music” group in my sidebar (and possible other static groups) , is it proper to create a record in my playlist table for “All Music” and create a record in PlayList_Songs for each and every song in the database with the playlist id for “All Music”?

I’m asking because I’m wondering how I would quickly run through all my records to get a sidebar badge count for all of the possible items in my sidebar.

I was thinking this:

SELECT g.name, count(a.song_id) FROM Playlist g LEFT JOIN Playlist_Songs a on g.id = a.playlist_id group by g.id

But then I would need to have that “All Music” Playlist_Songs record for each and every song in the database.

Many Thanks!

The idea of tweaking your data or even your table structure for the purpose of making it easier for the client to get the data it wants is usually frowned upon. It’s just to easy to mess up. In the concrete example, if you created a record “all Music” then even such a simple task as obtaining the number of playlist becomes challenging, because you always have to remember that you have that ghost record in there.
So, just normalize your structure as far as possible. Then be (or get - by reading SQL for Smarties for example, Google is your friend) smart about accessing that data - SQL is a very flexible language. So this is what you might be looking for:

SELECT
‘All Music’,
count(a.song_id)
FROM Playlist g
UNION
SELECT
g.name,
count(a.song_id)
FROM Playlist g
LEFT JOIN Playlist_Songs a on g.id = a.playlist_id
group by g.id

To make sure the ‘All Music’-row is the first one, do this:

Select t.name,t.count from(
SELECT
‘All Music’,
count(a.song_id),1 as orderbyfield
FROM Playlist g
UNION
SELECT
g.name,
count(a.song_id),2 as orderbyfield
FROM Playlist g
LEFT JOIN Playlist_Songs a on g.id = a.playlist_id
group by g.id) as t order by t.orderbyfield

HTH,

Max

Thank you Maximilian. I appreciate your direction. I understand what you’re doing here but I have a few questions.

I think there was a slight typo in your first SELECT statement (I changed g to a on line 4), so my questions are based on using this code:

SELECT 'All Music', count(a.song_id) FROM Playlist a UNION SELECT g.name, count(a.song_id) FROM Playlist g LEFT JOIN Playlist_Songs a on g.id = a.playlist_id group by g.id

What I forgot to mention is that each song can be in many playlists. So this accumulated ‘All Music’ count is counting the same song each time it appears in a new playlist when it should only count it once.

Also, the first SELECT before UNION works fine on it’s own and the second SELECT statement after the UNION works fine on it’s own, but when I combine them as above, I get incorrect results. The results seem to be missing one playlist. I think the ‘All Music’ column is getting it’s own id which is the same as one of my existing playlist id’s and therefore it’s skipping the real playlist. I hope I explained this correctly.

Many Thanks!

count(DISTINCT a.song_id)

Thanks Dave. The following code seems to work for me. I hope I got it right now.

SELECT playlist_id_col, playlist_name_col, count_col FROM( SELECT 'dummy_id' playlist_id_col, 'All Music' playlist_name_col, count(DISTINCT a.song_id) count_col,1 as orderbyfield FROM Playlist_Songs a UNION SELECT g.id, g.name, count(a.song_id),2 as orderbyfield FROM Playlist g LEFT JOIN Playlist_Songs a on g.id = a.playlist_id group by g.id) t ORDER BY t.orderbyfield