Posted 15 August 2010 - 06:45 PM
The idea that you propose in having a single table that lists the CD Name (Album Name) and the tracks together as a single line item isn't a very effective method of making that database for exactly the reasons you raised. Rather than having them in one table, split them up and relate them.
The way I would go about doing this is...
Table 1: Albums
Fields: Album Name, Artist, Year, Genre, Whatever other fields you might add
Primary Key: Either make it a composite key with Album Name and Artist or create an additional field which contains a unique identifier such as a Number. I would suggest creating a unique field as the primary key as it is easier to work with.
Table 2: Tracks
Fields: Song Title, Artist, Album, Track Number
Primary Key: I would go with Song Title and Artist as a composite Primary Key.
Table 3: Artists (If you so choose)
Fields: Artist, any other info about the artist or group you wish to include.
Primary Key: Artist Name
The important thing is the relationships between these three tables.
Albums Table is related to Tracks Table in a One to Many Relationship (One album has Many songs).
Albums Table is also related to the Artist Table in a One to Many Relationship as well (One Artist may have Many Albums).
You need to determine what you want to have as your primary keys which will determine what the foreign keys will be in the related tables. For example, if in the Albums Table you use the Name and Artist as the Primary Key, then these fields would be related to the corresponding fields in the Tracks Table.
You don't have to follow my model exactly, but do you understand the idea of splitting up the Tracks and Album Names fields into different tables and relating them? This eliminates the problem of constantly having to add fields for new tracks. It'll also make queries significantly easier to sculpt.
Hope this helps
I like to think I know a bit about computers, I apologize if my advice is not sound or clear. I try my best to give good answers.
Line of Defense
aVast! | Comodo | Malwarebytes