SQL schema - relationship between tables question

Question

I can't figure out how to relate the tables.

e.g. I have 4 tables (league, match, player and team) I have divided between PR and FK, but I cannot understand how to relate them.

Is it possible that they cannot be related?

I tried to link them together but I don't understand the logic

Answer 1

If you have these four tables, there are probably relationships between them that can be created.

Some of techniques to connect these tables are:

• the primary key i-e league ID, should be used in the league table.

• A foreign key i-e league ID, should be used to connect the team table to the league table and the primary key, i-e team ID, should be assigned to each team.

• A foreign key, i-e team ID, should be used to connect the player table to the team table and the primary key, such as a player ID, should be assigned to each player.

• Foreign keys should be used to connect the match table to the team table and the league table. A the primary key, i-e match ID, should be assigned to each match.

Answer 2

Start by creating a simple ERD diagram (https://www.databasestar.com/entity-relationship-diagram/). That way it will help you visualize the relationships between the entities.

For example:

League
    LeagueId  PK

Team
    TeamId  PK
    LeagueId  FK - a team plays in only 1 League

Player
    PlayerId  PK
    TeamId      FK - a player plays for only 1 team

Match
    MatchId   PK
    LeagueId  FK 
    Team1Id   FK to a team
    Team2Id   FK to a team

Obviously this can be extended if you need to track history of players and teams etc by introducing additional tables.