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.

I would suggest you to take a shot at it and say what tables and columns you have for now, as far as normalization goes

I have done a similar design recently for basketball, here is the basic concept

lowercase - tables
uppercase - columns


teams
TEAM_ID
TEAM_NAME
DISTRICT_ID

district
DISTRICT_ID
DISTRICT_NAME

game
GAME_ID
HOME_TEAM_ID
HOME_TEAM_SCORE
AWAY_TEAM_ID
AWAY_TEAM_SCORE
GAME_DATE
GAME_TIME

if you want to expand this out to players and scores, i would suggest something like this

players
PLAYER_ID
TEAM_ID
PLAYER_NAME

gameplayers
GAME_PLAYER_ID
GAME_ID
TEAM_ID (the reason for this, is because if a player has been traded, it still shows the team they played for in the game)
PLAYER_ID

gamescorers
GAME_SCORER_ID
GAME_PLAYER_ID
SCORE_TIME

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.