Re: Many to Many with a Twist

> Imagine three tables C, R and B. C has a primary key of CKEY, R
> is RKEY and B is BKEY.
 

> Another table NEWS has a primary key of NEWSKEY.
 

> C, R and B can all be associated with zero to many NEWS items.
 

> Any NEWS item can be associated with C and/or

I assume you mean                    ^^zero or more^^  here, 

> R and/or B.

and likewise here.

> One model is to put an intersection table between NEWS and C,
> NEWS and B, and NEWS and R.
 

> But when I add more tables at the level of C, R and B then I will
> need more intersection tables.

But if this likelihood appears so great, then I suspect that C, R and B share some common columns, right? If so, abstract the commonality of C, R, B (and future others) into table S with key skey. (S will be a table representing the 'superclass' of C, R and B).

Then create one link table (intersection tables is misleading term) called S_NEWS, which has just columns (newskey, skey). It has foreign keys S_News(newskey) to News(newskey), S_News(skey) to S(skey). The key of S_News is of course (skey, newskey).

Lastly, redo tables C, R and B so that all the common columns (now in S) are gone (these will now be the 'sub-class' tables), apart from their primary key, which is skey. Skey of all the tables C, R and B is at the same time a foreign key to S(skey)

Hope this helps,

                                                                      Philip

-- 
When C++ is your hammer, everything looks like a thumb. (Steven Haflich)
-----------------------------------------------------------------------------
Philip Lijnzaad, lijnzaad_at_ebi.ac.uk \ European Bioinformatics Institute,rm A2-24
+44 (0)1223 49 4639                 / Wellcome Trust Genome Campus, Hinxton
+44 (0)1223 49 4468 (fax)           \ Cambridgeshire CB10 1SD,  GREAT BRITAIN
PGP fingerprint: E1 03 BF 80 94 61 B6 FC  50 3D 1F 64 40 75 FB 53