CODEWITHSUNDEEP
sqldatabase-design
Adham
Adham

Reputation: 64904

How to design relationships between M-M tables in sql

If I have a match table ( id , details) and team table (id,name) and I want to get all matches played by a given two teams.

My solution is:

Create a third table contains on (match_id,team1_id,team2_id)

Is this the best practice in this case ?

Upvotes: 3

Views: 230

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52127

You don't need a third table - reference the teams directly from the match table:

enter image description here

Just to clarify, the {team1_id, team2_id} should not be a key, to allow the same pair of teams to be engaged in more than one match. And of course, it goes without saying you need appropriate FKs and a CHECK(team1_id <> team2_id).

To get the matches played by the given team, you'd need..

SELECT ... WHERE team1_id = :given_team_id OR team2_id = :given_team_id

...so you need indexes on both team IDs, as denoted by I1 and I2 in the diagram above.

Also, consider making these indexes wider for performance reasons. For example, if your only fetch a limited subset of fields...

SELECT team1_id, team2_id FROM ...

...and widen the indexes to cover these fields (I1: {team1_id, team2_id} and I2: {team2_id, team1_id}), the DBMS won't have to touch your table heap at all to satisfy the query.

Alternatively, you could consider using a natural key, for example:

enter image description here

This would allow you to eliminate one of the indexes (the one under the surrogate PK: {match_id}), but would of course make any downstream FKs "fatter", which may or may not be a good thing depending on what you are trying to accomplish.

Depending on your querying needs, you may fiddle with the order of PK fields:

  • For example, if majority of your queries ask: "give me matches played in the last X days", consider moving match_date to front.
  • If majority of your queries ask for matches played at any time, keep it in the back.
  • If both, then you'd need an additional index.

BTW, having a third table {match_id, team_id} is appropriate for matches that can be played by more than two teams (such as sailing). It also has an unfortunate feature of allowing only one or even zero teams - which is something you'll probably need to guard against in a non-declarative manner.

The design above ensures there are exactly two teams.

Upvotes: 2

Vikdor
Vikdor

Reputation: 24134

Assuming a match can be played only by two tables, the team1_id and team2_id should be part of the match table itself as attributes. A separate table is not needed. You can also define FOREIGN KEY relationship between team1_id and team table's id and the same for team2_id also.

A third table would be needed for a match that can be played by multiple teams and in such case the table structure would be (match_id, team_id) where there will multiple records with same match_id, i.e. one-to-many relationship between match table and match_teams table.

Upvotes: 1

Related Questions