Database Design Help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
CrazyJimmy
Forum Commoner
Posts: 34
Joined: Tue Nov 19, 2002 1:40 pm

Database Design Help

Post 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
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post 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.
Post Reply