Re: candidate keys in abstract parent relations

David Portas wrote:
> Yes it's a bad idea...

Thank you for your reply, which helped me better understand why adding an artificial key fails to improve a relation without a natural key.

> The candidate key is defined by a subset of the attributes, so the real
> question is: what attributes do you need to model about an entity? If
> you aren't sure what the key is then you probably haven't answered that
> question properly.

Yes. This is where I've been blocked for several weeks now. I guess the simplest way of stating it is, is there value to separating an Album from a Release? Does it make sense to think about an Album as some sort of Platonic ideal entity that exists independent from a release?

Leaving aside the question of normalization / repeated groups, my intuition tells me that if multiple releases share the same title and the same track listing, they're the same album released in different contexts. This is the nugget of domain insight I'm trying to model by splitting apart Releases, Albums, Tracks, and (musical) Pieces. However, the difficulty I'm having in coming up with a candidate key for the Album relation indicates to me that, as Roy Hann suggests, my brain is stubbornly holding on to an OO worldview.

> Now let's reconsider your example. You define an album by the tracks
> that it contains. There may be more than one album with the same name
> and in fact with all other attributes in common and those albums will
> be distinguishable only by the fact that they represent a different
> collection of tracks. Your mistake though is to identify a one-to-one
> correspondence between rows in your Album table (information about an
> album) and the album itself (a collection of tracks). Those are two
> different things. The missing piece in your model is a table or
> attribute(s) to supply the information that relates the two entities. I
> would have expected to see a "joining" table to represent the concept
> of a many-to-many relationship between the Album table and the Tracks
> table.

Interesting! So at that point the candidate key for the Album relation becomes Album(primary performer, name), and I create a new relation that joins Albums and Tracks (any suggestions for a name for that association?) and a uniqueness constraint on the Track, because no Track can be on more than one album: in the model, the Track is an association between a specific album and a Recording, and in the real world tracks have different durations even if they use the same recording.

The only problem at that point is, how do I tie a specific version of an Album to a Release? I could create a new AlbumVersion relation that ties an Album to a set of Rows in this new AlbumTracks association (for want of a better name), and then the Release is associated with an AlbumVersion, such that the set of candidate keys over the relations is

Album(primary performer, name)
AlbumVersion(album, version)
Tracks(album version, primary performer, name) Release(album version, label, release date, locale, ...)

Does that make sense? "version" is an arbitrary sequence, but not, I think, an artificial one.

Thanks for your insight. Received on Thu Jan 19 2006 - 20:54:06 CET