Re: Many to Many related with another Relation

From: Rob <>
Date: Thu, 6 Mar 2008 10:47:51 -0800 (PST)
Message-ID: <>

On Mar 5, 5:26 pm, wrote:

> |>   +----------+    M           /----------\          N    +----------
> +
> |>   | Entity_1 | <---------- | Relation1 | ---------> | Entity_2 |
> |>   +----------+                  \----------/
> +----------+

> 1 |
> |
> |
> /----------\ > | Relation2 | > \----------/
> |
> |
> N | > +----------+ > | Entity_3 | > +----------+ > > i have a lil doubt bout it, is it allow if we make that kinda relation > diagram ? > > coz ive found a pic diagram like that, where the relation1 will become > a New Entity after Mapping, n that new Entity will related to > Relation2.. > > is there any based theory that can explain bout that ? > > my regard, Shalli.. There is nothing fundamentally wrong with this. We are "allowed" to make any kind of relation diagram we want and to implement it using conventional mappings into SQL. Just be aware that few other database students/practioners will understand what you've built, and be aware
of all the update consequences of your (meta) model.

That being said, what you have here treats the Junction Table "Relation1" as an association entity and makes the second Junction Table "Relation2" as an association entity between Entity_3 and Relation1.

This is not conventional. Junction Tables (aka association entities) are usually defined between a pair of entity relations, not an entity relation (Entity_3) and an association entity (Relation1).

Assuming that referential integrity constraints (ICs) are declared between each foreign key (2 in Relation1, 2 in Relation2), the model you've constructed has the unusual property that deletion of an entity in Entity1 that is referenced by an association entity instance in Relation1 will either be blocked, or, will "trigger" a deletion cascade to a referencing association entity in Relation2 and in turn to an entity in Entity_3. (It can be set up either way, to block or to trigger.) Likewise for a deletion from an entity in Entity_2 referenced by an association entity in Relation1.

This group doesn't like new schema designs, I believe because their modeling discliplines don't need them. In particular, every UMLrepresented  relationship already maps to a schema design, so why muddy the water with anything new?

Here's another schema design (mine!!) that operates on the same engine level (i.e., Relational Database Management Engine - RDBME) as yours and provides capabilities of no current value to those who do their relational modeling at a higher level of abstraction:

I'm not 100% sure what your intent is with respect to your schema. It looks like it might provide a useful triggering mechanism in conjunction with an embedded DBMS database.

Rob Received on Thu Mar 06 2008 - 19:47:51 CET

Original text of this message