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!!
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.
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.
if team rosters (the players on a team) can change from game to game, then the following will need some adjustment
TeamNo FK to Teams
HomeTeam FK to Teams
AwayTeam FK to Teams
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