1

I am currently trying to setup a database managing players and teams for a competition. Players are competing in teams of two but may be shuffled.

As every team combination can only exist once, I decided that the primary key of each team is composed of the two ids of the players in it:

CREATE TABLE teams (
  player1 INTEGER REFERENCES User(id) NOT NULL,
  player2 INTEGER REFERENCES User(id) NOT NULL,
  rating INTEGER NOT NULL,
  PRIMARY KEY (player1, player2)
);

The competition consists of matches, each match being three games.

I want to reference two teams in a match:

CREATE TABLE matches (
  id INTEGER PRIMARY KEY NOT NULL,
  team_1 INTEGER REFERENCES Team(id) NOT NULL,
  team_2 INTEGER REFERENCES Team(id) NOT NULL,
  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
)

Each game contains the score of the winning and the losing team:

CREATE TABLE games(
  id INTEGER PRIMARY KEY NOT NULL,
  match_id: INTEGER REFERENCES Match(id) NOT NULL,
  score_winner : INTEGER NOT NULL,
  score_loser : INTEGER NOT NULL
)

As I do not know the winner of a game like this, I would like to add the primary key of the team that won the game to be able to to figure out the winner of a match (whoever won the most matches)/

How do I reference the winner of a game in the given table?

2

What I would do here is to have a table match_winner! It will be a JOINing table (also more formally called an ASSOCIATIVE ENTITY as well as many other names from the page).

I'm not quite sure why you have two tables called matches and games - are they not the same thing? If not, let me know and I'll modify the schema.

Something like this is what you require IMHO. It is a joining table between teams and matches

CREATE TABLE match_winner
(
  match_id INTEGER NOT NULL,
  team_id  INTEGER NOT NULL,
  winning_score INTEGER NOT NULL CHECK (winning_score > 0),

  CONSTRAINT 'match_winner_pk' PRIMARY KEY (match_id, team_id),
  CONSTRAINT 'mw_match_fk'     FOREIGN KEY (match_id) REFERENCES matches (match_id),
  CONSTRAINT 'mw_team_fk'      FOREIGN KEY (team_id)  REFERENCES teams   (team_id) 
);

I made a small modification to your schema - I put a id INTEGER SERIAL into the teams table definition - check out the fiddle here. SERIALs are great! I also added a constraint CHECK (player1 != player2).

I then ran this SQL

SELECT t.player1, t.player2, m.id, mw.match_id, mw.team_id, mw.winning_score
FROM teams t
JOIN matches m ON t.player1 = m.team_1 AND t.player2 = m.team_2
JOIN match_winner mw ON m.id = mw.match_id;

You can check the result on the fiddle. Having the joining table avoids the use of NULL and provides added flexibility. Just a couple of recommendations - use upper case for SQL keywords and lower case for your own variables - or the opposite - just choose a standard and stick to it. You should also name your constraints as I did for the match_winner table - it's much easier to figure out error messages that way.

I hope this answers your question - if not, let me know! p.s. welcome to the forum! ;-)

Ooops - I nearly didn't notice the question. I reversed my modification of your teams schema - see the fiddle here, but the SQL is exactly the same. To use a two field PRIMARY KEY you just join on both fields instead of just a simple id one - et voilà! Of course, you can have the id and with a UNIQUE constraint, it's that bit easier sometimes - it's just that I wanted to give you the identites of the players. I've added a further fiddle here showing how you can alias a table twice in the same query to do useful things - here to join the names of player1 and player2 to your results.

  • Thank you for the the detailed answer! I have matches and games because a match consinsts of multiple games, like a best of three or a best of five. Would I really need an extra table for the winner of a match/game? Each game can only have one winner, so I figured I should just put the id directly into the game. – Marv Sep 21 at 11:28
  • That's really up to you as far as I can see but if it's a nice simple field, then why not? However, there is a theoretical reason not to have it I suppose. It can be derived from games? I just like to avoid having NULLable fields if at all possible. The SQL isn't that complex as you've seen above. Do you want an overall_score also in the match table? Again, shouldn't be difficult. p.s. thanks for the upvote! – Vérace Sep 21 at 11:46
  • No just as you figured I would like to know game winners and will derive the matchwinner from that. Having no nullable fields sounds fair. Might even give more flexibility later to have an extra table for matchwinners. – Marv Sep 21 at 11:59
  • Just wondering, can a game end in a draw - or is there a tie-break at the end if there's a draw? Or if it's a best of 5 and it's 2-2, is there then a tie-break? – Vérace Sep 22 at 4:37
  • There is going to be no draws, just regular bo3, bo5 or whatever the format is then. – Marv Sep 22 at 10:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.