How to store selected songs ids in db

[keith23]

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

[B]ID[/B]|filename|artist|album|title|track|comments|genre|seconds|filesize|bitrate

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

 

[jrbarnett]

I'd create one new table called PlaylistItem with the following fields:

UserID
SongID
PlaylistID
PlaylistPosition

Composite primary key of UserID, SongID and PlaylistID.
and another one of

PlaylistID - Primary key
PlaylistName
UserID

This way, each playlist can be stored in a table, and be viewed by multiple users (if you decide to allow such permissions). You will also need another table with the details of all authorised users if you haven't already got one with as a minimum the following fields:

UserID - primary key
Fullname

Then, create each of the tables and set referential integrity between the tables. By doing it this way, it allows:
* Any users to have multiple playlists
* Any playlist to have as many songs on it in any order as are required by the system.

The biggest limitation is not allowing a particular song on a playlist multiple times.

John

 

[keith23]

Many thanks for u reply. could u tell me how in mysql i can create the refrential interity ? and how to relate the tables ? you said :

The biggest limitation is not allowing a particular song on a playlist multiple times

do u me an 2 user can not have same song in their playlist? well if that is the case i do not want it to
be that way. I want 2 user to be able to put same song in their playlist.I hope u be able to help me over come these problems.Furthermore,I already have a table with these feils:
user talbe fields:
ID,userid,userpassword,fullname,email,notes
do i need to change this or keep it as it is ?
Thanks

 

[johnwm]

These forums may not be the best place to get basic database theory. There is an excellent tutorial by Paul Litwin on relational database theory which will answer these questions and provide you with an understanding of the underlying processes. Start with:

http://www.alphabetcitydataworks.com/fundamentals.htm

When you want details of a particular database come back and try the appropriate individual forum, which for MySQL is forum436. You will also need to read up the MySQL documentation from:

http://dev.mysql.com/doc/refman/5.0/en/

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller