Correct setup for my tables??

[Iwra]

I am looking for a little bit of advice for tracking my mp3 collection. so far I have set up the following tables in my database

tblArtists
ArtistID PK
ArtistName

tblTracks
TrackID PK
TrackTitle

tblArtistTrack
ArtistID
TrackID

Compund Key

I have set this up as a many to many relationship as 1 artist can perform many different song titles but one song can be covered by many different artists. Based on this would my current set up be correct? Eventually I will want to be able to link this to the track itself so a variety of different searches can be pulled upon artist and or song title and a link to the song or to include a playlist and open the music program to do this.

I also want to include a category table ie rock, pop etc. Again this would probably be many to many as 1 track can fall under many different music categorys.

Just wanted a second opinion on the above setup before I go any further with the development.

Thanks

 

[The_Doc_Man]

This does appear to be the correct method of setting up many-to-many mappings, which surely applies to the case you described.

In my own version of this, I have more than one table, but a strange set of attributes. The CD itself has an autonumber assigned to each table record. This differs from the number that appears on the spine and often on the CD label as a serial number. Since serial numbers are unique only within a given company, I had to build the autonumber to keep it all unique.

Don't do it this way, but I have a separate table that lists attributes of the tracks of the CD. So my second-level key is CD number/track number. That combo is unique to any track, but that's not how I use it. I added a third code, which is "Attribute." Attributes include things like "Track title (song title)" "Composer" "Lyricist" "Group name" "Featured Artist" "Featured Instrument" "Cover Art" "Conductor" (got lots of classical music) etc. etc.

Then, when I want to pull stuff out, I select from the attribute table where the attribute code is specific to what I'm seeking. So I have a query for all track titles. A query for all composers. A query for conductors. If I want to find everything by, say, Michael Jackson, I can query for Featured Artist. MJ will be there following Allen Jackson and Mahalia Jackson, but before Carole King.

Probably, the cover art belongs with the CD record itself, but I was doing this at a time when I didn't necessarily care about normalization that much. So this isn't perfectly normalized. (Yeah, even I can be lax about DB design sometimes.) The query allows an empty track number when the same attribute applies to all tracks on the disk, so I still have a way to know that it is a single-artist CD or a single-instrument CD (I've got a lot of pipe organ stuff, rumbles the walls when I crank it up.)

If I ever got around to redoing this DB, I would probably take your approach. But actually, I imported some of this from another DB where I didn't have quite the luxury of strict normalization that I do with Access. Also, now I care more about structure than I did at the time I first built this.