Toad World® Forums

Many-to-many relationships with an identifying attribute


#1

A film can have many crew members, and a crew member can work on many films. It’s a simple many-to-many except a crew member may be an assistant director on one film and a director on another. The intersection table has an identifying attribute which is the crewmember’s role on the film.

I understand how to represent that on the physical model, but how is it described on the logical model?


#2

Hello,

In your LER model, edit the identifying relationship | Cardinality tab | select options Many (for left side) and Many for (right side). Confirm.
No intersection table is displayed in the LER model (keys do not migrate in LER model).
However, after LER to PER conversion, you can see the intersection table, including the key attributes, in the physical model.

Please see the attached screenshot.

If you have any questions, please write me back.

Regards,

Vladka + TDM team


#3

What do you do when the interesection table has a key attribute of its own in addition to the PFKs?

What I ended up doing was creating the intersection table as three 1-ms. Now, many people can have many roles in many movies, and vice versa.


#4

Hi,

We are not exactly sure what you mean. Could you please model it in PER model and send it to us (modeling@quest.com). Thanks!

Regards,

Vladka


#5

Hi,

Thanks for your model.
We’ll have a look at it and let you know.

Regards,

Vladka


#6

Hello Thomas,

Thanks for your model again. Now we understand what you mean.

Important information: Toad Data Modeler supports only binary relationships. We are sorry, it is not possible to model 3rd relationships in TDM.

The way you solved it is fine. We converted your LER model to Oracle 10g PER and our developers say it is modeled correctly.

If you have any questions, please do not hesitate to write us back. Thanks.

Regards,

Vladka + TDM Team