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?
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?
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)
