A database design challenge!
Date: 1995/07/17
Message-ID: <3udvj8$keb_at_parsifal.nando.net>#1/1
| 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