title: what is the best way to store fixture for men doubles tournament
body: <p>I am designing an sports app where I need to create fixture for doubles tournament like Men doubles event in Tennis and then store it for future usage. In doubles 2 players plays as a team against another team of 2 players. Fixture looks something like the attached image </p>
  
  <p><a href="https://i.stack.imgur.com/vOx2r.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vOx2r.png" alt="Doubles tournament Fixture"></a> </p>
  
  <p>For each match in the fixture I need to record score and then winner/runner up. For 2nd round onwards I need to know from which match winners player will come to the 2nd round match.</p>
  
  <p>I have designed the database something like this. Kindly let me know if there could be better design. </p>
  
  <pre><code>create table doubles_fixture(
      doubles_fixture_id bigint(20) not null auto_increment primary key,
      fixture int not null,#fk fixture
      round varchar(32),
      match1 bigint(20),#fk doubles_match
      match2 bigint(20),#fk doubles_match
      player1 bigint(20), #fk ,
      player2 bigint(20),#fk
      player3 bigint(20),#fk
      player4 bigint(20),#fk
      game varchar(16) not null check (game IN ('badminton','cricket','tt','carrom','chess','tennis')),
      match_score varchar(128),
      winner1 bigint(20),
      winner2 bigint(20),
      winner1_points_gain float,
      winner2_points_gain float,
      losser1_points_lost float,
      losser2_points_lost float,
      match_start_time timestamp,
      match_end_time timestamp,
      constraint doubles_fixture_fk1 foreign key(fixture) references fixture(fixture_id),
      constraint doubles_fixture_fk2 foreign key(match1) references singles_fixture(doubles_fixture_id),
      constraint doubles_fixture_fk3 foreign key(match2) references singles_fixture(doubles_fixture_id),
      constraint doubles_fixture_fk4 foreign key(player1) references oyetournament_user(user_id),
      constraint doubles_fixture_fk5 foreign key(player2) references oyetournament_user(user_id),
      constraint doubles_fixture_fk6 foreign key(winner) references oyetournament_user(user_id),
      constraint doubles_fixture_fk7 foreign key(player3) references oyetournament_user(user_id),
      constraint doubles_fixture_fk8 foreign key(player4) references oyetournament_user(user_id)
  )ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
  </code></pre>