Question on normalisation and best practice

All,

As noted on other questions I am building a music database for my own purposes. Thanks to the myriad questions and answers on here it is coming along nicely and fits my needs well. However, I want to check one issue that is niggling at me. Consider the following:

tblSongs
SongID
SongName...

tblTracks
TrackID
SongID...

tblRoles
RoleID
RoleName...

tblMusicians
MusicianID
MusicianName...

jtblComposers
SongID
MusicianID

jtblMusicianTracks
TrackID
MusicianID
RoleID

I am using a junction table for Composer information for each Song. Separately I am using another junction table for Musician/Role information for each Track. So Bob Dylan could compose a Song using jtblComposers and be the Vocals on many Tracks using jtblMusicanTracks.

However, I could get rid of the Composer junction table and add the Role:Composer in the jtblMusicianTracks instead. Queries could extract Composer information using criteria as required although this is a little more work than simply using the jtblComposers.

My current approach seems to work for me but is there a good reason to adopt the alternative approach? I want to avoid problems up the line. For information, the same approach is used for Track Artists (separate junction table between TrackID and MusicianID).

Any thoughts would be appreciated. I have tried to follow sensible practice on normalisation but this is still new territory for me.

Composer is a role. No need for jtblComposers.

Also, what's the difference between a track and a song? Looks like 1 song can belong to multiple tracks. Additionally, this makes it seem like you have a circuitous/duplicatous relationship among your tables:

tblTracks:
Track-Song

jtblComposers
Song-Musicision

jtblMusicianTracks
Musician-Track

At most only 2 of those relationships should exist.

Thanks Plog for the quick reply.

A Track is a physical track on 1 CD in my collection. A Song is a composition which will be on multiple tracks in theory. A Track can also have multiple Songs (e.g. a medley!)

The second point will be addressed if I remove the jtblComposers as you suggest.

Thinking about it further though since a Song is a separate entity from a Track (and one Song will be on multiple Tracks) should I not keep a junction table for Composer separate from that for Tracks and Musicians who play on them? A Song and its Composer is entered once; Tracks and Musicians are entered many times (for different CDs).


Need to sleep on this!

So a musician should not be associated with a track. Instead a musician should be associated with a song. So jtblMusicianTracks goes away, or better still becomes:

jtblMusicianSongs
SongID
MusicianID
RoleID

Hi again and thanks. I don't agree but let me see if we are coming from the same place:

A Song is a one-off. Hey Jude was written by Lennon/McCartney. Ignoring the possibility of other songs with the same title (which we can deal with separately) it is unique. Certainly Hey Jude by Lennon/McCartney is one of a kind.

Various Musicians played on the original Beatles Track. But others played on different versions (e.g. Elvis Presley's version). By associating Musicians with Tracks and Roles (via a junction table) I deal with that. Associating a Song with Musicians would not work as the exact same Song appears on different albums (Tracks) but with a different group of Musicians.

What am I missing do you think? Thanks.

Yes it makes sense now and forget everything I've said. Your initial posting is correct. jtblComposers should remain as you have it.

Thanks plog for your thoughts and acting as a sounding board. Much appreciated. I will stick with the current approach*.

* until I break the database and come here in need of immediate help.

Irish lad said:

* until I break the database and come here in need of immediate help.

Click to expand...

At least you are in luck to have some folks on here very literate in music and how it is marketed. I remember the first time I had to explain the difference between a track and a song to someone. They just couldn't understand that "Proud Mary" was sung by different artists at different times, and that the same song sung by the same artist at a different point in their career would be a different "Track".

Best example I can think of today is from the Lego Movie. They use the same song repeatedly in the same movie, but do it in different styles (hence different tracks).

They use the same song repeatedly in the same movie, but do it in different styles (hence different tracks).

Click to expand...

Or, for that matter, consider the main theme of The Elder Scrolls III, IV, and V. Same tune, totally different motifs.

But on this direct question:

I had a CD collection and tried to organize it. My structure said something like this:

CD table was the top of the hierarchy. CDs had cover descriptions as part of the CD table. Also had label (I.e. Motown, Columbia, RCA, ... pick your favorite label) and CD serial number (=that publisher's catalog number). Also had descriptions of CD cover art. PK was autonumbered because (a) publishers used different formats for their numbering and (b) sometimes it wasn't so easy to find the catalog number because of inconsistent ways of publishing it.

Tracks table was the Child of the CD table. Tracks had individual playing times as part of the Tracks table. Key was compound: CD autonumber as FK and ordinal track number on the CD, numbering from 1 to whatever.

Tracks had lists of attributes, which were ALL in the same Attributes table.
Attributes were things like Song, Composer, Genre, Performing Players or Group. HOWEVER, each attribute had a number, so if the track held a medley, the member elements were numbered. The attributes allowed me to list both a BAND name and a SOLOIST name. (Like Huey Lewis and the News or Frankie Valli and the Four Seasons or whatever.) The Attributes table had a code to go with the track's compound PK and the Medley Ordinal to form a four-field PK. That code was translated via a Roles table that could be anything from the artist to the composer to the orchestra to the orchestra's conductor to the lyricist and librettist... you name it.

If I wanted a report, I had a secondary code in the Roles table so that artist names would all come out together whether we were talking soloist, band name, accompanist, etc. Since I could back-query titles and such, no issue.

When Hurricane Katrina messed up the CD collection due to mold affecting any of the CDs that weren't properly sealed, I kind of lost interest in maintaining that database.

@ Doc,

In the music industry, you will find that the same track can appear on more than one album. CCR's Proud Mary appears on more than one album where the source track is the same. Under your scheme, you would consider it two different tracks even though it is one recording.

When tracking air play you go by track, not album. Had to do this a long time ago for Accelerated Chart Movements. Really neat stuff.

Just make sure that you don't create a query that joins tblTracks, jtblComposers, and jtblMusicianTracks. This will create a Cartesian Product because Composers and Musicians are each related to Tracks but not to each other. You won't see a problem as long as a song has only a single composer but you'll see the duplication as soon as you have more than one composer sharing credit for a song. To present this data on a form or report, you will need to use subforms/subreports.

Mark_ - that happened more than once and if it DID, I listed it twice. Sometimes I added my own comment "(reprise)" to the song name for the 2nd track. Never personally ran into a second reprise so never had to deal with that.

Doc, I take it you don't have many "Best of" albums?

IIRC, you can have three or four albums with the same track if the artist is well known and they've done multiple "Compilation" albums. Think this was mostly Elvis though.

Mark_ - actually, I do have a few "Best Of" albums. But because the CD ID and the Track Number were part of a compound PK (for the track table), it didn't matter HOW many times the track repeated across multiple CDs. AND it didn't matter how many times in the attribute table, because THAT table had a four-way compound: CD, Track, Attribute Code, and a "uniqueness" number (for medley cases).

I will link these two since related
https://www.access-programmers.co.uk/forums/showthread.php?t=300727

I also think to make this work you need another junction table.
jncTblPerformerGroup
MusicianID
GroupID

This is where you put all the Musicians into one or more bands.

Paul McCartney Beatles
Paul McCartney Wings
George Harrison Beatles
....
Johnny Ramone Ramones
Dee Dee Ramone Ramones
...

Then when you are assigning performers to tracks you could select from a specific group or select from all performers. If I am doing a Beatles album I would like my selections narrowed to Paul, George, Ringo, John. So when you add a performer to the DB you can add them to one or more groups. If solo add them to themselves.

Good luck on your project.

murphybridget said:

Good luck on your project.

Click to expand...

You DO realize you re-opened a thread over 5 years old AFTER it had gone dormant, don't you?