Re: A database design challenge!

No, you haven't made a mistake. Ignoring the special handling of "various", this is a properly normalized design.

-JJ

 Jeffrey Jacobs & Associates
 Oracle CASE Consulting and Training
 951-2 Old Country Road, Suite 119
 Belmont, CA 94002
 Voice: 415-571-7092
 FAX: 800-665-1379
 CompuServe: 76702,456
 Internet: jjacobs_at_well.com
 Internet: jmjacobs_at_ix.netcom.com  

In <3udvj8$keb_at_parsifal.nando.net> taylorkh_at_nando.net writes:
>
>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.
>
>Originally I maintained a separate table for tracks from
>"VARIOUS" artist CDS and handled the situation with in the
>FoxPro application. I have now normalized "artist" to its own
>table and use this table for both cds.(artist) and tracks.
>(artist). The problem occurs when I attempt to execute a SQL
>such as:
>
>select cds.*, artists.ar_name, tracks.*
> from cds, artists, tracks
> where cds.cd_ar_key = ar_ar_key
> and tracks.tr_cd_key = cds.cd_key
> and tracks.tr_ar_key = artists.ar_key
>
>It appears that Oracle does not like multiple relations
>into the same table (artists). All of the "VARIOUS" artist
>cds (where the artist at the cd level is different from the
>artist at the track level) are omitted from the SQL output.
>Upon reflection, this seems reasonable.
>
>If I change the SQL thus:
>
>select cds.*, artists.ar_name, tracks.*, artists.ar_name
> from cds, artists, tracks, artists artists_a1
> where cds.cd_ar_key = ar_ar_key
> and tracks.tr_cd_key = cds.cd_key
> and tracks.tr_ar_key = artists_a1.ar_key
>
>I get the desired result. The artist at the cd level is
>supplied by artists and the artist at the track level is
>supplied by artists_a1.
>
>Bottom line - it works but, have I made a stategic error
>in referencing artists by two related tables (cds and
>tracks)??? Your thoughts would be greatly appreciated.
>
>Ken Taylor
>Carolina Power & Light Co.
>Raleigh, NC
>
Received on Mon Jul 17 1995 - 00:00:00 CEST