Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Tricky Normalization

    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!

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Think about what should be your identifier for a goal. The time perhaps? Or maybe only the sequence is important to you?

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Quote Originally Posted by dportas
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •