I've been thinking all day of how to create a play list that will avoid duplicating data.
This is what I can make up with
CREATE TABLE `play_lists` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`userID` INT NOT NULL,
`name` VARCHAR(255) NOT NULL,
`date` DATETIME NOT NULL
FOREIGN KEY userID(userID) REFERENCES users(id) ON DELETE CASCADE
) ENGINE = innoDB;
CREATE TABLE `play_listSongs` (
`id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`play_listID` int(11) NOT NULL,
`songID` int(11) NOT NULL,
`date` datetime NOT NULL,
FOREIGN KEY play_listID(play_listID) REFERENCES play_lists(id) ON DELETE CASCADE,
FOREIGN KEY songID(songID) REFERENCES uploaded_songs(uploaded_songID) ON DELETE CASCADE
) ENGINE = innoDB;
The problem is that my friend says that he also needs a repository of all songs that are in the play lists.
The problem is that, if a user does not create an initial play list than the above tables will not work correctly. Also, if the user inserts a song into a play list then the repository would not have that new song that was inserted.
His method would be to create a table that stores ALL your songs that you have and also the tables I created and reference them to each other.
The problem is that the repository table would contain ALL your songs AND the play_listSongs table would also contain all your songs.
Is there another method to meet his needs that I am missing out on?
What hes trying to create is an mp3 play list, like iTunes.
In iTunes you have a repository of all your songs. Then you can create different play lists to store only certain songs.
That's what I'm trying to help me setup DB wise. But It's a lot of duplication of data his way.
okay, i don't do itunes and i don't do playlists, but the general principle of databases is "one fact in one place"
surely you people aren't thinking that an entire mp3 file has to be copied from a central repository into some other location to constitute a playlist?
each song would be stored only once, yeah?
I believe (if I remember correctly) that he had a table for the songs.
So I guess that makes it the repository of all songs, whether a user has selected a particular song to be in a play list.
But that wouldn't solve the problem. Or am I missing something and you're trying to hint it at me? :shifty:
your tables are fine
I'm not sure you understand me correctly. Let me give you a scenario:
User clicks on a mp3 to add to his play list.
The user does not have an initial play list so the script recognizes that and creates one for the user and adds the song.
The user finds another mp3 and decides to create a brand new play list and store it there. *this becomes a problem because the song is now available within that play list but not in the user's repository of all songs. So that means I would also need to copy over that row into another table possible called song_library or something like that and hold the same information.
In essence the play_listSongs and the song_library table will both have the same information (song id and the user id) and both reference the songs table (where all the information is stored such as the URL where the mp3 is stored, song name, etc)
If a user should be able to have a song in their own specific library, but not in any of their own playlists, then you need a user_library table.
If a users library consists of only songs that are currently in one or more of their playlists, then it's fine how it is.