Re: A database design challenge!
Date: 1995/07/20
Message-ID: <3umj3h$45o_at_toon.ctp.com>#1/1
In article <3udvj8$keb_at_parsifal.nando.net>, taylorkh_at_nando.net writes:
<Neat ASCII ER diagram deleted...>
...
|> 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.
|>
<Analysis stuff deleted...>
|> 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.
My opinion is that your data model should capture your real-life rules (business rules). If your current requirements dictate that an artist is an attribute of the CD itself and also that of a specific track then that is what you should design to. Another application might have a more complicated relationship between artists, CDs and tracks. For example, you could have an album such as Peter Gabriel's "So!" which features a track which is a duet with Kate Bush. The current model does not support this. But that may not be a problem depending on the specific application of the data.
In this particular case I don't see a problem in designing it the way it has been since it does not violate any basic principles. Your second SQL statement is exactly what you need to write to get the correct information to the front-end. The meaning of the table "Artists" is different for the table "CDs" and different for the table "Tracks". The fact that one is the child of the other is outside of the scope of the "Artists" table.
I also want to comment on the statement "It appears that Oracle does not like multiple relations into the same table (artists)." Oracle by itself is just DBMS and it does not care which tables reference which ones (mostly). Hence your design is so important!
Pavan.
Pavan V. Muzumdar
Cambridge Technology Partners, Cambridge, MA 02139. USA. Tel: (617) 374-9800 Fax: (617) 374-8300 E-mail: pmuzu_at_ctp.com Received on Thu Jul 20 1995 - 00:00:00 CEST