1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Help with Music Database Relationships

Discussion in 'Databases' started by Kenold, Oct 24, 2015.

  1. #1
    I need help with many-to-many relationships for a small music database project. Here are the main entities: albums, artists, tracks, genres, labels, videos

    [Artists] artist_id, type_id, name
    [Albums] album_id, title, genre_id, cover_art, label_id, release_date
    [Artists_Albums] album_id, artist_id
    [Tracks] track_id, title,album_id, position, length, video_id, lyrics
    [Artist_Tracks] album_id, track_id, artist_id, credit_id
    [Credits] credit_id, position
    [Genres] genre_id, name
    [Labels] label_id, name
    [Videos] video_id, title, track_id

    - an album can have one or more artists
    - a track can have one or more artists with a join keyword (ex: "Feat", ",", "&") Katy Perry Feat. Kanye West, Rihanna OR Rihanna & Kanye West & Paul McCartney. I'm not sure where to add the join keyword in the table
    - a track can have one more more videos / a video belongs to a track
    - a track can have one or more credits (Ex. Producer, Writer)

    Here's a link to the tables side by side http://prntscr.com/8uutq0
    Any help would be appreciated
    SEMrush
     

    Attached Files:

    Solved! View solution.
    Last edited: Oct 24, 2015
    Kenold, Oct 24, 2015 IP
    SEMrush
  2. #2
    Hi,

    You don't need a join keyword. the fact that you have Artists_Albums is enough. This means that you can have the following 2 records

    Album1, Kanye West
    Album1, Katy Perry

    This would mean that album1 has two artists.

    In order to query this database, you'll just perform something like this:

    select * from Albums inner join Artists_Albums on Albums.album_id = Artists_Albums.album_id inner join Artists on Artists_Albums.artist_id = Artists.artist_id where Albums.title = 'Album1';

    Result= 2 records => Album1, Kanye West + Album1, Katy Perry

    Hope this helps,


    Please like my answer if it helps you. Regards,
     
    neutralhatter, Nov 7, 2015 IP
  3. Kenold

    Kenold Greenhorn

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    Kenold, Nov 10, 2015 IP