Page 1 of 1

Database Design Help

Posted: Tue Nov 19, 2002 1:40 pm
by CrazyJimmy
Hello,

Im trying to create a mysql db of a music collection. I've come up with the following table structure

-Artist

Artist_ID,
Artist_Name,

-Album

Album_ID,
Artist_ID,
Album_running_time,
Album_Record_Label,
Album_Format

-Tracks

Album_ID,
Artist_ID,
Track_album_no,
Track_running time


The problem is the collection has alot of double/complication albums and I would want to be able to run a query and list an album with the names of the tracks on each cd of the album. I'm not sure how to alter or add to my structure to do this. Would adding a cd number column to Tracks table be sufficient?

Thanks

J

Posted: Tue Nov 19, 2002 5:56 pm
by mydimension
here's how i would od it: create a field "Album_Num_discs" in the Album table and create a field "Track_Disc_Num" in the Tracks table. then when going through the Album table look at Album_Num_Discs to see if you need to look for more than one disc in the Tracks table. i.e. if Album_Num_Discs is set to 2 then look for all tracks in that album with Track_Disc_Num equal to 1 and 2.