Initial Stages of a football (soccer) database

[MargateSteve]

I am in the very early stages of setting up a database to primarily tracks results, players and statistics of the football club I support. Eventually I may want to track statistics of many clubs over many leagues so may lean in that direction with the design and just use queries to extract the information I want.
In the long run I will be using it for a huge number of statistics and queries which are going (in my belief) some quite complex relationships and queries that are way above my head at the moment and have, on previous attempts, tried to worry about some of the more advanced stuff rather than get the basics right.
I would be grateful of any advice that can be given on this or if anyone can point me in the right direction of the answer. I know that with sites like this patience is required and if I ever seem to be forgetting that with one of my posts it is probably me not understanding something correctly!
And on the subject of understanding correctly, I will try to convey what I am trying to do as simply as I can. If I over complicate things please do not be afraid to tell me so!

I currently have three tables tblTeam, tblCompType and tblCompName that have some fields in. I have held off from putting in some of the fields that I think should be in those tables until I have some better idea of what structure I need to design it in.

tblTeam
TeamID
TeamName
YearFormed
Nickname

tblCompType
CompTypeID
CompType

tblCompName
CompNameID
CompName
CompType

Before I get to the stage of the matches table there are a few bits on these tables that I cannot get my head around. To avoid getting too confused I will just mention my headache with tblTeam for now!

A team may have had several different names over the years, for example, Manchester United were called Newton Heath from 1878 to 1902.
A very basic idea of some of what would be in the matches table would be
tblMatches
MatchesID
Date
Competition
HomeTeam
HomeScore
AwayTeam
Away Score
Attendance

HomeTeam and AwayTeam will be selectable from tblTeam so I would like it so that Newton Heath would only be available for matches played on a date before 1902 and Manchester United would only be available for matches played after 1902. This way there would be less team names to filter through when adding a match.
As well as this there would be reports and queries that would either be based on one of the team names or both so all names that a certain team has had (some teams have had 5 or 6 previous names) would need to be linked somehow.
Finally, on a basic information report for any team I would like it to show "Previous Names" so in the case of the Manchester United report it would show "Previous Names: Newton Heath 1878-1902".
The way I have always approached this before (without any success) is to create another table
tblPreviousName
PreviousNameID
CurrentName
PreviousName
YearFrom
YearTo

Would it be better to work this through some sort of query or was I moving along the right lines with the other table?

Thanks in advance for any suggestions or advice.
Steve

 

[David Eagar]

tblPreviousName
PreviousNameID
CurrentName
PreviousName
YearFrom
YearTo


I would suggest a TeamHistory table linked to tblTeam to store previous names etc that way you won't have duplicate data like CurrentName.

 

[jdraw]

I am in the very early stages of setting up a database to primarily tracks results, players and statistics of the football club I support. Eventually I may want to track statistics of many clubs over many leagues so may lean in that direction with the design and just use queries to extract the information I want.
In the long run I will be using it for a huge number of statistics and queries which are going (in my belief) some quite complex relationships and queries that are way above my head at the moment and have, on previous attempts, tried to worry about some of the more advanced stuff rather than get the basics right.
I would be grateful of any advice that can be given on this or if anyone can point me in the right direction of the answer. I know that with sites like this patience is required and if I ever seem to be forgetting that with one of my posts it is probably me not understanding something correctly!
And on the subject of understanding correctly, I will try to convey what I am trying to do as simply as I can. If I over complicate things please do not be afraid to tell me so!

I currently have three tables tblTeam, tblCompType and tblCompName that have some fields in. I have held off from putting in some of the fields that I think should be in those tables until I have some better idea of what structure I need to design it in.

tblTeam
TeamID
TeamName
YearFormed
Nickname

tblCompType
CompTypeID
CompType

tblCompName
CompNameID
CompName
CompType

Before I get to the stage of the matches table there are a few bits on these tables that I cannot get my head around. To avoid getting too confused I will just mention my headache with tblTeam for now!

A team may have had several different names over the years, for example, Manchester United were called Newton Heath from 1878 to 1902.
A very basic idea of some of what would be in the matches table would be
tblMatches
MatchesID
Date
Competition
HomeTeam
HomeScore
AwayTeam
Away Score
Attendance

HomeTeam and AwayTeam will be selectable from tblTeam so I would like it so that Newton Heath would only be available for matches played on a date before 1902 and Manchester United would only be available for matches played after 1902. This way there would be less team names to filter through when adding a match.
As well as this there would be reports and queries that would either be based on one of the team names or both so all names that a certain team has had (some teams have had 5 or 6 previous names) would need to be linked somehow.
Finally, on a basic information report for any team I would like it to show "Previous Names" so in the case of the Manchester United report it would show "Previous Names: Newton Heath 1878-1902".
The way I have always approached this before (without any success) is to create another table
tblPreviousName
PreviousNameID
CurrentName
PreviousName
YearFrom
YearTo

Would it be better to work this through some sort of query or was I moving along the right lines with the other table?

Thanks in advance for any suggestions or advice.
Steve

Steve,

I would recommend a model of the tables and relationships between them; and a list of possible questions and reports you might want the database to answer. The tables and relationships make up the things of interest to you. The potential questions and reports are a major step in determining the requirements. Once you get the model somewhat organized, you can "test the model" by determining if the structure and data can answer your questions. After a little tweaking, I'm sure you'll get it just right.

I agree with David's TeamHistory table to provide a link to previous/alias names.

Also there is a model of a High School Football database at this site

http://www.databaseanswers.org/data_models/high_school_football/index.htm

It isn't an answer to your specific task, but it may give you some guidance on tables, attributes/fields and relationships.
Also, take a look at the User requirements link on the model.

Good luck. Hope this is useful.