CODEWITHSUNDEEP
database-design
Padawan256
Padawan256

Reputation: 103

Is a many to many relationship bad? What about this example?

According to "Database Processing: Fundamentals, Design, and Implementation" 11th ed.,

"The problem for the data models of N:M relationships between strong entity is that they have no direct representation. An N:M relationship must always be decomposed into two 1:N relationships using an intersection table in the database design."

OK, I get that. But what if your intersection table creates a many to many. I have come up with a simple example. Lets take a video store. The video store has many copies of each movie, and each movie has many actors. So, to show the database tables:

TITLE  (The movie title.)
TitleNumber   (unique pk)
Title

VIDEO 
VideoNumber   (each copy is unique even across titles, so unique  pk)
TitleNumber  (fk)

TITLE_ACTOR
TitleNumber  (pk)
ActorNumber  (pk)
CharactorPlayed

ACTOR
ActorNumber  (pk)
FirstName
LastName

So lets say you have three copies of Star Wars I. And a copy of Raiders of the lost arc. Star wars would have video numbers 1-3 and Raiders would have video number 4, and the next movie or copy would be 5.

So the issue I see is that VIDEO and TITLE_ACTOR both have the TitleNumber (I.e. movie title) in common. And both tables allow for many duplicates of the TitleNumber, many copies of a title and a title can have many actors. So VIDEO to TITLE_ACTOR is a many to many relationship. Is that correct so far? Or does the composite key change it? Or something else?

Normally you join these with a transition table, but they are already joined. I don't see a way to move a attribute over to make a 1:M without anomolies.

I have spent many days researching this, books and online. Please be kind as I am here to learn.

Upvotes: 4

Views: 7789

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562310

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 the physical 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.

Upvotes: 4

paulsm4
paulsm4

Reputation: 121649

Q: Is a many to many relationship bad?

A: No, of course not!

Lets take a video store. The video store has many copies of each movie, and each movie has many actors. So, to show the database tables:

TITLE (The movie title.) TitleNumber (unique pk) Title

VIDEO VideoNumber (each copy is unique even across titles, so unique pk) TitleNumber (fk)

TITLE_ACTOR TitleNumber (pk) ActorNumber (pk) CharactorPlayed

ACTOR ActorNumber (pk) FirstName LastName

For a video store, I might have just ONE table: "video_selections":

create table video_selections (
  title varchar(20) not null,  -- EX: Star Wars
  serial_no varchar(20) not null,
  type char(1) not null, -- V)HS, D)VD, B)lu-Ray
  genre char(1) not null,
  star varchar(20) not null,
  director varchar(20) not null,
  release_date datetime not null
  checkout_date datetime,
  return_date datetime
)

This could easily a) keep track of inventory, b) allow searches based on title, genre or star and c) cross-reference actor (at least lead actor) and movie title or movie genre.

On the other hand, if you were creating a movie database, you might want the following tables:

table movie
  title
  release_date
  ...

table actors
  name
  birthdate
  biographical info
  ...

table roles
  movie_title
  actor_name
  ...

Table "roles", of course, would model the many-to-many relationship between movies (which have multiple actors) and actors (who may appear in multiple films).

'Hope that helps ... at least a little bit...

Upvotes: 1

Related Questions