SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2001
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    need some database design advice

    I'm working on building a database for a web site that catalogues biographies and discographies for old bands. Now, most of it is pretty straightforward... I have a "bands" table that contains all of the bio information for the groups, an "albums" table that contains information about each record like catalogue number, format, etc. (cross-referenced, of course, with the bands table), and a "tracks" table that contains track numbers and song titles (cross-referenced with both the bands and albums tables).

    I have a working prototype running, but I've run into a problem: compilation albums.

    There is a field in the "albums" table indicating whether the album is a compilation or a single-band release, but I'm running into a problem on the band pages. Each band page contains a discography section where I pull all of the single-band releases whose band ID number matches the band ID number whose information is being displayed. However, how am I supposed to list all of the compilations they are a part of?

    I've thought of two solutions:

    1. Have a field called "band_ids" in the albums table, and just list all of the id numbers of all of the bands on the comps, then have the band page search to see if the proper id number exists anywhere in that field. However, I don't think this would work because if the band's ID number was 2, then it would not only pull the compilations that band 2 is on, it would also pull compilations where band 22, 32, 302, etc., were on.

    2. Rather than looking for the band id in the albums table, look for the band id in the tracks table, then use the indexed album id to figure out what album it came from. The only problem with this is that I would have to find some way to eliminate all of the duplicate albums, because some bands have several cuts on a compilation and I don't want that compilation to show up multiple times in the index. The main problem with this is that I'm not familiar with such a way to eliminate duplicate (or, I guess in this case, similar) records from a query. However, if it's not insanely complicated I'm definitely willing to learn.

    Anyone have any advice? If you need me to explain anymore just post and I'll do my best.

    Thanks,
    Daniel

    http://www.dfbpunk.com

  2. #2
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You don't say what kind of database that you're working with. That would be a definite factor is just how you can display your information.

    I have a music database very similar to what you're talking about. Basically, what you have is AlbumArtist, where the artist is the same for the whole album, and TrackArtist, where each individual track could each be by a different artist. Both can be referenced by an ArtistID if you wish, but you still have to spin through artist and tracks tables for searches by artist.

    The biggest problem with that is that some databases, like PHP 4.x for example, don't allow for sub-queries so that you can end up with an unduplicated album list. I haven't taken the time to figure out how to get around that one.

    I suppose what you could do is query the database for artists, albums and tracks where either album artist or track artist had a certain value, then load each unique artist and album into an array prior to processing the results. Something along that line, anyway.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  3. #3
    SitePoint Member
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The best way I can think to do this is using more tables. Make an Artists, Albums and Tracks table. Then create an ArtistAlbum table to link an artist to an album. This woudl allow you to link multiple Artists to the same Album. The Album table itslef would have no link to the Artists table, you would have to join the ArtistAlbum table when doing a query. The Tracks table should also have no direct relation to the Artists table, you would get the teack info by doing joins again.

    Keith

    PS. Where do I get the PHP 4.x database, I think you meant to say MySQL 4.x

  4. #4
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kcomer
    PS. Where do I get the PHP 4.x database, I think you meant to say MySQL 4.x
    Yep, I meant MySQL. It was getting late and I was tired when I posted that.

    You don't need more tables. You just need to do joins with the existing tables. For example, here's my table structure:

    tblArtists - ArtistID key, also contains AlbumID, ArtistName
    tblAlbums - AlbumID key, also contains ArtistID
    tblTracks - AlbumID key, also contains ArtistFullName

    Here's a sample query:
    Code:
    SELECT tblArtists.ArtistID,
    tblArtists.The, 
    tblArtists.Artist, 
    tblArtists.SortName,
    tblAlbums.ArtistID, 
    tblAlbums.Title,
    tblAlbums.AlbumID, 
    tblTracks.ArtistID,
    tblTracks.Title,
    tblTracks.ArtistFullName,
    FROM (tblTracks 
    INNER JOIN tblArtists ON tblTracks.ArtistID = tblArtists.ArtistID) 
    INNER JOIN tblAlbums ON (tblArtists.ArtistID = tblAlbums.ArtistID) 
    AND (tblTracks.AlbumID = tblAlbums.AlbumID) 
    WHERE 
    (tblArtists.Artist LIKE '%Willie Nelson%'
    OR tblTracks.ArtistFullName LIKE '%Willie Nelson%')
    As you can see, there are only three tables necessary. Just do your SQL joins to get everything you need.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  5. #5
    SitePoint Member
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My goal was to move the database towards a normalized state, as much as possible. If you go with vinyl-junkie's solution ti wil work but you will be storing the same data in 2 different places. This means all your selects, inserts, updates and deletes will need to access the same data twice. Some people think it's overkill to try and normalize small databases or very simple applications but it's a nice habit to try and force yourself into. Also, what happens if you want a single track to be linekd to 2 artists, or maybe even 3? Why start your application with such limits even if you don't think you'll hit them at first? Using tables as links between the artists, albums and tracks will allow a limitless setup of relationships between all 3.
    Quote Originally Posted by vinyl-junkie
    tblArtists - ArtistID key, also contains AlbumID, ArtistName
    tblAlbums - AlbumID key, also contains ArtistID
    tblTracks - AlbumID key, also contains ArtistFullName
    Why would the tblArtists table have AlbumID in it? You are assuming that an artist only has one album then, unless you are entering duplicate records into the artists table for each album?

    Maybe I am seeign this all wrong though.

  6. #6
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kcomer
    My goal was to move the database towards a normalized state, as much as possible. If you go with vinyl-junkie's solution ti wil work but you will be storing the same data in 2 different places. This means all your selects, inserts, updates and deletes will need to access the same data twice.
    Yes, but minimizing data redundance in the process. Wouldn't you still like to know which track is associated with which artist?
    Some people think it's overkill to try and normalize small databases or very simple applications but it's a nice habit to try and force yourself into. Also, what happens if you want a single track to be linekd to 2 artists, or maybe even 3? Why start your application with such limits even if you don't think you'll hit them at first? Using tables as links between the artists, albums and tracks will allow a limitless setup of relationships between all 3.
    When you say normalize, do you mean you're building an array into each record for artist?
    Why would the tblArtists table have AlbumID in it? You are assuming that an artist only has one album then, unless you are entering duplicate records into the artists table for each album?

    Maybe I am seeign this all wrong though.
    You're seeing this wrong. If an artist (with say, ArtistID=34) has three albums (AlbumID's = 25, 37, 40) in my database, for example, there would be three records in tblArtists, with:

    34 25
    34 37
    34 40

    You can't get away from data redundance, unfortunately, but I believe my way produces the minimum amount.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  7. #7
    SitePoint Member
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I got you. But you'll have another table, maybe called tblArtistsInfo that has the name of the artist and maybe some other info, because if you just add that info to the tblArtists table you are duplicating alot of data.

  8. #8
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,526
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Um, actually with my database, the artist info can go in tblArtists. I don't know what I was thinking when I posted earlier (messed up again!), but I have only one record per artist in tblArtists. It's tblAlbums that has the structure I mentioned earlier. Doh!

    Also, something I should have mentioned initially is that my (MS Access) database is a back-end to some desktop software I use for managing my music collection, so I don't have a lot of control over the structure.

    That said, I have converted that Access database to MySQL for my website and added another table to it for song lyrics. The table structure for the desktop version requires that the lyrics be input into each track record where that song occurs. What a waste! Anyway, I'm having fun customizing a Web version of my database.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web


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
  •