To me, at least.

I'm taking boxscores from hockey games and putting them into a relational database. So far, I have this:

Games

GameNumber (PK)
-----------------
GameDate Type DateTime
HomeTeam Type String
VisitorTeam Type String

Goals

GameNumber (FK)
Team (FK)
--------------
Period Type Int
Time Type DateTime
Scorer Type Int
Assist1 Type Int
Assists2 Type Int

GameNumber and Team in the Goals table form a composite primary key.

Here's my problem: Each goal also has information about who was on the ice for each team (GoalFor and GoalAgainst). Right now, I'm storing it in the Goals table like this:

GFTeam
GF1
GF2
GF3
GF4
GF5
GATeam
GA1
GA2
GA3
GA4
GA5

This is a pain because I have to concatenate every column to search for someone.

I know I need to create a GoalAgainst and GoalFor table somehow with a composite primary key, but I don't know what columns to combine.

Any suggestions are much appreciated!

Think about what should be your identifier for a goal. The time perhaps? Or maybe only the sequence is important to you?

Yeah, that's what I figured. I'll focus on that- thanks!