Re: candidate keys in abstract parent relations


Forrest L Norvell wrote:
> Is there a method to generate a primary key based on entities that are
> children of a parent relation (and if there is, is it all a good idea
> to use it?)? Is the Album an overly-abstract construct that should be
> pushed into the Tracks and Releases? Or am I being overly finicky and
> should I just write a sequence generator / use an autoincrementing
> column? In the real world, labels issue the same Album at different
> times on different formats in different Locales that are struck from
> the same masters with the same name (but as separate Releases) and the
> same Recordings on the Release, and I think most people would
> intuitively understand the relationships as I've sketched them out
> above, as well as intuitively understanding that two albums with the
> same title and artist can, on occasion, contain different Tracks, and
> therefore do not share the same identity.
>
> Any hints on what I should look at next?

In a nutshell, you're being overly finicky. Why are you so afraid of
an auto-incrmenting surrogate key column? They makes joins cleaner,
faster, and easier. In general, they makes things more legible.
There's nothing wrong with defining additional unique constraints on
your data either, even when using a surrogate key. I think you'll find
in practice that it's quite common.

.