SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2011
    Location
    Trinidad and Tobago
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Database structure for music

    Here is a question I am trying to figure out. If you have a database of music where you store the individual song for each artist how then would you record if the song features someone especially if the person is only a one time entry into the database? note to that the number of features will not be limited to 2 but can be any number of persons.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    with a one-to-many table, so that the features are each a separate row, that way you can have an unlimited number of them
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2011
    Location
    Trinidad and Tobago
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok not sure exactly what you mean. At this time lets say I have 2 tables artist and song where

    Artist table has
    artist_id
    artist_name

    song table has
    song_id
    artist_id
    song_name

    How would the relationship you spoke about work in this case as the artist.artist_id is already one to many with song.artist_id.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    artists
    id
    name

    songs
    id
    name
    artist_id

    songfeats
    song_id
    artist_id
    seq -- optional
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2011
    Location
    Trinidad and Tobago
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is good however what about a feature of an artist not in the database. So the main artist is part of the database but the artist featured is just a one time entry for a track

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by skelleex View Post
    this is good however what about a feature of an artist not in the database. So the main artist is part of the database but the artist featured is just a one time entry for a track
    that's interesting... how do you think you should handle that, in light of the songfeats table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Aug 2011
    Location
    Trinidad and Tobago
    Posts
    194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Am not sure but am looking at it from a real world perspective, where I may have rights to use someones music because they are subscribed to the database. However it is possible they can have a song that features someone who isn't subscribed to the database. So legally I cannot have any info on the featured artist but I can mention that artist as a feature to a song. Thinking about it I maybe able to store them in the database just for data purposes but not have anything about them in the database itself, that sounds like the only viable option.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    now you're thinking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •