NetNews Usenet Archive 1992 #27/NN_1992_27.iso/spool/comp/database/oracle/2255

Internet Message Format | 1992-11-19 | 2.8 KB

Xref: sparky comp.databases.oracle:2255 comp.databases.sybase:364
Newsgroups: comp.databases.oracle,comp.databases.sybase
Path: sparky!uunet!gator!rde!andy
From: [e-mail] (Andy Finkenstadt)
Subject: Re: Table Design Hint needed
Message-ID: <[e-mail]>
Reply-To: [e-mail]
Organization: Vista-Chrome Incorporated
References: <[e-mail]>
Date: Thu, 19 Nov 1992 17:28:34 GMT
Lines: 69


Without duplication of data, the many-to-many relationship is best
expressed as an intersection table:

create table DIRECTORS (
  pk_director number not null primary key,
  dir_name char(40) not null
) ;
create table BUSINESSES (
  pk_business number not null primary key,
  bus_name char(40) not null
) ;
create table BUS_DIR_XREF (
  fk_business number not null references BUSINESSES,
  fk_director number not null references DIRECTORS,
) ;
create unique index pk_bus_dir_xref (fk_business,fk_director);
create unique index pk_bus_dir_xref (fk_director,fk_business);

It may serve your purposes to define a view pre-joining these tables:

create view BUSINESS_DIRECTORS as
  select pk_business business, pk_director director,
    bus_name, dir_name
  from BUSINESSES, DIRECTORS, BUS_DIR_XREF
  where fk_business=pk_business
    and fk_director=pk_director
  ;


Now you can do selects like this:

  select business, bus_name
  from BUSINESS_DIRECTORS
  where bus_name='INTERNATIONAL BUSINESS MACHINES'
  ;

Enjoy.
-Andy

PS - this was typed with verification against SQL.  There might be
some C-isms that creeped in .


[e-mail] (Ijaz Rashid Malik) writes:
>Hello,
>I have to design two tables and I need some help to minimize the duplication
>etc.
>One table is "businesses" and key fields is business ID.
>Other is "directors" and key is dir. ID.
>A business can have more than one directors and a director can control more
>than one business.
>Both table have to be linked so that all directors for a given business or
>all businesses under given director could be listed.
>My thinking either calls for mulitple Dir. ID. columns in business table
>(dir1. ID, dir2. ID...) but its not practicle as # of directors varies. OR
>same director could be listed in directors table under different ID's and
>there is a column (field) called Business ID which points to the business
>controlled by this person but problem with this is that same record is listed
>multiple times ==> redundancy!!!
>Could someone please help me out here with some hints...
>Thanks very much!
>Malik
>[e-mail]
--
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, [e-mail], [e-mail].
  Join GEnie, call 800-638-9636/301-251-6415.  Join Unix, CASE, and Desktop
  Oracle RDBMS Database discussions, send mail to [e-mail]