Re: Database Design - Tricky

In article <279730c0.91eee10f_at_usw-ex0106-046.remarq.com>, Van Messner <vmessnerNOvmSPAM_at_discovernet.com.invalid> writes
>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 R and/or B.
>
>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. Is there a more elegant solution?
>Please be specific about which would be the key and foreign key
>columns of any alternative solution.
>
>Many thanks,
>
>Van
>
>
>
>-----------------------------------------------------------
>
>Got questions? Get answers over the phone at Keen.com.
>Up to 100 minutes free!
>http://www.keen.com
>

How about an intersection table that has a foreign key to the NEWS table optional foreign keys to each of the B, C, R tables?

When a new table at the level of B, C, R is added you would need to modify the intersection table to include the new key.

E.G.

NEWS_FK NOT NULL references news(newskey)

B_FK NULL references b(bkey)
C_FK NULL references c(ckey)
R_FK NULL references r(rkey)

Alternatively, and doing without some foreign keys entirely, you could have an intersection table that has the NEWS foreign key, a table name and the key of item within that table... you'd loose a lot of the database 'protection' regarding foreign keys but you wouldn't have to modify anything when you add new tables.

E.G.

NEWS_FK NOT NULL references news(newskey) TABLE_NAME NULL VARCHAR2(30)
ITEM_FK NULL VARCHAR2(30) -- dependent on the type of the tables

Andy

-- 
Andy Hardy. PGP ID: 0xA62A4849
===============================================================