RE: [PHP] A little help needed, I cant figure!
> Indeed, another problem I have is that if a single is on an > album, which track it is on the album. Off hand, I'd say that if you need this information as well (ie. You want to recreate the "songs on album" list in the correct order) then you need to extend one of your tables to include this information. I'd be looking to include album order in the song id, album id lookup table and then using that column for sorting. > I want to centre the site on the Charts that are generated weekly for > singles and albums so if I use that as my source for the > lookup and then > have the data contained in separate fields that are linked to this > table. Will that be making things easier or harder? There's no easy answer to that. Much depends on what information you want to be able to derive from the raw data. If all you need is to be able to drag out weekly charts then your database schema need not be all that complex. If you want to make it possible to cross reference between singles and albums and let people, for example, find out which album a single was on when they are looking at a singles chart then you'll certainly need more information. Ultimately, it's going to be a trade-off between the information you want to be able to provide and the complexity of the database to provide that capability. CYA, Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] A little help needed, I cant figure!
Indeed, another problem I have is that if a single is on an album, which track it is on the album. I want to centre the site on the Charts that are generated weekly for singles and albums so if I use that as my source for the lookup and then have the data contained in separate fields that are linked to this table. Will that be making things easier or harder? -Original Message- From: David Freeman [mailto:[EMAIL PROTECTED]] Sent: 13 October 2002 22:12 To: [EMAIL PROTECTED] Subject: RE: [PHP] A little help needed, I cant figure! > >Song Table: > >Song_id > >A_id > >Type > >Name > >Length > >Lyrics > >... > > > >That's my ideas on how to do it. There are many ways to do > it, though. > > > Totally agree with what John says. If the lyrics are long > (as I assume > they will be) then I'd probably hive the lyrics off into a table of > their own with as many records as needed to make up one > song. The song > table would then become: > > Song Table: > Song_id > A_id > Type > Name > Length > Lyric_id > > and have a lyrics table such as: > > Lyrics Table: > Lyric_id > sequence > text Hmm, just thinking about normalizing the data and I suspect that the song table will be insufficient if you have to take into account songs that are released as a single and then also released on an album. You may find, in that circumstance, that having lookup tables will help. One of these tables will contain artist ID's and associated song ID's and enable you to get singles for an artist. The other table would contain album ID's and song ID's and enable you to get songs for an album. Obviously, you can get much more complex queries going as well such as the number of times a particular song has been included on an album by an artist. This addition would also help with songs on compilation albums by multiple artists where you could drill down to a list of songs on a compilation album complete with the artist for each song. CYA, Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] A little help needed, I cant figure!
> >Song Table: > >Song_id > >A_id > >Type > >Name > >Length > >Lyrics > >... > > > >That's my ideas on how to do it. There are many ways to do > it, though. > > > Totally agree with what John says. If the lyrics are long > (as I assume > they will be) then I'd probably hive the lyrics off into a table of > their own with as many records as needed to make up one > song. The song > table would then become: > > Song Table: > Song_id > A_id > Type > Name > Length > Lyric_id > > and have a lyrics table such as: > > Lyrics Table: > Lyric_id > sequence > text Hmm, just thinking about normalizing the data and I suspect that the song table will be insufficient if you have to take into account songs that are released as a single and then also released on an album. You may find, in that circumstance, that having lookup tables will help. One of these tables will contain artist ID's and associated song ID's and enable you to get singles for an artist. The other table would contain album ID's and song ID's and enable you to get songs for an album. Obviously, you can get much more complex queries going as well such as the number of times a particular song has been included on an album by an artist. This addition would also help with songs on compilation albums by multiple artists where you could drill down to a list of songs on a compilation album complete with the artist for each song. CYA, Dave -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] A little help needed, I cant figure!
John W. Holmes wrote: >Song Table: >Song_id >A_id >Type >Name >Length >Lyrics >... > >That's my ideas on how to do it. There are many ways to do it, though. > Totally agree with what John says. If the lyrics are long (as I assume they will be) then I'd probably hive the lyrics off into a table of their own with as many records as needed to make up one song. The song table would then become: Song Table: Song_id A_id Type Name Length Lyric_id and have a lyrics table such as: Lyrics Table: Lyric_id sequence text The sequence would be an incrementing number (starting at 1 for each new lyric_id), so you could have many records per song. Index this table on lyric_id, sequence. Make sure in your code that you maintain integrity between tables. That is, if you delete a record from the artists table, ensure you delete relevant records from the others too. Just my 2p (I'm in the UK, so not even EuroZone, yet). HTH Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] A little help needed, I cant figure!
[snip] > For example I intend to have over 4,000 different charts covering well > over 6,000 artists and over 26,000 singles and album titles. Could mySQL > and PHP cope with this demand? Yes, easily. Make sure your tables are properly indexed. > I thought about having a table for Artists, one for Singles and then one > for Albums and a junction table coupling all of them together but I > can't figure out how to do it. > > Has anybody got any thoughts that could help me along the way? Table one would be the artists. Here you could store their name, age, etc. They would be assigned an ID to relate to their albums and songs. Artists Table: Artist_id Name Age Hometown ... Table two would be an album table where you would list the albums name, release date, etc. It would be related back to the artist of the album by the artist_id. You wouldn't have to store number of songs here, because you can count them out of the next table. Album Table: Album_id Artist_id Name Release_date ... This table would hold the songs off the albums and the singles. You would use type to designate if the song is a single or off an album. If it's off an album, then a_id would be the album_id that relates back to the album and artist. If it's a single, then a_id would be the artist_id of who had the single. Song Table: Song_id A_id Type Name Length Lyrics ... That's my ideas on how to do it. There are many ways to do it, though. ---John Holmes... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
