Re: Table Design Hint needed

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 .

ummalik_at_ccu.umanitoba.ca (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
>ummalik_at_ccu.umanitoba.ca

-- 
Andrew Finkenstadt, Vista-Chrome, Inc., Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager, andy_at_vistachrome.com, andy_at_genie.geis.com.
  Join GEnie, call 800-638-9636/301-251-6415.  Join Unix, CASE, and Desktop 
  Oracle RDBMS Database discussions, send mail to ora-request_at_vistachrome.com