SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating play lists schemas

    Hi,

    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

    Code MySQL:
    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?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    what's a playlist?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by YBH305 View Post
    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.
    your tables are fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Nov 2007
    Posts
    120
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure you understand me correctly. Let me give you a scenario:

    1. User clicks on a mp3 to add to his play list.

    2. The user does not have an initial play list so the script recognizes that and creates one for the user and adds the song.

    3. 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)

  8. #8
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •