General concept, joining two tables...

Building a database up from ground zero, here is a brief description: This is a Patent Application database that falls nicely into two tables, Applications (Docket Number (unique), Filing Date, etc.) Inventors (InventorID (unique),Name, Address, Country, etc.) Now here is the problem. There may be multiple inventors per application, and an inventor can be associated with more than one application. So, there is a many to many relationship between each table. The first idea that comes to me is I could have a third table containing Docket Number & Inventor ID. Neither field could be a unique index. Can someone think of a way I can avoid having a third table without a unique index?

Steve, You are basically on the correct track. You indeed need a third table to relate the Applications and Inventors tables. While neither Docket Number nor Inventor ID can separately be a primary key on this third table, you can create a composite key of the two that will be unique. Composite keys are not at all uncommon. Steve Schroeder <zamdrist@pconline.com> wrote in message news:Pine.LNX.4.10.10003110140280.724-100000@linux.loveshackbaby.org... > Building a database up from ground zero, here is a brief description: > > This is a Patent Application database that falls nicely into two tables, > > Applications (Docket Number (unique), Filing Date, etc.) > Inventors (InventorID (unique),Name, Address, Country, etc.) > > Now here is the problem. There may be multiple inventors per application, > and an inventor can be associated with more than one application. > > So, there is a many to many relationship between each table. > > The first idea that comes to me is I could have a third table containing > Docket Number & Inventor ID. Neither field could be a unique index. > > Can someone think of a way I can avoid having a third table without a > unique index? > > > >

In article <Pine.LNX.4.10.10003110140280.724-100000@linux.loveshackbaby. org>, Steve Schroeder <zamdrist@pconline.com> writes >Building a database up from ground zero, here is a brief description: > >This is a Patent Application database that falls nicely into two tables, > >Applications (Docket Number (unique), Filing Date, etc.) >Inventors (InventorID (unique),Name, Address, Country, etc.) > >Now here is the problem. There may be multiple inventors per application, >and an inventor can be associated with more than one application. > >So, there is a many to many relationship between each table. > >The first idea that comes to me is I could have a third table containing >Docket Number & Inventor ID. Neither field could be a unique index. > >Can someone think of a way I can avoid having a third table without a >unique index? The third table you propose here does have a unique index, it's a compound index including both the ID of the inventor and of the docket. That index is unique provided that an inventor can only appear once on any docket. This is the usual way of implementing m:m relationships. When you create a link-entity like this it's worth looking at it carefully. You might find that it has more attributes than just the two IDs. -- Bernard Peek bap@shrdlu.com bap@shrdlu.co.uk

>Subject: General concept, joining two tables... >From: Steve Schroeder zamdrist@pconline.com >Date: 11.03.00 03:32 W. Europe Standard Time >Message-id: <Pine.LNX.4.10.10003110140280.724-100000@linux.loveshackbaby.org> > >Building a database up from ground zero, here is a brief description: > >This is a Patent Application database that falls nicely into two tables, > >Applications (Docket Number (unique), Filing Date, etc.) >Inventors (InventorID (unique),Name, Address, Country, etc.) > >Now here is the problem. There may be multiple inventors per application, >and an inventor can be associated with more than one application. > >So, there is a many to many relationship between each table. > >The first idea that comes to me is I could have a third table containing >Docket Number & Inventor ID. Neither field could be a unique index. > >Can someone think of a way I can avoid having a third table without a >unique index? > Use the collection types offered in the 9.2 server. Nona