Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2009
    Posts
    1

    Normalization....help me please!!!!

    Hello everyone, this is something i have been stuck on for quite a while now and its starting to drive me crazy!!!

    I really need someones help with normalizing the fields i have for a database. I want to create a database in order to input and store data on a local soccer league so that fixtures, results, league tables and player stats can be viewed easily as reports ready for them to be printed and distributed to the leagues clubs.

    I think the majorty of the fields i have are correct and have listed them below but in case i haven't then i will explain what data i need entering into the forms and what i need the reports to display so that if the fields below are incorrect then hopefully someone can steer me in the right direction with them.....please!!

    The fields are as follows:

    DistrictNo TeamNo PlayerNo
    DisrictName TeamName PlayerName

    I don't think these fields are the ones that are causing me the problems because they are fairly straight forward in regards to what table they would go into and how they would fit into a relationship with one another however, the fields below and with that the table they would go into is what i am having problems with.

    FixtureNo
    FixtureDate
    HomeTeam
    AwayTeam
    HomeTeamScore
    AwayTeamScore
    HomeTeamPlayers(s)
    AwayTeamPlayers(s)
    HomeTeamGoalScorer(s)
    AwayTeamGoalScorer(s)

    The reason why i am having problems with these is fields and table is because i dont know if i need the majorty of the fields. These fields are any of the ones that have home/away team in their title because im thinking that maybe this data wouldn't need to be stored in a table and could be created and taken from a form. However, if all the data below would need to be stored in a table then would it be in the one above or a seperate one?

    I understand that this table above is incorrect but i am struggling to understand what to do with it and where to get certain data from i.e the home/away team, do i use just one field (TeamNo) or do i need to separate fields like above to cater for both home and away teams? This is also the same for Score, Players and Goalscorers.

    If anyone could resolve this i would be very grateful indeed.

    Thank you.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if team rosters (the players on a team) can change from game to game, then the following will need some adjustment
    Code:
    Teams
    TeamNo    PK
    
    Players
    PlayerNo   PK
    TeamNo     FK to Teams 
    
    Fixtures
    FixtureNo      PK
    FixtureDate
    HomeTeam       FK to Teams
    AwayTeam       FK to Teams
    HomeTeamScore
    AwayTeamScore
    
    FixtureGoals
    FixtureGoalNo  arbitrary PK
    FixtureNo      FK to Fixtures
    GoalScorer     FK to Players
    if FixtureGoalNo key values can be assigned incrementally, you won't need a timestamp column in the FixtureGoals table

    you don't actually need to store HomeTeamScore and AwayTeamScore in Fixtures (as they can be derived from the child rows in FixtureGoals) but many people do this for convenience, since the data isn't going to change and since not all reports need to know the individual goal scorers but likely do need the final score of the game

    and, no, before someone else jumps in and tries to claim this, 3NF is not violated by storing HomeTeamScore and AwayTeamScore

    Last edited by r937; 04-05-09 at 19:08.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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