Normalization....help me please!!!! (1 Viewer)

[tower2108]

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.

 

[RuralGuy]

Have you read this link yet?

 

[gemma-the-husky]

offhand i would have said you need these tables

leagues (not necessary if you only have 1 league)
divisions (not necessary if you are only tracking 1 division)
teams

ie so that a team is in a division, is in a league

then
players (linked to teams)
matches (home team/away team - see notes)
matchplayers (see notes)

all the fields you mention will fit logically in one of these tables

FixtureNo - matches
FixtureDate - matches
HomeTeam - matches
AwayTeam - matches
HomeTeamScore - matches
AwayTeamScore - matches
HomeTeamPlayers(s) - matchplayers
AwayTeamPlayers(s) - matchplayers
HomeTeamGoalScorer(s) - matchplayers (see notes)
AwayTeamGoalScorer(s) - matchplayers (see notes)

now the problem with this is that it is not normalised - since the home and away sides are both different columns of the same table - (it becomes harder to find all the goals scored by a given team or player, since you will need to use a union query) - its therefore probably better to have two rows in a match table, linked to each other, to store the match - then you don't need the union queries

now some other details that may cause problems, and you need to consider

a) this set up is ok to handle match players, but will only let you record the total goals for each player - if you need to store details of each goal individually (times etc) then so you may need ANOTHER table to store this - alternatively, you could just store the player, the number of goals, and a text field saying "14mins, 28 mins (pen), 56 mins" - the only thing you can't then easily do is analyse at which point in a game a player is scoring.

b) also this is ok for a single season, but next year teams get promoted relegated, and the division structure changes

c) this also doesnt necessarily allow for transfers - so you may need to consider adding a mechanism to enable players to play for multiple teams