mysql - One-to-one relation through pivot table -

okay have soccer website im building when user signs team , and 6 different stadium chose from. have teams table:

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

then decided make stadiums own table

---------------------------------------------- | id  |    name   |    capacity  |  price      | ---------------------------------------------- |  1  |  maracana |     50000    |    90000    | ------------------------------------------------ |  2  |  morombi  |     80000    |    150000    | ------------------------------------------------ 

to teams arena name have arena_id teams table , fetch arena name id. don't think efficient gave thought , think best solution adding pivot table so:

---

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

i think of pivot tables tables many many relationships not 1 one relationships. using pivot table in instance best solution or should leave implementation i'm using?

you don't need use pivot-table this. can either one-to-one or one-to-many relationship. it's one-to-one if every user/team relate 1 stadium (no stadium can used 2 teams). in one-to-many relationship multiple teams/users use same stadium, might become necessary if have thousands of users , start running out of stadiums.

a join statement efficient , sufficient here.

select s.name, t.team_name   -- team's , stadium's name   team t                -- team table   join stadium s             -- join stadium table     on (t.stadium_id = s.id) -- join on stadium_id 

this return team name , stadium name of every registered team.

you might need adjust query, should able catch grasp of after reading mysql reference linked above.