3

I am trying to understand many to many relationship. I have read articles on this topic but I am still not getting it.

Let's take team and player as an example. One team can have multiple players, but one player can only play in one team? I know that it's not correct but I can not understand why.

What is the proper way to determine relationship between tables? What questions should I ask (from side of player and from team) to find out relationships?

Not sure whether my second question is clear enough.

Thanks for help

Peter F
  • 83
  • 2
  • 3
  • 12

2 Answers2

4

Perhaps the best way to look at it is to draw all teams on one side, all players on the other, and draw the relationships between them as lines.

If no player is (nor can be) connected to multiple teams, i.e. there is at most one line leaving from each player, then it has a to-one relationship with the teams.

If teams are (or can be) connected to multiple players, i.e. there can be multiple lines leaving from each team joining it to multiple players, then it has a to-many relationship with the players.

So typically, teams have a one-to-many relationship with players. One team shares many players, and players are part of only one team.

towr
  • 4,147
  • 3
  • 20
  • 30
  • I think I am getting it. So let's say I have Categrories(Personal,Work..) and Notes. One category can have multiple notes and one note belong to one category. Is it right? – Peter F Mar 22 '14 at 21:08
  • If the categories are mutually exclusive, that's correct, yes. (Otherwise a note could be in multiple categories.) – towr Mar 22 '14 at 21:11
  • OMG, been trying to figure this out few hours and now it's clear. – Peter F Mar 22 '14 at 21:14
  • And if many notes could belong to multiple categories how would you resolve it? Creating new table (eg. category_detail) with FK's from both tables? That means one category can have many category details and many category details can belong to one note? Thanks. – Peter F Mar 22 '14 at 22:11
  • 1
    Yup, for a many-to-many relation having an extra table like that is precisely the standard solution. – towr Mar 22 '14 at 22:20
1

Here is a good many-to-many relationship example: a car type can be sold at many dealerships and a dealership can sell many car types.

The following is a good one-to-many relationship using the same objects as an example: a car type with a unique VIN# can be sold at one dealership and a dealership can sell many different car types with a unique VIN#.

J.S. Orris
  • 4,653
  • 12
  • 49
  • 89