Re: candidate keys in abstract parent relations

David Cressey wrote:
> "Forrest L Norvell" <spankysyourpal_at_gmail.com> wrote in message
> news:1137556243.101621.119250_at_o13g2000cwo.googlegroups.com...
>
> > 4. The hard part: An Album is a collection of Tracks by a Credited
> > Performer with a Title. The difficulty comes in because I'm trying to
> > be scrupulous, and defining uniqueness on those attributes is proving
> > to be very difficult.
>
> This surprises me, although maybe it's just my ignorance of the subject
> matter.
>
> If some Label released an Album entitled "Billboard Hits of the Sixties", I
> would expect it to be an anthology of tracks drawn from the works of
> different performers.

Yes, and I apologize for the ambiguity in what I wrote. Every Album has a Primary Performer, although often that Primary Performer is "{various artists}". Each Performance has a Primary Performer as well. Tracks do not have Primary Performers defined on them. Dealing with soundtracks and compilations correctly is a lot harder than it looks at first, because even "single-artist" Albums often have tracks with different Primary Performers. My application is designed not to display the artist for a track if the Primary Performer for the Album and the Primary Performer for the Performance match. If you think about it for a little while, you can see how this is a flexible and general model.

> I'd also expect the title to be ambiguous, having been used by more than one
> Label, for an entirely distinct set of tracks.

Remember, Labels are tied to Releases, not Albums. Swans released _The Great Annihilator_ in identical formats on two different labels, and then in a slightly different form on a third. When the title is ambiguous is when the same (name, primary performer) tuple is used for multiple collections of tracks. In my present schema, I've disambituated this by creating a new relation AlbumVersion, which has a 'version' property on it defined such that AlbumVersion(name, primary performer, version) is guaranteed to be unique (even though I'm using a surrogate key to point from Album to AlbumVersion, so the actual natural key is AlbumVersion(album id, version).

Just in case anyone's curious, the 'version' attribute is a VARCHAR with the default set to 'original version'. I'm all for conventions and meaningful attributes.

> Identity is much more subtle than it appears at first.

Yes. :) Received on Fri Jan 20 2006 - 21:27:46 CET