Can someone help me normalise my ER diagram please. Also it would be great if people can suggest improvements I can make to the current design.

Thanks in advance

player table and goal table can't both have an FK to each other

team table and group table can't both have an FK to each other

player table is missing an FK to team (can a player ever change teams?)

match table has two FKs to team but team doesn't have a matching column

match table has a goal_id? why?

match table has a group_id? why?

booking table has no PK

whew!!!

As this is not specific to MySQL, its more to do with the overall db design this thread has been moved to the concepts

An earlier related thread may be found at:
Football league

player table is missing an FK to team (can a player ever change teams?)

all players are signed to one team and they can't change over

match table has two FKs to team but team doesn't have a matching column

the two FK indicate that two teams are facing each other in a match

match table has a goal_id? why?
soon as a player scores a goal, a goal_id would be generated in the match table. but come to think of it now I don't need a GOAL_ID in the match table. the ID would be generated in the goal table soon as the player scores


I hope this answered your question, but let me known if you have an idea which is better than mine

actually, i had several

and they were theoretical questions (pun intended)

Can you indicate which table require normalisation?

only after you fix all the PK/FK issues

match table has two FKs to team but team doesn't have a matching column

I don't understand the above question, can you give me more details?

let me ask you... which column in the teams table do the fteam_id and steam_id columns reference?

I was think about have the fteam_id & steam_id as attribute. I hope that makes sense

What column in the team table properly completes this:

Also, you resolved your team-group FK opposite of what it should be.

I am totally confused

I was thinking about manually adding the team in the match table.

I understand that FTEAM_ID & STEAM_ID has no reference in the team table

I think what you are trying to say is to resolve the issue I should label the FTEAM_ID & STEAM_ID as a FK

am I correct?

no, if i was trying to say something, i would just say it

i was actually hoping you might try to answer the question i gave you --

which column in the teams table do the fteam_id and steam_id columns reference?

they refer to the TEAM_NAME