Re: General concept, joining two tables...

In article <Pine.LNX.4.10.10003110140280.724-100000_at_linux.loveshackbaby. org>, Steve Schroeder <zamdrist_at_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_at_shrdlu.com
bap_at_shrdlu.co.uk