What I would do here would be to have a table with match_winner! It will be a
JOINtable (also more formal than
ASSOCIATIVE ENTITY as well as many other names from the side).
I'm not sure why you called two tables
games – Are not they the same? If not, let me know and I will change the scheme.
Something you need IMHO. There is a connection table between
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've made a small change to your scheme
id INTEGER SERIAL in the team table definition – here it goes to the violin.
SERIALs are great! I also added a restriction
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 violin. If you have the connection table, avoid the use of
NULL and offers additional flexibility. Just a few recommendations – use uppercase letters for SQL keywords and lowercase letters for your own variables – or vice versa – just pick a standard and stick to it. You should also name your restrictions as I did for that
match_winner Table – it is much easier to find error messages in this way.
I hope this answers your question – if not, let me know! P.S. Welcome to the forum! 😉
Oops – I hardly noticed the question. I've undone my change to your team schema – see the violin here, but the SQL is exactly the same. Use two fields
PRIMARY KEY You simply connect both fields instead of just a simple one
id one – et voila! Of course you can have that
id and with one
UNIQUE Coercion, sometimes it's a bit easier – I just wanted to tell you the identities of the players. I've added another fiddle function here, showing how you can alias a table twice in the same query to do useful things – here, to match the names of Player1 and Player2 to your results.