Re: A database design challenge!

In article <3udvj8$keb_at_parsifal.nando.net>, <taylorkh_at_nando.net> wrote:
>Here is one for all you database design gurus. (I thought
>that I was rather good at it but this one has me stumped.)
>The diagram below represents part of a sample database
>which I have been using for testing and teaching (FoxBase,
>FoxPro) for several years. Recently I "improved" the
>design to the model below and implemented it in Personal
>Oracle. The relationships are defined as constraints in
>the database to maintain integrity.
 

> ---------------------
> | CDS |
> ------------------ |---------------------|
> | ARTISTS | | cd_key (pri key) |---
> |------------------| |---------------------| |
> | ar_key (pri key) |-------<| cd_ar_key (foreign) | |
> |------------------| | |---------------------| |
> | artist_name | | | other data | |
> |------------------| | --------------------- |
> | other data... | | |
> ------------------ | --------------------- |
> | | TRACKS | |
> | |---------------------| |
> | | tr_key (pri key) | |
> | |---------------------| |
> ----<| tr_ar_key (foreign) | |
> |---------------------| |
> | tr_cd_key (foreign) |>--
> |---------------------|
> | other data |
> ---------------------
 

>A compact disk (table CDS) has one OR MORE artist(s)
>associated with it. In the case of more than one artist,
>the artist name (at the cd level) is set to "VARIOUS". The
>artist data is stored in table ARTISTS and is associated by
>the foreign key cd_ar_key. So far, so good.
 

>A CD will have several tracks (or songs) on it. This data
>is stored in table "TRACKS". A track is related to its
>parent CD by the foreign key tr_cd_key. A track also has an
>artist associated with it. Usually, it is the same artist to
>which the CD is associated. Sometimes the CD contains a
>compilation of songs by various artists.

[rest of posting re. SQL statements deleted]

Ignoring for a moment your SQL question, I think you may have a flawed design. (This is not an unusual error, if it is an error.)

Is it possible for an artist to have some relationship to a CD, OTHER than through a track on that CD?

For example, if I have my Zima Sampler CD, and we agree that Elton John is somehow associated with this CD, then my question is, Must it be true that Elton John is also associated with one or more tracks on this CD?

IF NOT, then your design is okay (but poorly documented).

IF YES, then your design has one too many foreign keys. Specifically, the CD_AR_KEY column should go away.

We could turn this into an extended discussion of generic modelling, and all the ways artists (and recording engineers and groupies and drummers) can have associations with CDs and with tracks, and how to track all those different associations.

But you didn't ask for that. ;-)

 -Tom

-- 
Thomas B. Cox   tcox_at_netcom.com
Author of _Oracle Workgroup Server Handbook_, ISBN 0-07-881186-4