Re: Manage many to many relationship between "N" number of tables

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Fri, 4 Jan 2008 19:42:52 -0000
Message-ID: <hqSdneC0msigEePanZ2dnUVZ8sOonZ2d@giganews.com>


"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1199335357.976184_at_bubbleator.drizzle.com...
> steven.p24_at_googlemail.com wrote:
>> Hi All,
>>
>> Problem:
>>
>> Say i have 3 tables A, B & C in my db. They can have Many to Many
>> relation ship with each other i.e.
>
> Three is a simple solution to the problem. Many to many relationships
> are not allowed in a well designed RDBMS. Create intersecting entities,
> tables holding the primary keys of the other tables, so that all
> relationships are 1:M.

By "relationship" I expect you mean "referential integrity constraint". Actually I would expect any well-designed RDBMS to allow integrity constraints of ALL kinds - particularly inclusion dependencies (of which SQL's "foreign key" constraint is just a special case - ie. an inclusion dependency the target of which must be a superkey). In ER modelling an inclusion dependency corresponds to a many-to-many relationship.

In common with most other SQL DBMSs, Oracle has only limited support for the generic constraint syntax that has been part of standard SQL for years (ASSERTION and the subquery form of CHECK constraints appeared in SQL92 I think). Instead we are expected to implement business rules in procedural code, which is a shame.

-- 
David Portas
Received on Fri Jan 04 2008 - 13:42:52 CST

Original text of this message