Search code examples
mysqlsqldatabaserelationships

One-to-one relation through pivot table

Okay so I have a soccer website im building when a user signs up they get a team and and 6 different stadium to chose from. so I have a teams table:

----------------------------------------
|  user_id  |  team_name  |  stadium_id  |
----------------------------------------
|     1     |     barca   |      2     |
----------------------------------------

Then I decided to make the stadiums its own table

----------------------------------------------
| id  |    name   |    capacity  |  price      |
----------------------------------------------
|  1  |  Maracana |     50000    |    90000    |
------------------------------------------------
|  2  |  Morombi  |     80000    |    150000    |
------------------------------------------------

to get the teams arena name I would have to get the arena_id from the teams table and then fetch the arena name with the id. Now I don't think this is very efficient so I gave it some thought and I think the best solution is adding a pivot table like so:

| id  |    arena_id    |    team_id   | 
---------------------- ----------------
|  1  |    2           |     1    
--------------------------------------|
|  2  |    1           |     2   
--------------------------------------|

I usually think of pivot tables as tables for many to many relationships not one to one relationships. Is using a pivot table in this instance the best solution or should I just leave the implementation I'm currently using?

Solution

  • You don't need to use a pivot-table for this. It can either be a One-To-One or a One-To-Many relationship. It's a One-To-One if every user/team does only relate to one stadium (no stadium can be used by two teams). In a One-To-Many relationship multiple teams/users could use the same stadium, which might become necessary if you have thousands of users and start running out of stadiums.

    A JOIN statement would be efficient and sufficient here.

    SELECT s.name, t.team_name   -- Get the team's and stadium's name
  FROM team t                -- From the team table
  JOIN stadium s             -- Join it with the stadium table
    ON (t.stadium_id = s.id) -- Join ON the stadium_id

    This will return the team name and stadium name of every registered team.

    You might need to adjust the query, but you should be able to catch the grasp of it after reading the MySQL reference I linked above.