The relationships can be diagrammed as follows:
VIDEO >-- TITLE --< TITLE_ACTOR >-- ACTOR
Where "
>--" is a many-to-one relationship and "--<" is one-to-many.
You're right that a query that joins VIDEO to TITLE_ACTOR, even indirectly via TITLE, is going to match N rows from VIDEO to M rows from TITLE_ACTOR, and the result set will have N*M rows for a given TITLE. That's a Cartesian product between VIDEO and TITLE_ACTOR, if there are no direct join restrictions between those two tables.
Re your comments:
The diagram notation I show can illustrate the difference between a logical many-to-many relationship, and thephysical implementation of that relationship, using an intersection table containing two many-to-one relationships. For example, the logical relationship we're trying to represent is the following:
TITLE >---< ACTOR
You might draw your logical data model this way while you're designing the relationships.
But SQL does not support a way to store a many-to-many relationship. To store it physically in the database, we must use an intersection table:
TITLE --< TITLE_ACTOR >-- ACTOR
For any logical many-to-many relationship, your physical model adds an intersection table and reverses the direction of the arrows. This physical model achieves the same relationship as the logical many-to-many relationship.
Does this mean that on a diagram, that I shouldn't show a direct relationship from video to title_actor?
Yes, I would consider there to be the following relationships:
- VIDEO references TITLE (M:1)
- TITLE_ACTOR references TITLE (M:1)
- TITLE_ACTOR references ACTOR (M:1)
But there is no direct relationship from VIDEO to TITLE_ACTOR. Only an indirect relationship via TITLE.
The references correspond to foreign key constraints. For example, VIDEO contains a foreign key referencing TITLE, but VIDEO does not contain (and has no need for) a foreign key referencing TITLE_ACTOR.
FWIW, the diagram above is my attempt to make a simple ASCII format of an Entity-Relationship Model.
No comments:
Post a Comment