Football Match stats - how to capture goals scored

[shoten]

It's a fairly trivial ask but i'm stumped on how to approach the problem. As the title suggests i'm creating a personal app to capture stats from Football matches. I have a few source tabs in a single sheet, Players, Fixtures, Results. Goals, Assists. I can happily ref the right tables to create the right relationships. Parent = Fixture with Results as child, and Goals and Assists as a Child of Results. Where i am stuck is how to capture goals scored in a game and associate them with a playerID, feeding eventually back up to the fixture. 

My tactical fix is just adding each player as a column in the goals table and adding goals per fixture, this doesn't give me the association with the playerID though that i'd like.

Any help/suggestions would be greatly appreciated.

[Rafael_ANEIC-PY]

Hello @shoten, you can create, as you said, a child table called "Goals" and inside this child table also have a reference to the "Players" table, so that you can save the information regarding who scored the goal, same thing for assists as well.

[shoten]

Thanks for the response, appreciate the time. Dont think it quite achieves what i'd like. The columns in the goals sheet are named manually for each players name, i can then add a number to the relevant player but there is sadly no association with the playerID. I may have not implemented your suggestion correctly however but i do now see goals as an inline element of the player, albeit with no data for that player.  My ideal would be a drop down selection of the players names (ref to their row id)which when selected, i can then enter how many goals they scored. Obviously more than one player could score a goal in any given match. It's likely i just can't see the wood for the tree's at this point.

[Marc_Dillon · Accepted solution]

One column per player is really bad data structure, please do not do that. Your Goals table should be something like this:

id	Match	Player	Goals

[shoten]

Thanks for replying, yep it's nasty and seriously limits functionality. Would the above format allow for multiple players per match? .. as i type i think i see how .. new row entry per player who scored all related to the same fixture. Will give that a shot... thanks very much

[shoten]

Worked like a charm thank you .. and should have been staring me in the face. Thanks for the help.