Football (soccer) database design questions (not found in FAQs)

[Endaxi]

I recently discovered that a football stats program written originally by my Father in the days of Win 3.1, no longer works under 64-bit Windows 7, as the program is looking for "run32.dll". As a result, I have recovered all my data to TXT files, but now need another means of retreiving the information and stats from the data.

I am completely new to Access, but I aim to design a new database for recording every match played by my football club, Clyde, to include the list of Clyde's goal scorers (but NOT their opponents' scorers). Match categories are effectively the various competitions, such as League or Cup, etc.

At present, I have ALL the match data stored in a tabbed TXT file, in the following format:
08/12/1888 SC Celtic 9 Clyde 2 Cherrie (2)
28/09/1889 SC Clyde 1 Northern 2 Bonnar
06/09/1890 SC Clyde 7 Whitefield 2 Brandon H, Brandon R, Gillespie, Neaves (2), Stevenson (2)
27/09/1890 SC Clyde 4 Hurlford 3 Not Known, Brandon R (2), Neaves

I also have a list of the club's Managers, Grounds, and (although I probably don't need it separately(?)), the list of all the scorers (currently in the matches data), plus the list of match Categories.

In the program that no longer works, wildcards were used for filtering match categories. "??" meant all Matches, "?L" meant all League matches in All Divisions, and "1L", "2L", etc identified the League Divisions.

NOTE that for Home matches, my team is placed first, and for Away matches, it is listed second. Also note that the Scorers' names are listed with commas and spaces, and are in a single field. There is also the problem (as I see it) that if one player scores more than once in a single match, his name is listed with the number of goals in brackets after his name, e.g. "Brandon R (2)", rather than listing "Brandon R, Brandon R". (How it currently appears is my preference!)

My initial thoughts on Tables:

Tbl_Categories
ID
CategoryCode
Category
CategoryNotes

Tbl_Teams
ID
TeamName
TeamNotes

Tbl_Grounds
ID
Ground
StartDate
EndDate
GroundNotes

Tbl_Managers
ID
LastName
FirstName
StartDate
EndDate
ManagerNotes

Tbl_Results
ID
MatchDate
CategoryCode
HomeAway
MyTeam
MyTeamGoals
OppTeam
OppTeamGoals
MyTeamScorer1
MyTeamScorer2
etc up to
MyTeamScorer20
MatchNotes

Tbl_Scorers
ID
LastName
FirstName
FirstGoalDate
LastGoalDate
ScorerNotes

Some of the information I aim to be able to extract:
Results against all teams in all categories, including totals for played, won, drawn, lost, goals for, goals against.
Results against a specific other team, either in all categories, or in one category, including totals as above.
Results in all home matches, against all teams in all categories, including totals for played, won, drawn, lost, goals for, goals against.
Results in all away matches, against all teams in all categories, including totals for played, won, drawn, lost, goals for, goals against.
Results for a specific category, with totals.
Results per Manager, with totals.
Results per home ground, with totals.
Results per season, with totals.
List of scorers, including totals of the goals they have scored, in all matches, or per season, or per category, etc.

QUESTIONS:
1. While I have all the match data as a TXT file, I am unsure as to whether I need to re-format it to identify Home or Away in a separate field in order to list "Clyde" always in the same field, or if I would be able to extract the information I require from my data as it stands at present. Can anyone advise, please?


2. I'm unsure about how the Scorers are listed, at present all in one field. Do I need to separate each scorer into a separate field, or can the information I need be extracted from my data without any need to alter it.

ANY ASSISTANCE will be gratefully received!
I don't want to load in any data until I am sure the basic database structure is right.

[aytee111]

While your philosophy of "I don't want to load in any data until I am sure the basic database structure is right" is a good one under most circumstances, in your case it would be a good learning excercise.

You have a table for the Grounds - that should tell you if it is a home or away match. You could also put a value for home or away on the results table, just in case a "home" match is played on a different ground.

Read up about database normalisation. Your Results table is definitely not normalised - you are right, the scorers cannot be stored like that. Make a new table which will contain the scorer name linked to the scorers table and the result linked to the results table.

My suggestion would be to start loading data via queries, that will give you the chance to see if all your data has been kept somewhere. Next begin by the requirements you have stated above - create queries for each of those requirements to see if it can be done. Don't start on forms and reports until you are sure it is all hanging together.

[jzwp11]

In addition to aytee111's comments, I would have more descriptive names for the ID fields in the respective tables. Having a bunch of fields called ID will become confusing. For example teamID instead of ID in the team table.

I am also not sure what the purpose is of Tbl_Scorers. You can extract all of the players and their scores from your the results (once you have all that normalized).

Since you have people stored in the manager table and you need a table for players who are also people, why not have 1 table for all people? You might even have the situation where a person can be a player today and a manager at some point in the future so having separate tables does not make sense.

Do you ever have a situation where a match is played on neutral ground? In these situations, how is the home or away status of a team determined?

[Endaxi]

In response to aytee111's post, the table of Grounds is necessary as my partiucular club has had around 5 'home' grounds over the years. Putting these in a table allows me (eventually) to extract information based on the period when a particular Ground was home.
I am reading up on Access on a daily basis, so hopefully my understanding of the program will improve at a respectable rate. I also note your sugestion to add some data as a learning experience. This would perhaps best. I will give it a try. I hadn't realised you could ADD data via Queries... I had thought they were only for extracting data. Must go and read up on this.
Thanks.

[Endaxi]

In response to jzwp11's notes, the reason that I thought it better to keep Managers and Scorers separate was that the scorers are entered with the match data, while the list of Managers is added separately, and I will want to extract data such as the playing record under one Manager or another, while this would not be possible for a scorer, as the database only holds records of the games they scored in, not the games they played in. Also, the list of Managers is reasonably short, consisting of perhaps 20 names at present.
I agree with you re. the matter of 'ID' fields.
Re. Neutral grounds, yes, this happens occasionally, and all are handled as 'away' matches, i.e. not played at home. I have no requirement to analyse results by neutral grounds.
Thanks.

[jzwp11]

...reason that I thought it better to keep Managers and Scorers separate was that the scorers are entered with the match data, while the list of Managers is added separately,

You can add a field to a table to distinguish players from managers, you can then use a query to separate the groups as needed. The structure would be a little different if you want to handle the case where players can eventually become managers (over time). Do you have any players who are managers simultaneously?

If players cannot become managers, the structure would look like this

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName
-fkRoleID foreign key to a table that holds all roles


tblRoles (2 records in this table: manager, player)
-pkRoleID primary key, autonumber
-txtRole


If players can be managers, the structure would be like this

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblRoles (2 records in this table: manager, player)
-pkRoleID primary key, autonumber
-txtRole

tblPeopleRoles
-pkPeopleRoleID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkRoleID foreign key to tblRoles
-dteEndEffective (date field to say when person in role was no longer in effect--i.e. when a player ceased to be a player.)