How to store selected songs ids in db?

Hi all i have a db for my music collection. It has one table with the following feilds on it:



I already created pages that with checkboxes that user select indvidual songs and by click of play button it goes and plays those songs for me.

Now i created another button next to play that it supposed to write the selected songs to db and save the ids of slected songs.Note: i use song ids to pull indvidual songs from db to be played by my song player!

My problem is i do not how to design a few tables that keep track of selected songs for each user along with the name of playlist for each user!! In processs i do not want to modify my current table i just want to add more tables to help me achive what i want. I be happy if an expert tell me how i can create tables that store play list(selected songs)info for each user and later i be able to query them easily in order to pull out playlist of each user.Thanks

Well, I know you don't want modify your existing table, but your should normalize it.

This is what I'd do (all id field's are auto_incrementing primary keys: all *_id fields are foreign keys referring to the id field of the table represented by *; fore example, artist_id is an FK to the ARTIST table's id):

Table ARTIST
id
name

Table ALBUM
id
artist_id
name

Table SONG
id
artist_id
album_id
filename
title
track
comment
genre
filesize
bitrate

And that should take care of your general song DB. You could also break title and genre out into their own tables. I probably wouldn't bother with genre, but title's worth considering, since you can conceivably have a lot of repeated information there.

Anyway, on the playlists:

Table USER:
id
username
<other user-specific info>

Table PLAYLISTS:
id
user_id
name

Table PLAYLIST_SONGS
playlist_id
song_id
playlist_order

Note that PLAYLIST_SONGS doesn't have an id; all three columns are part of the PK (unless you want to allow repeated songs in the same playlist). playlist_order holds an integer to list the order in which the songs should be played.

Now you can pretty easily pull anyone's playlist. Say I (user id 55) want to run my 'rainy day' playlist:



You can expand this query to list artist and album information, of course, though I think there are script-side ways to read that sort of information from id3 tags that may be preferable.

If your playlist can contain doubles (..) you might still want to add a unique (primary) key to the playlist table to be able to delete specific records.